**SQL-92 Core (universally portable):**

SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING

INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, CROSS JOIN

UNION, INTERSECT, EXCEPT (MINUS in Oracle)

CASE WHEN ... THEN ... ELSE ... END

CAST(value AS type)

COALESCE(a, b, c)

NULLIF(a, b)

CURRENT_DATE, CURRENT_TIMESTAMP (no parentheses)

LIKE, BETWEEN, IN, IS NULL, IS NOT NULL

COUNT, SUM, AVG, MIN, MAX

**SQL:2003+ (mostly portable, check versions):**

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)

RANK(), DENSE_RANK(), NTILE()

LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

WITH cte AS (...) SELECT ... (CTEs — SQL:1999)

WITH RECURSIVE (check MySQL 8.0+)

GENERATED ALWAYS AS IDENTITY

**Vendor-Specific (non-portable):**

LIMIT / TOP / FETCH FIRST (all mean the same thing, all different syntax)

|| vs CONCAT vs +

AUTO_INCREMENT vs SERIAL vs IDENTITY(1,1)

NOW() vs GETDATE() vs SYSDATE

ON CONFLICT vs ON DUPLICATE KEY vs MERGE

BOOLEAN vs BIT vs TINYINT(1)

SQL-92 is the practical portability baseline — all production databases support it

CURRENT_DATE, CURRENT_TIMESTAMP are keywords (no parentheses) — portable SQL-92

CONCAT() is safer than || for string concatenation — || is logical OR in MySQL by default

FETCH FIRST n ROWS ONLY is ANSI SQL:2008 — SQL Server uses FETCH NEXT (not FIRST)

GENERATED AS IDENTITY (SQL:2003) is more portable than SERIAL, AUTO_INCREMENT, IDENTITY(1,1)

COALESCE() and NULLIF() are ANSI SQL-92 and work on every database

Window functions (ROW_NUMBER, RANK, LAG) are SQL:2003 — not available in MySQL 5.7 and earlier

Most ANSI-compliant of the Big Four. Supports || , BOOLEAN, GENERATED AS IDENTITY, ON CONFLICT, FILTER clause, full window functions. Best choice for new projects.

AUTO_INCREMENT only (no GENERATED AS IDENTITY). No || (logical OR). No BOOLEAN (TINYINT). No INTERVAL in SQL Server style. Window functions added in 8.0 (2018). CONCAT is safe.

No INTERVAL syntax — use DATEADD. No BOOLEAN — use BIT with 1/0. No LIMIT — use FETCH NEXT. MERGE requires HOLDLOCK hint. No CURRENT_DATE — use CAST(GETDATE() AS DATE).

Legacy ROWNUM pagination (pre-12c). SYSDATE includes time component (use TRUNC). CONCAT only takes 2 arguments. FROM DUAL required for standalone expressions (pre-23c).

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;