SQL Standard Versions and SQL Dialects

Lesson 1.8 · Reading time: ~8 min

The SQL standard is an international specification of the SQL language, published by ISO and ANSI as ISO/IEC 9075. The first version appeared in 1986, and the latest is SQL:2023. In this lesson you'll see what each version of the standard added, from explicit JOIN syntax to JSON and graph queries, and why every database speaks its own SQL dialect. By the end, you'll know which parts of a query are portable and which depend on the database.

In the previous lesson you saw what SQL is and how a basic query is built. SQL is not owned by one vendor: its core is defined by a standard, and each database implements that standard in its own way.

This explains a common surprise: a query that works in PostgreSQL may fail in SQL Server or MySQL. Knowing the standard helps you write queries that move between databases with fewer changes.

What Is the SQL Standard?

SQL was created at IBM in the 1970s. By the mid-1980s several vendors had their own versions of the language, so ANSI and then ISO published a common specification: SQL-86. Since then the standard has been revised regularly by the ISO/IEC committee JTC 1/SC 32.

The standard is large and consists of several parts. Part 2, SQL/Foundation, defines the core language: tables, queries, data changes, transactions. Other parts cover XML, external data, multidimensional arrays and, since 2023, property graphs.

A few things are worth knowing:

• The standard defines a mandatory set of features called Core SQL; everything else is optional.
• No database implements the whole standard, and every database adds its own extensions.
• The standard documents are paid, so in practice developers learn SQL from database documentation.

SQL Standard Versions and What They Added

Each version kept the previous ones and added new features. The table lists the changes you'll meet in this course:

SQL-92 is still an important reference point: it defined the syntax that almost every relational database supports. But the modern standard is much richer, and most features of analytical SQL appeared after 1999.

What's New in SQL:2023: JSON and Graph Queries

SQL:2023 lets one database work with relational, document and graph data in the same query.

The JSON data type

SQL:2016 added functions that read JSON stored as text. SQL:2023 adds a real JSON type, so the database checks that a column holds valid JSON and can store it in an efficient binary form. MySQL, MariaDB and PostgreSQL (jsonb) had their own JSON types before the standard; Oracle 21c and later follow the standard type.

Property graph queries (SQL/PGQ)

A property graph is a set of vertices and edges, for example actors and films connected by "acted in". Graph databases such as Neo4j query such data with special languages. SQL/PGQ, the new Part 16 of the standard, lets you define a graph on top of ordinary tables and query it in SQL.

In Sakila, actor and film become vertices and film_actor becomes the edge between them:

CREATE PROPERTY GRAPH film_graph
  VERTEX TABLES (actor KEY (actor_id), film KEY (film_id))
  EDGE TABLES (
    film_actor KEY (actor_id, film_id)
      SOURCE KEY (actor_id) REFERENCES actor (actor_id)
      DESTINATION KEY (film_id) REFERENCES film (film_id)
      LABEL acted_in
  );

Note: the graph doesn't copy data; it describes how existing tables connect.

The GRAPH_TABLE operator finds paths with a pattern. This query returns the actors who appeared in a film together with Penelope Guiness:

SELECT DISTINCT partner
FROM GRAPH_TABLE (film_graph
  MATCH (a IS actor) -[IS acted_in]-> (f IS film) <-[IS acted_in]- (b IS actor)
  WHERE a.first_name = 'PENELOPE'
    AND a.last_name = 'GUINESS'
    AND b.actor_id <> a.actor_id
  COLUMNS (b.first_name || ' ' || b.last_name AS partner)
);

Result: one row per co-actor. The pattern reads like the picture: actor → film ← actor.

The same result can be written with two joins of film_actor, which you'll learn in the JOINs module. The graph syntax becomes much shorter for longer paths, such as "actors connected through two films". Today SQL/PGQ is supported by Oracle Database 23ai; support in PostgreSQL is in development.

SQL Dialects: How Databases Differ from the Standard

