SQL dialects cheat sheet

How common PostgreSQL constructs are written in MySQL, SQL Server, SQLite, Oracle, BigQuery and Snowflake.

Syntax

-- PostgreSQL (what SQL Quest runs)
SELECT ... ORDER BY x LIMIT 10;
COALESCE(a, b)    a || b    x::numeric    date_trunc('month', d)    now()
INSERT ... ON CONFLICT (k) DO UPDATE ...    ... RETURNING *

Only PostgreSQL is executable in SQL Quest. Core SQL (SELECT, JOIN, GROUP BY, window functions, CTEs) is almost identical everywhere; the differences cluster in a few places:

task	PostgreSQL	MySQL	SQL Server	Oracle	BigQuery	Snowflake
first n rows	`LIMIT n`	`LIMIT n`	`TOP (n)` / `OFFSET..FETCH`	`FETCH FIRST n ROWS ONLY`	`LIMIT n`	`LIMIT n`
NULL default	`COALESCE`	`IFNULL`, `COALESCE`	`ISNULL`, `COALESCE`	`NVL`, `COALESCE`	`IFNULL`, `COALESCE`	`IFNULL`, `NVL`, `COALESCE`
concatenate	`\	\	`,` concat`	`CONCAT`	`+`, `CONCAT`	`\	\	`	`\	\	`,` CONCAT`	`\	\	`,` CONCAT`
cast	`x::t`, `CAST`	`CAST`	`CAST`, `CONVERT`, `TRY_CAST`	`CAST`, `TO_NUMBER`	`CAST`, `SAFE_CAST`	`x::t`, `CAST`, `TRY_CAST`
month start	`date_trunc('month', d)`	`DATE_FORMAT(d, '%Y-%m-01')`	`DATETRUNC(month, d)` (2022)	`TRUNC(d, 'MM')`	`DATE_TRUNC(d, MONTH)`	`DATE_TRUNC('month', d)`
add days	`d + 7`, `d + interval '7 days'`	`DATE_ADD(d, INTERVAL 7 DAY)`	`DATEADD(day, 7, d)`	`d + 7`	`DATE_ADD(d, INTERVAL 7 DAY)`	`DATEADD(day, 7, d)`
current time	`now()`	`NOW()`	`SYSDATETIME()`	`SYSTIMESTAMP`	`CURRENT_TIMESTAMP()`	`CURRENT_TIMESTAMP`
case-insensitive match	`ILIKE`	`LIKE` (default collation)	`LIKE` (default collation)	`UPPER(x) LIKE`	`LOWER(x) LIKE`	`ILIKE`
conditional count	`count(*) FILTER (WHERE c)`	`SUM(c)`	`SUM(CASE ...)`	`SUM(CASE ...)`	`COUNTIF(c)`	`COUNT_IF(c)`
filter on window	subquery	subquery	subquery	subquery	`QUALIFY`	`QUALIFY`
upsert	`ON CONFLICT`, `MERGE`	`ON DUPLICATE KEY UPDATE`	`MERGE`	`MERGE`	`MERGE`	`MERGE`
changed rows back	`RETURNING`	—	`OUTPUT`	`RETURNING INTO` (PL/SQL)	—	—
auto id	`GENERATED AS IDENTITY`	`AUTO_INCREMENT`	`IDENTITY(1,1)`	`GENERATED AS IDENTITY`	—	`IDENTITY` / `AUTOINCREMENT`
set difference	`EXCEPT`	`EXCEPT` (8.0.31+)	`EXCEPT`	`MINUS` (`EXCEPT` 21c+)	`EXCEPT DISTINCT`	`EXCEPT` / `MINUS`

Other differences that bite:

Identifier case and quoting: PostgreSQL folds unquoted names to lower case, Oracle and Snowflake to upper case. MySQL quotes with backticks, SQL Server with [brackets].
Integer division: 7 / 2 is 3 in PostgreSQL, SQL Server and SQLite; 3.5 in MySQL, Oracle and Snowflake; BigQuery's / always returns FLOAT64.
Empty string: Oracle treats '' as NULL.
Transactional DDL: PostgreSQL (and SQL Server) can roll back CREATE/ALTER; MySQL and Oracle commit DDL implicitly.
NULL sort order: NULLs last in ascending order in PostgreSQL, Oracle and Snowflake; first in MySQL, SQL Server, SQLite and BigQuery.

Examples

PostgreSQL-specific conveniences in one query starter

SELECT DISTINCT ON (c.city)
       c.city, c.customer_name, o.order_date::text AS last_order,
       count(*) FILTER (WHERE o.status = 'delivered') OVER (PARTITION BY c.city) AS delivered_in_city
FROM customers c
JOIN orders o USING (customer_id)
WHERE c.city ILIKE '%a%'
ORDER BY c.city, o.order_date DESC, o.order_id DESC;

DISTINCT ON, FILTER, ILIKE and :: are all PostgreSQL features you would rewrite in other engines.

Portable version of the same idea starter

WITH ranked AS (
  SELECT c.city, c.customer_name, o.order_date,
         ROW_NUMBER() OVER (PARTITION BY c.city ORDER BY o.order_date DESC, o.order_id DESC) AS rn,
         SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) OVER (PARTITION BY c.city) AS delivered_in_city
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  WHERE LOWER(c.city) LIKE '%a%'
)
SELECT city, customer_name, CAST(order_date AS VARCHAR(10)) AS last_order, delivered_in_city
FROM ranked
WHERE rn = 1
ORDER BY city;

ROW_NUMBER + CASE + LOWER/LIKE + CAST run (with at most small changes) on every major engine.

Pitfalls

Copying LIMIT into SQL Server or Oracle code.
Assuming || concatenates in MySQL (it is logical OR by default).
Assuming the same integer-division behaviour everywhere.
Assuming LIKE is case-sensitive (or insensitive) everywhere.