Версии стандарта SQL и диалекты SQL

Q: Какая последняя версия стандарта SQL?

Последняя версия - SQL:2023 (ISO/IEC 9075:2023), опубликована в июне 2023 года. Она добавила тип данных JSON, графовые запросы (SQL/PGQ) и несколько небольших возможностей, например GREATEST, LEAST и ANY_VALUE.

Урок 1.8 · Время чтения: ~8 мин

Стандарт SQL - это международная спецификация языка SQL, которую публикуют ISO и ANSI под номером ISO/IEC 9075. Первая версия вышла в 1986 году, последняя - SQL:2023. В этом уроке вы узнаете, что добавляла каждая версия стандарта, от явного синтаксиса JOIN до JSON и графовых запросов, и почему у каждой СУБД свой диалект SQL. К концу урока вы будете понимать, какие части запроса переносимы, а какие зависят от СУБД.

В предыдущем уроке вы увидели, что такое SQL и из чего состоит базовый запрос. SQL не принадлежит одному производителю: его основу задаёт стандарт, а каждая СУБД реализует этот стандарт по-своему.

Отсюда частое удивление: запрос, который работает в PostgreSQL, может не выполниться в SQL Server или MySQL. Знание стандарта помогает писать запросы, которые переносятся между СУБД с меньшими изменениями.

Хронология версий стандарта SQL от SQL-86 до SQL:2023 с ключевыми возможностями каждой версии и диалекты MySQL, PostgreSQL, SQL Server, Oracle и SQLite на основе общего стандарта

Что такое стандарт SQL?

SQL создали в IBM в 1970-х годах. К середине 1980-х у нескольких производителей были свои версии языка, поэтому ANSI, а затем ISO опубликовали общую спецификацию: SQL-86. С тех пор стандарт регулярно обновляет комитет ISO/IEC JTC 1/SC 32.

Стандарт большой и состоит из нескольких частей. Часть 2, SQL/Foundation, описывает основу языка: таблицы, запросы, изменение данных, транзакции. Другие части посвящены XML, внешним данным, многомерным массивам и, с 2023 года, графам свойств.

Полезно знать несколько фактов:

Стандарт задаёт обязательный набор возможностей - Core SQL; всё остальное необязательно.
Ни одна СУБД не реализует стандарт целиком, и каждая добавляет свои расширения.
Документы стандарта платные, поэтому на практике SQL изучают по документации СУБД.

Версии стандарта SQL и что они добавили

Каждая версия сохраняла предыдущие и добавляла новые возможности. В таблице - изменения, которые встретятся вам в этом курсе:

