How Do SQL Dialects Differ?

The portable core is larger than most people assume and the dialect-specific parts are fewer than the folklore suggests. Standard SELECT, JOIN, GROUP BY, HAVING, subqueries, CTEs, window functions, CASE and the standard aggregate functions all behave identically on MySQL, PostgreSQL, SQLite and SQL Server. If your query is only that, you can move it without edits.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada Lovelace'), (2, 'Grace Hopper');
INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00), (2, 1, 340.50), (3, 2, 75.25);

WITH revenue AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT c.name,
       r.total,
       CASE WHEN r.total > 200 THEN 'gold' ELSE 'standard' END AS tier
FROM revenue AS r
JOIN customers AS c ON c.id = r.customer_id
ORDER BY r.total DESC;

Where dialects diverge is in four areas. Identifiers: MySQL quotes with backticks by default, standard SQL with double quotes. Upserts: the standard ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQL Server's MERGE. Row limiting: LIMIT n OFFSET m everywhere except SQL Server, which wants OFFSET m ROWS FETCH NEXT n ROWS ONLY and requires an ORDER BY. Functions: CONCAT, NOW and ILIKE are not standard, so the portable spellings are ||, datetime('now') and plain LIKE.

Type strictness is the subtlest difference. PostgreSQL refuses to compare a text column to an integer; MySQL coerces silently. The same query can pass review on MySQL and fail in production on PostgreSQL, which is why portability testing matters more than portability theory.

Take the classic "insert the row, or update it if it is already there" upsert. The standard form names the conflicting column and uses excluded to reach the value that was proposed for insertion. MySQL has no excluded pseudo-table; it uses VALUES(column) instead, and names the conflict implicitly by which unique index is violated.

CREATE TABLE account_email (
  account_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Portable form, works on SQLite and PostgreSQL:
INSERT INTO account_email (account_id, email) VALUES (1, '[email protected]')
ON CONFLICT (email) DO UPDATE SET email = excluded.email;

-- The MySQL / MariaDB spelling of the same statement:
-- INSERT INTO account_email (account_id, email) VALUES (1, '[email protected]')
-- ON DUPLICATE KEY UPDATE email = VALUES(email);

SELECT account_id, email FROM account_email;

The SELECT returns one row: account 1 with [email protected]. The commented line is the MySQL equivalent — it is commented out only because SQLite cannot parse it, not because the logic differs.

There is a third spelling worth knowing, because SQL Server has no ON CONFLICT at all and uses a full MERGE statement with a MATCHED and a NOT MATCHED branch. It is more verbose, and it is the form most often used to implement slowly changing dimensions, since a merge can update several columns conditionally rather than setting them all to the incoming values.

The takeaway for a review: the semantics you want — insert, or update on key collision — are expressible in all three, but only the standard spelling is portable. When you write application SQL, decide once at the start whether the data layer is dialect-aware, and if it is, keep the dialect-specific statements in one module instead of scattering them through the codebase.

Prevention beats detection, and the prevention has to be mechanical rather than cultural. A style guide that says "use standard SQL" will be ignored under deadline pressure; a linter that rejects CONCAT and NOW() will not.

The practical stack is four layers. First, a dialect-specific linter in CI — sqlfluff has a dialect setting that flags dialect-only functions, and a custom rule set can ban the specific constructs your target database does not support. Second, integration tests against the real target, not a SQLite stand-in: if PostgreSQL is the production database, the test suite must open a PostgreSQL connection, because SQLite will happily accept MySQL-style date arithmetic that PostgreSQL rejects at runtime. Third, a thin data-access layer that owns every non-portable statement, so the 5 percent of dialect-specific SQL lives in one reviewable file instead of two hundred service methods. Fourth, static type checking on the query string, where a tool like SQLAlchemy, Diesel or jOOQ validates the statement against the target dialect at build time.

Two habits help as much as the tooling. Prefer the standard spelling by default — || over CONCAT, datetime('now') over NOW() — and only reach for a dialect feature when you have written down why the standard one will not do. And when you must use one, comment it with the engine name, so the next reader knows the portability cost is deliberate rather than accidental.

The last piece is a deployment check. Run the migration and a smoke query against the production engine in CI, not just locally. A dialect bug that only appears on the target database should fail the build, never the release.