A SQL dialect is the version of SQL implemented by a particular database: the standard features it supports plus its own extensions. Basic queries look the same everywhere, but details differ. For example, here are the five longest films in different databases:

-- Standard (PostgreSQL, Oracle)
SELECT title, length
FROM film
ORDER BY length DESC
FETCH FIRST 5 ROWS ONLY;

-- MySQL, MariaDB, SQLite, PostgreSQL
SELECT title, length
FROM film
ORDER BY length DESC
LIMIT 5;

-- SQL Server
SELECT TOP 5 title, length
FROM film
ORDER BY length DESC;

Result: the same five films in each case. LIMIT and TOP appeared before the standard FETCH FIRST and are still more common.

Other frequent differences:

PostgreSQL is usually considered the closest to the standard. MySQL has the most differences, though recent versions added CTEs, window functions and other standard features.

How to Write Portable SQL

Moving a schema to another database always needs some work, but these habits keep queries closer to the standard:

• Prefer standard constructs when the database supports them: CASE instead of IF(), COALESCE instead of IFNULL or ISNULL, explicit JOIN ... ON.
• Use CAST(x AS type) instead of shortcuts like x::type in PostgreSQL.
• Avoid quoted names: lowercase names without spaces don't need quotes in any database.
• Keep database-specific parts, such as row limits or date functions, in clearly marked places.

On this site the same tasks can be solved on several databases, so you'll see these differences in practice. The next module starts writing queries with SELECT, the statement behind every data retrieval.

Key Takeaways:

• The SQL standard is ISO/IEC 9075; it started with SQL-86, and the current version is SQL:2023.
• SQL-92 defined the common base, SQL:1999 and SQL:2003 added CTEs and window functions, SQL:2016 added JSON functions.
• SQL:2023 adds the JSON type and property graph queries (SQL/PGQ) over ordinary tables.
• A SQL dialect is the standard as implemented by one database, plus its extensions.
• Core queries are portable; row limits, string functions, auto-increment and quoting differ most.

Frequently Asked Questions

What is the latest version of the SQL standard?

The latest version is SQL:2023 (ISO/IEC 9075:2023), published in June 2023. It added the JSON data type, property graph queries (SQL/PGQ) and several smaller features such as GREATEST, LEAST and ANY_VALUE.

What is the difference between ANSI SQL and ISO SQL?

Today they are the same standard. ANSI published the first version in 1986, ISO adopted it in 1987, and since then ANSI adopts the ISO standard ISO/IEC 9075. "ANSI SQL" is just the common name for standard SQL.

Does any database fully support the SQL standard?

No. Every database supports Core SQL and a different set of optional features, and adds its own extensions. PostgreSQL is usually considered the closest to the standard.

Is it still worth learning SQL-92?

SQL-92 syntax is the base that works in almost every database, so it's a good start. But modern SQL also includes CTEs, window functions and JSON, and analytical work uses them every day.

Interview Questions

What is a SQL dialect?

A SQL dialect is the version of SQL implemented by a specific database: a subset of the ISO standard plus vendor extensions. Examples are T-SQL in SQL Server and the SQL dialects of Oracle, MySQL and PostgreSQL. The core syntax is shared; details like row limits, functions and data types differ.

Name some differences between MySQL and SQL Server syntax.

MySQL limits rows with LIMIT, SQL Server with TOP or OFFSET ... FETCH. MySQL joins strings with CONCAT, SQL Server also with +; MySQL quotes names with backticks, SQL Server with square brackets. Auto-increment keys are AUTO_INCREMENT in MySQL and IDENTITY in SQL Server.

What did SQL:2023 add to the standard?

A native JSON data type and SQL/PGQ, property graph queries over relational tables with CREATE PROPERTY GRAPH and GRAPH_TABLE. It also added smaller features such as GREATEST, LEAST and ANY_VALUE.

How do you make a SQL query portable between databases?

Use standard constructs: explicit JOIN ... ON, CASE, COALESCE, CAST. Avoid vendor-specific functions and quoted identifiers, and isolate the parts that must differ, such as row limits and date functions.