SQL Reference ยท Dialects and Concepts
SQL dialect differences
How SQLite, PostgreSQL, MySQL, SQL Server and Oracle differ: row limiting, quoting, types, dates, strings, upserts and what to do to keep queries portable.
SQL is a standard, but no database implements exactly the standard. Each product (SQLite, PostgreSQL, MySQL and MariaDB, Microsoft SQL Server, Oracle) supports a large common core and then adds its own syntax, function names and behaviors. The core is enough for most SELECT, INSERT, UPDATE and DELETE work. The differences show up when you limit rows, work with dates, build strings, generate keys or write anything procedural.
This page is a map. It shows where the dialects agree, where they split, and how to write queries that survive a move from one system to another. The live examples run on SQLite, which is what the browser uses here.
What is the same everywhere
These work, with the same meaning, in all five systems:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BYINNER JOINandLEFT JOINwithONINSERT INTO t (cols) VALUES (...),UPDATE ... SET ... WHERE,DELETE FROM ... WHERECOUNT,SUM,AVG,MIN,MAX,COALESCE,CASE,CAST(x AS type)- Common table expressions with
WITHand window functions withOVER(in current versions of all five) UNION,UNION ALL;INTERSECTandEXCEPTin everything except old MySQL (MINUSinstead ofEXCEPTin Oracle before 21c)- Single-quoted string literals and
--line comments
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The main differences
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Limit rows | LIMIT n |
LIMIT n or FETCH FIRST n ROWS ONLY |
LIMIT n |
TOP n or OFFSET ... FETCH |
FETCH FIRST n ROWS ONLY (12c+) |
| Identifier quotes | "x" (also `x` and [x]) |
"x" |
`x` |
[x] or "x" |
"x" |
| String concatenation | || |
|| |
CONCAT() (|| is OR by default) |
+ or CONCAT() |
|| |
| Auto-generated keys | INTEGER PRIMARY KEY, AUTOINCREMENT |
GENERATED ... AS IDENTITY, SERIAL |
AUTO_INCREMENT |
IDENTITY(1,1) |
GENERATED ... AS IDENTITY (12c+) |
| Boolean type | none (0 and 1) | BOOLEAN |
BOOLEAN is TINYINT(1) |
BIT |
none in SQL before 23ai |
| Current time | CURRENT_TIMESTAMP, datetime('now') |
now(), CURRENT_TIMESTAMP |
NOW() |
GETDATE(), SYSDATETIME() |
SYSDATE, SYSTIMESTAMP |
| Upsert | ON CONFLICT |
ON CONFLICT |
ON DUPLICATE KEY UPDATE |
MERGE |
MERGE |
| Row-returning writes | RETURNING |
RETURNING |
none (MariaDB has it) | OUTPUT |
RETURNING ... INTO |
| Empty string and NULL | different | different | different | different | the same ('' is NULL) |
Dialect differences in detail
Two cases show how much a single idea can change. Getting the five newest orders:
-- SQLite, PostgreSQL, MySQL SELECT * FROM orders ORDER BY ordered_at DESC LIMIT 5; -- SQL Server SELECT TOP 5 * FROM orders ORDER BY ordered_at DESC; -- Standard SQL (PostgreSQL, Oracle 12c+, SQL Server, Db2) SELECT * FROM orders ORDER BY ordered_at DESC FETCH FIRST 5 ROWS ONLY;
Adding one month to a date:
-- SQLite SELECT date('2025-01-15', '+1 month'); -- PostgreSQL SELECT DATE '2025-01-15' + INTERVAL '1 month'; -- MySQL SELECT DATE_ADD('2025-01-15', INTERVAL 1 MONTH); -- SQL Server SELECT DATEADD(month, 1, '2025-01-15'); -- Oracle SELECT ADD_MONTHS(DATE '2025-01-15', 1) FROM dual;
Other differences that cause trouble when you move code:
- Case sensitivity.
LIKEis case-insensitive in SQLite (for ASCII), MySQL and SQL Server under default collations, but case-sensitive in PostgreSQL and Oracle. PostgreSQL hasILIKE. - Unquoted names. PostgreSQL folds them to lower case, Oracle to upper case, others keep what you typed or ignore case.
GROUP BYstrictness. PostgreSQL, SQL Server and Oracle reject a selected column that is neither grouped nor aggregated. SQLite accepts it and picks a value from some row in the group. MySQL rejects it by default throughONLY_FULL_GROUP_BY.FROMis optional? Oracle always needsFROM dualfor a constant query; the others do not.- Transactions for DDL. PostgreSQL, SQLite and SQL Server can roll back
CREATE TABLE; MySQL and Oracle commit implicitly. - Foreign keys. Enforced by default in PostgreSQL, MySQL (InnoDB), SQL Server and Oracle; SQLite needs
PRAGMA foreign_keys = ONper connection.
Usage notes
- Write the standard form first. Prefer
CAST,COALESCE,CASE,CURRENT_TIMESTAMP,||orCONCATthrough a thin layer, and explicitJOIN ... ON. Reach for vendor syntax only when needed. - Keep dialect code in one place. Date arithmetic, row limiting and string building are the usual suspects. Isolate them in views, a query builder or a small repository layer.
- Test on the target engine. A query that returns the right rows in SQLite can fail or return different rows elsewhere, especially with
GROUP BYand type coercion. - Version matters. Window functions, CTEs,
FETCH FIRSTandRETURNINGwere added at different times; check the version you actually deploy. - Dialect names in this reference. Code marked
-- PostgreSQL,-- MySQL,-- SQL Serveror-- Oracleis read-only; runnable examples use SQLite.