Skip to content
Tutorials Center
My learning

Topics

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 BY
  • INNER JOIN and LEFT JOIN with ON
  • INSERT INTO t (cols) VALUES (...), UPDATE ... SET ... WHERE, DELETE FROM ... WHERE
  • COUNT, SUM, AVG, MIN, MAX, COALESCE, CASE, CAST(x AS type)
  • Common table expressions with WITH and window functions with OVER (in current versions of all five)
  • UNION, UNION ALL; INTERSECT and EXCEPT in everything except old MySQL (MINUS instead of EXCEPT in Oracle before 21c)
  • Single-quoted string literals and -- line comments

Try it

SQL A query that is portable as written ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Where SQLite is looser than the others ยท database: shop

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:

SQL
-- 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:

SQL
-- 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. LIKE is case-insensitive in SQLite (for ASCII), MySQL and SQL Server under default collations, but case-sensitive in PostgreSQL and Oracle. PostgreSQL has ILIKE.
  • Unquoted names. PostgreSQL folds them to lower case, Oracle to upper case, others keep what you typed or ignore case.
  • GROUP BY strictness. 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 through ONLY_FULL_GROUP_BY.
  • FROM is optional? Oracle always needs FROM dual for 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 = ON per connection.

Usage notes

  • Write the standard form first. Prefer CAST, COALESCE, CASE, CURRENT_TIMESTAMP, || or CONCAT through a thin layer, and explicit JOIN ... 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 BY and type coercion.
  • Version matters. Window functions, CTEs, FETCH FIRST and RETURNING were added at different times; check the version you actually deploy.
  • Dialect names in this reference. Code marked -- PostgreSQL, -- MySQL, -- SQL Server or -- Oracle is read-only; runnable examples use SQLite.