Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

The SQL standard

What the SQL standard is, who maintains it, what each revision added from SQL-86 to SQL:2023, and why every database still has its own dialect.

SQL is defined by an international standard, ISO/IEC 9075, developed with the American national committee that first published it in 1986. The standard is a large document in many parts. Part 2, "Foundation", defines the language most people mean by "SQL". Other parts cover the call interface, stored modules, XML, JSON, property graph queries and more.

No product conforms to the whole standard, and few claim to. The standard defines a small mandatory core and many optional features, each with an identifier (such as T611, "elementary OLAP operations"). Vendors implement the features that their customers want, keep their older syntax for compatibility, and add extensions. That is why a dialect exists for every database.

The revisions

Version Year Notable additions
SQL-86, SQL-89 1986, 1989 First standard: basic SELECT, INSERT, UPDATE, DELETE, views, simple integrity constraints.
SQL-92 (SQL2) 1992 The base most databases still follow: JOIN syntax with ON, outer joins, CASE, CAST, UNION/INTERSECT/EXCEPT, date and time types, VARCHAR, schemas, INFORMATION_SCHEMA.
SQL:1999 (SQL3) 1999 Common table expressions and WITH RECURSIVE, triggers, user-defined types, BOOLEAN, regular-expression predicate SIMILAR TO, procedural extensions (SQL/PSM).
SQL:2003 2003 Window functions, MERGE, identity columns (GENERATED ... AS IDENTITY), sequences, XML.
SQL:2006, SQL:2008 2006, 2008 XML queries; OFFSET ... FETCH, TRUNCATE, INSTEAD OF triggers.
SQL:2011 2011 Temporal tables (system-versioned and application time), more window-function options.
SQL:2016 2016 JSON functions and the SQL/JSON data model, row pattern recognition (MATCH_RECOGNIZE), polymorphic table functions.
SQL:2023 2023 Property graph queries (GRAPH_TABLE, SQL/PGQ), a native JSON data type and more JSON syntax, and the ANY_VALUE aggregate.

Only some of the SQL:2023 features are widely implemented yet.

Try it

SQL Standard features that SQLite runs ยท database: company

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

SQL SQL:1999 and SQL:2003 features ยท database: company

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

Standard or vendor extension?

Feature Standard form Typical vendor form
Row limit OFFSET/FETCH FIRST n ROWS ONLY LIMIT n, TOP n
Upsert MERGE ON CONFLICT, ON DUPLICATE KEY UPDATE
Auto keys GENERATED ... AS IDENTITY AUTO_INCREMENT, SERIAL, AUTOINCREMENT
Current time CURRENT_TIMESTAMP NOW(), GETDATE(), SYSDATE
Concatenation a || b CONCAT(), +
Boolean BOOLEAN BIT, TINYINT(1), NUMBER(1)
Date parts EXTRACT(YEAR FROM x) strftime, YEAR(), DATEPART

Dialect differences

Each major product aims at a different slice of the standard.

  • PostgreSQL is generally the closest to the standard and documents which feature identifiers it supports.
  • Oracle pioneered much of SQL but kept its own syntax for compatibility (ROWNUM, (+) outer joins, DUAL); newer versions add standard forms.
  • SQL Server uses T-SQL, a procedural dialect with its own functions (TOP, DATEADD, ISNULL), and has adopted OFFSET ... FETCH, window functions and JSON.
  • MySQL and MariaDB historically favored speed and simplicity, with lenient defaults, and moved toward the standard with CTEs, window functions and stricter modes in version 8.
  • SQLite implements a large, practical subset with flexible typing; it has no stored procedures, MERGE or TRUNCATE.

Usage notes

  • Learn the standard form first. It is the part that transfers, and many dialect forms are renamed versions of the standard idea.
  • Standard does not mean available. A feature in SQL:2016 may be absent from your version; check release notes.
  • The standard is not free. The documents are sold by ISO, which is why vendors document their own behavior instead of quoting it.
  • "ANSI SQL" is a common nickname; in practice it usually means SQL-92.
  • Do not trust a vendor claim of compliance. It generally covers the core; optional features vary.
  • Portability tools. ORMs and query builders translate common forms between dialects, but cannot hide differences in types, NULL handling and locking.