Версия	Главные нововведения	В курсе
SQL-86, SQL-89	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE TABLE`, первичные и внешние ключи	Модуль 2
SQL-92	явный `JOIN ... ON`, `LEFT`, `RIGHT` и `FULL OUTER JOIN`, `CASE`, `CAST`, типы `DATE` и `TIMESTAMP`	JOIN, CASE
SQL:1999	`WITH` и рекурсивные запросы (CTE), `BOOLEAN`, триггеры, `ROLLUP` и `CUBE`	CTE, рекурсивные CTE
SQL:2003	оконные функции, `MERGE`, столбцы-идентификаторы, последовательности, SQL/XML	Оконные функции
SQL:2006	запросы к XML на XQuery	-
SQL:2008	`FETCH FIRST n ROWS ONLY`, `OFFSET`, `TRUNCATE TABLE`	LIMIT и FETCH FIRST
SQL:2011	темпоральные таблицы (история строк), `LAG`, `LEAD`, `FIRST_VALUE`, `LAST_VALUE`	LAG и LEAD
SQL:2016	функции JSON (`JSON_VALUE`, `JSON_QUERY`, `JSON_TABLE`), `LISTAGG`, `MATCH_RECOGNIZE`	Функции JSON
SQL:2019	многомерные массивы (SQL/MDA)	-
SQL:2023	тип данных `JSON`, графовые запросы (SQL/PGQ), `GREATEST`, `LEAST`, `ANY_VALUE`	Сложные типы данных

SQL-92 до сих пор важная точка отсчёта: он задал синтаксис, который поддерживает почти любая реляционная СУБД. Но современный стандарт гораздо богаче, и большинство возможностей аналитического SQL появились после 1999 года.

Что нового в SQL:2023: JSON и графовые запросы

SQL:2023 позволяет одной СУБД работать с реляционными, документными и графовыми данными в одном запросе.

Тип данных JSON

SQL:2016 добавил функции, которые читают JSON, хранящийся как текст. SQL:2023 добавляет настоящий тип JSON: СУБД проверяет, что в столбце корректный JSON, и может хранить его в эффективном двоичном виде. В MySQL, MariaDB и PostgreSQL (jsonb) свои типы JSON появились раньше стандарта; Oracle начиная с версии 21c следует стандартному типу.

Графовые запросы (SQL/PGQ)

Граф свойств - это набор вершин и рёбер, например актёры и фильмы, связанные отношением «снимался в». Графовые СУБД, такие как Neo4j, работают с такими данными на специальных языках. SQL/PGQ, новая часть 16 стандарта, позволяет описать граф поверх обычных таблиц и обращаться к нему на SQL.

В Sakila таблицы actor и film становятся вершинами, а film_actor - ребром между ними:

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
  );

Примечание: граф не копирует данные, он описывает, как связаны существующие таблицы.

Оператор GRAPH_TABLE находит пути по шаблону. Этот запрос возвращает актёров, которые снимались в одном фильме с 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)
);

Результат: по одной строке на каждого партнёра по фильмам. Шаблон читается как рисунок: актёр → фильм ← актёр.

Тот же результат можно получить двумя соединениями film_actor, которые вы изучите в модуле о JOIN. Для более длинных путей, например «актёры, связанные через два фильма», графовый синтаксис намного короче. Сегодня SQL/PGQ поддерживает Oracle Database 23ai; поддержка в PostgreSQL находится в разработке.

Диалекты SQL: чем СУБД отличаются от стандарта

Диалект SQL - это версия SQL, которую реализует конкретная СУБД: поддерживаемые ею возможности стандарта плюс собственные расширения. Простые запросы везде выглядят одинаково, но детали различаются. Например, пять самых длинных фильмов в разных СУБД:

-- Стандарт (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;

Результат: в каждом случае те же пять фильмов. LIMIT и TOP появились раньше стандартного FETCH FIRST и до сих пор встречаются чаще.

Другие частые различия:

Задача	Стандарт	MySQL / MariaDB	PostgreSQL	SQL Server	Oracle	SQLite
Первые N строк	`FETCH FIRST`	`LIMIT`	`LIMIT`, `FETCH FIRST`	`TOP`, `OFFSET ... FETCH`	`FETCH FIRST`	`LIMIT`
Склеить строки	`a \|\| b`	`CONCAT(a, b)`	`a \|\| b`	`a + b`, `CONCAT`	`a \|\| b`	`a \|\| b`
Взять имя в кавычки	`"name"`	`name`	`"name"`	`[name]`, `"name"`	`"name"`	`"name"`
Автоинкрементный ключ	`GENERATED AS IDENTITY`	`AUTO_INCREMENT`	`GENERATED AS IDENTITY`, `SERIAL`	`IDENTITY(1, 1)`	`GENERATED AS IDENTITY`	`INTEGER PRIMARY KEY`
Рекурсивный CTE	`WITH RECURSIVE`	`WITH RECURSIVE`	`WITH RECURSIVE`	`WITH`	`WITH`	`WITH RECURSIVE`
Логический тип	`BOOLEAN`	`BOOLEAN` = `TINYINT(1)`	`BOOLEAN`	`BIT`	`BOOLEAN` (23ai)	0 и 1
`FULL OUTER JOIN`	да	нет	да	да	да	да (3.39+)

Ближе всего к стандарту обычно считают PostgreSQL. Больше всего отличий у MySQL, хотя в последних версиях в нём появились CTE, оконные функции и другие стандартные возможности.

Как писать переносимый SQL

Перенос схемы в другую СУБД всегда требует работы, но несколько привычек держат запросы ближе к стандарту:

Выбирайте стандартные конструкции, если СУБД их поддерживает: CASE вместо IF(), COALESCE вместо IFNULL или ISNULL, явный JOIN ... ON.
Используйте CAST(x AS type) вместо сокращений вроде x::type в PostgreSQL.
Избегайте имён в кавычках: имена в нижнем регистре без пробелов не требуют кавычек ни в одной СУБД.
Держите зависящие от СУБД части, например ограничение строк или функции дат, в явно отмеченных местах.

На этом сайте одни и те же задачи можно решать на нескольких СУБД, так что эти различия вы увидите на практике. В следующем модуле мы начнём писать запросы с SELECT - оператора, с которого начинается любое извлечение данных.

Ключевые выводы:

Стандарт SQL - это ISO/IEC 9075; он начался с SQL-86, текущая версия - SQL:2023.
SQL-92 задал общую основу, SQL:1999 и SQL:2003 добавили CTE и оконные функции, SQL:2016 - функции JSON.
SQL:2023 добавляет тип JSON и графовые запросы (SQL/PGQ) поверх обычных таблиц.
Диалект SQL - это стандарт в реализации конкретной СУБД плюс её расширения.
Базовые запросы переносимы; сильнее всего различаются ограничение строк, строковые функции, автоинкремент и кавычки.

Часто задаваемые вопросы

Какая последняя версия стандарта SQL?

Последняя версия - SQL:2023 (ISO/IEC 9075:2023), опубликована в июне 2023 года. Она добавила тип данных JSON, графовые запросы (SQL/PGQ) и несколько небольших возможностей, например GREATEST, LEAST и ANY_VALUE.

Чем ANSI SQL отличается от ISO SQL?

Сегодня это один и тот же стандарт. ANSI опубликовал первую версию в 1986 году, ISO принял её в 1987-м, и с тех пор ANSI принимает стандарт ISO/IEC 9075. «ANSI SQL» - просто привычное название стандартного SQL.

Есть ли СУБД, которая полностью поддерживает стандарт SQL?

Нет. Каждая СУБД поддерживает Core SQL и свой набор необязательных возможностей, а также добавляет собственные расширения. Ближе всего к стандарту обычно считают PostgreSQL.

Стоит ли сегодня учить SQL-92?

Синтаксис SQL-92 - основа, которая работает почти в любой СУБД, поэтому с него удобно начинать. Но современный SQL включает также CTE, оконные функции и JSON, и в аналитике они используются каждый день.

Вопросы на собеседовании

Что такое диалект SQL?

Диалект SQL - это версия SQL, которую реализует конкретная СУБД: часть стандарта ISO плюс расширения производителя. Примеры: T-SQL в SQL Server и диалекты SQL в Oracle, MySQL и PostgreSQL. Основной синтаксис общий; различаются детали вроде ограничения строк, функций и типов данных.

Назовите несколько различий синтаксиса MySQL и SQL Server.

MySQL ограничивает число строк через LIMIT, SQL Server - через TOP или OFFSET ... FETCH. MySQL склеивает строки функцией CONCAT, SQL Server - ещё и оператором +; MySQL берёт имена в обратные кавычки, SQL Server - в квадратные скобки. Автоинкрементные ключи в MySQL задаются через AUTO_INCREMENT, в SQL Server - через IDENTITY.

Что добавил в стандарт SQL:2023?

Встроенный тип данных JSON и SQL/PGQ - графовые запросы к реляционным таблицам с CREATE PROPERTY GRAPH и GRAPH_TABLE. Также появились небольшие возможности: GREATEST, LEAST и ANY_VALUE.

Как сделать SQL-запрос переносимым между СУБД?

Использовать стандартные конструкции: явный JOIN ... ON, CASE, COALESCE, CAST. Избегать специфичных для СУБД функций и идентификаторов в кавычках, а части, которые обязаны различаться (ограничение строк, функции дат), выносить в явно отмеченные места.