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
Press Run (or Ctrl + Enter) to see the result here.
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 adoptedOFFSET ... 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,
MERGEorTRUNCATE.
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.