ANSI SQL (officially ISO/IEC 9075) is the international standard that defines how SQL must behave across all database systems. Think of it as the constitution of relational databases — every vendor must implement the core, but each adds their own amendments.

The critical insight: SQL-92 is the practical portability baseline. Every major database (PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery) supports SQL-92 core. Features added in SQL:1999+ have uneven support. Vendor extensions are completely non-portable.

**The SQL Standard Timeline**

SQL-86 (1986) — The first ANSI standard. Basic SELECT, INSERT, UPDATE, DELETE. Barely resembles modern SQL.

SQL-92 (1992) — The portability baseline. Introduced: JOIN syntax (INNER/LEFT/RIGHT), CASE expressions, CAST(), COALESCE(), string functions (UPPER, LOWER, SUBSTRING), date literals, transaction isolation levels, FETCH FIRST (sort of). Every production database supports this.

SQL:1999 (SQL3) — Introduced: recursive CTEs (WITH RECURSIVE), triggers, procedural language (PSM), user-defined types, BOOLEAN data type (finally). MySQL's support remained patchy for years.

SQL:2003 — The window functions revolution. Introduced: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(). Also: GENERATED AS IDENTITY (replacing vendor auto-increment hacks), MERGE statement (upsert). MySQL didn't add window functions until 8.0 (2018).

SQL:2008 — Formalized FETCH FIRST n ROWS ONLY (pagination). TRUNCATE TABLE. INSTEAD OF triggers standard.

SQL:2011 — Temporal tables (system-versioned and application-time). Still poorly supported in MySQL.

SQL:2016 — JSON functions standard (JSON_VALUE, JSON_QUERY, JSON_TABLE). Each vendor had already shipped their own JSON API — now they're all different AND there's a standard nobody fully implements.

SQL:2023 — Property graph queries (MATCH clause), enhanced JSON, multi-dimensional arrays. Bleeding edge — essentially unsupported in production databases today.

---

**The Big Four Divergence Points**

These are the areas where databases diverge most aggressively from each other:

**1. Pagination**

- ANSI (SQL:2008): FETCH FIRST n ROWS ONLY / OFFSET n ROWS

- PostgreSQL/MySQL: LIMIT n OFFSET n ← simple but non-standard

- SQL Server (pre-2012): SELECT TOP n ← non-standard, no offset

- SQL Server (2012+): OFFSET/FETCH NEXT (matches ANSI)

- Oracle (pre-12c): WHERE ROWNUM <= n ← the old nightmare

- Oracle (12c+): FETCH FIRST / OFFSET (matches ANSI)

**2. String Concatenation**

- ANSI: || operator (pipe pipe)

- PostgreSQL: || ✓, CONCAT() ✓

- MySQL: CONCAT() only — || is logical OR by default!

- SQL Server: + operator, CONCAT() (2012+)

- Oracle: || ✓, CONCAT() for 2 args only

- Safe choice: CONCAT() works on all four

**3. Auto-Increment Identity**

- ANSI (SQL:2003): GENERATED ALWAYS AS IDENTITY / GENERATED BY DEFAULT AS IDENTITY

- PostgreSQL: SERIAL (legacy), BIGSERIAL, or GENERATED AS IDENTITY (preferred since PG 10)

- MySQL: AUTO_INCREMENT

- SQL Server: IDENTITY(seed, increment)

- Oracle: GENERATED AS IDENTITY (12c+), or legacy SEQUENCE + trigger

**4. Date/Time Functions**

- ANSI: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP (no parentheses — they're keywords)

- PostgreSQL: NOW(), CURRENT_TIMESTAMP, LOCALTIMESTAMP — all work

- MySQL: NOW(), CURDATE(), CURTIME() — CURRENT_DATE works too

- SQL Server: GETDATE(), SYSDATETIME(), GETUTCDATE() — no CURRENT_DATE without cast

- Oracle: SYSDATE (includes time), TRUNC(SYSDATE) for date only — no CURRENT_DATE without FROM DUAL