Dialect Differences

While “SQL” is a standard (ANSI SQL), every database vendor implements its own “flavor” or Dialect. It’s like English: you can understand someone from London, New York, and Sydney, but they use different words for the same thing (e.g., Elevator vs. Lift).

Definition: The Big 3

1. PostgreSQL: The most standard-compliant and feature-rich open-source database.
2. SQL Server (T-SQL): Microsoft’s enterprise database, known for great tooling and window function support.
3. MySQL: The world’s most popular open-source database, known for speed and simplicity.

Context: Key Differences in Syntax

1. Limiting Results

• Postgres/MySQL: SELECT * FROM table LIMIT 10;
• SQL Server: SELECT TOP 10 * FROM table;
• Oracle: SELECT * FROM table FETCH FIRST 10 ROWS ONLY;

2. Concatenation

• Postgres: FirstName || ' ' || LastName
• SQL Server: FirstName + ' ' + LastName
• MySQL: CONCAT(FirstName, ' ', LastName)

3. Case Sensitivity

• Postgres: Table and column names are case-insensitive unless quoted ("MyTable"). Data is case-sensitive by default.
• SQL Server: Usually case-insensitive for both names and data (depending on the “Collation” settings).
• MySQL: Table names are case-sensitive on Linux but insensitive on Windows. Data is insensitive by default.

Detailed Explanation: Feature Support

Feature	PostgreSQL	SQL Server	MySQL
Recursive CTEs	Yes	Yes	Yes (since v8.0)
JSON Support	Excellent (JSONB)	Good	Good
Window Functions	Excellent	Excellent	Good
Full Outer Join	Yes	Yes	No (Must use Union)
Materialized Views	Yes	Yes (Indexed Views)	No

Example: The “Upsert” Operation

A common task is “Update if the row exists, otherwise Insert.” This is a classic dialect nightmare.

• Postgres: INSERT INTO ... ON CONFLICT (id) DO UPDATE SET ...
• SQL Server: MERGE INTO ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...
• MySQL: INSERT INTO ... ON DUPLICATE KEY UPDATE ...

Under the Hood: The Driver and Protocol

When you switch between databases, your code doesn’t just change syntax; it changes how it talks to the server.

• Postgres uses the libpq protocol.
• SQL Server uses TDS (Tabular Data Stream).
• MySQL uses its own binary protocol.

This is why you need different “Database Drivers” (like psycopg2 for Postgres or pyodbc for SQL Server) in your programming language.

Tip

The ANSI Bridge:
If you are building a product that needs to support multiple databases, try to stick to ANSI SQL. Avoid vendor-specific functions (like Postgres’s :: cast or SQL Server’s GETDATE()) and use the standard equivalents (CAST() and CURRENT_TIMESTAMP).

Summary of Specialized Topics

If you need…	Look at…
Flexible data	JSONB columns
Complex sequences	Gaps and Islands logic
Reusable logic	Stored Procedures
Cross-platform code	ANSI SQL Standards