SQL Dialects Compared: Where MySQL, PostgreSQL, SQL Server and SQLite Disagree

Quoting: the one that breaks silently

Standard SQL uses single quotes for string literals and double quotes for identifiers that need protecting — a column named order, a table with a space in its name, a mixed-case name in PostgreSQL. MySQL, by default, treats double quotes as a second string delimiter and uses backticks for identifiers. SQL Server uses square brackets for identifiers and accepts double quotes only when QUOTED_IDENTIFIER is on, which it is in most modern connections but not all.

The trap is that MySQL's double-quoted "string" is accepted by PostgreSQL — as a column reference. WHERE status = "active" runs in MySQL and fails in PostgreSQL with "column active does not exist", which is at least loud. The reverse is quieter: a PostgreSQL query with "Order Id" as an identifier runs in MySQL as a string comparison and returns nothing, with no error. Setting MySQL's ANSI_QUOTES mode makes it behave like the standard, and single-quoting every string literal makes the query portable in the first place.

Limiting rows

-- MySQL, PostgreSQL, SQLite
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 20;

-- SQL Server (2012+); ORDER BY is mandatory
SELECT * FROM orders ORDER BY created_at DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
SELECT TOP 10 * FROM orders ORDER BY created_at DESC;   -- no offset

-- Oracle 12c+, DB2, and the SQL:2008 standard
SELECT * FROM orders ORDER BY created_at DESC OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

Two things bite here beyond the syntax. Without an ORDER BY, a limited result is an arbitrary subset that can change between runs, and SQL Server enforces this by refusing OFFSET without one. And OFFSET-based pagination reads and discards every skipped row, so page 1,000 of a large table is slow on every engine; keyset pagination (WHERE created_at < :last_seen ORDER BY created_at DESC LIMIT 10) is the portable fix.

Strings, nulls and booleans

• Concatenation: || in PostgreSQL, SQLite, Oracle and the standard; + in SQL Server (where || is a syntax error); MySQL treats || as logical OR unless PIPES_AS_CONCAT is set. CONCAT(a, b) works everywhere except older SQLite.
• Null in concatenation: 'a' || NULL is NULL in PostgreSQL and Oracle; CONCAT('a', NULL) is 'a' in MySQL and SQL Server but NULL in PostgreSQL. This inconsistency corrupts generated addresses and names across migrations.
• Booleans: PostgreSQL has a real BOOLEAN with TRUE/FALSE; MySQL's BOOLEAN is TINYINT(1); SQL Server has no boolean, only BIT, and cannot use a bit column directly in WHERE active — it needs WHERE active = 1.
• Case-insensitive comparison: MySQL and SQL Server compare strings case-insensitively by default collation; PostgreSQL and SQLite are case-sensitive. WHERE email = 'A@X.COM' matches in one and not the other. PostgreSQL's ILIKE or LOWER() on both sides is the portable form.

Dates: assume nothing is portable

Note the reversed argument order in SQL Server's DATEDIFF compared with MySQL's — a query that is copied across returns negated intervals and nothing warns you. Storage differs too: SQLite has no date type at all and stores whatever text or number you give it; MySQL's TIMESTAMP converts through the session time zone while DATETIME does not; PostgreSQL's timestamptz is the one that behaves the way people expect. For anything beyond trivial arithmetic, doing date work in the application language with a proper time-zone library is more portable than any SQL.

Upsert, returning, and other useful non-standards

"Insert or update if it exists" has four spellings: MySQL's INSERT … ON DUPLICATE KEY UPDATE, PostgreSQL's and SQLite's INSERT … ON CONFLICT (key) DO UPDATE SET …, and SQL Server's MERGE, which is more general and considerably easier to get wrong. Getting the inserted row back is RETURNING * in PostgreSQL and SQLite (3.35+), OUTPUT INSERTED.* in SQL Server, and LAST_INSERT_ID() as a separate call in MySQL. Window functions, common table expressions and FILTER clauses are now widely supported but arrived in MySQL only in version 8 and in SQLite only in 3.25, so a query using them fails on older installations with an unhelpful syntax error.

Why formatting is a review tool

None of the differences above are visible in a single-line query pasted from a log. Formatted, they are: every clause on its own line, joins aligned, subqueries indented, keywords in one case. The value is not aesthetic. A consistently formatted query means a code review shows the changed condition rather than a reflowed blob; a hand-written WHERE a = 1 OR b = 2 AND c = 3 reveals its precedence problem when the formatter indents AND under OR; and a JOIN without an ON that has silently become a cross join stands out on its own line.

Pick one style — keyword case, comma placement, indent width — and apply it mechanically in a pre-commit hook or on paste, so that the formatting never carries information and the diff only ever shows logic. A formatter that knows the dialect is a prerequisite, because one that treats [Order Id] as an array or backticks as garbage will break the query it is meant to tidy.

Write to the standard, test on the engine

Portable SQL is mostly discipline: single quotes for strings, quoted identifiers only when unavoidable, CONCAT over operators, explicit ORDER BY with every limit, booleans compared explicitly, dates handled in one place. None of it costs anything on the engine you use today, and all of it is what makes the migration or the second database possible later. And when a query does need an engine-specific feature, isolate it and comment it, so the next person knows that DATEDIFF's argument order was a decision and not a typo.

Frequently asked questions