3.07. Справочник по SQL

В РАЗРАБОТКЕНЕ ДЛЯ НОВИЧКОВНЕ ОБЯЗАТЕЛЬНО

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Справочник SQL

Версия: ANSI SQL:2016 (с учётом общепринятых расширений)

1. DDL — Data Definition Language

Операции определения и модификации структуры данных.

`CREATE`

Создание объектов БД.

Таблица

CREATE TABLE [IF NOT EXISTS] table_name (
    column_name data_type [CONSTRAINT constraint_name] [column_constraint],
    ...,
    [table_constraint]
);

IF NOT EXISTS — необязательно, поддерживается в PostgreSQL, MySQL, SQLite; в T-SQL используется IF NOT EXISTS (SELECT * FROM sys.objects WHERE ...) или BEGIN TRY CREATE ... END TRY.
column_constraint:
NOT NULL, NULL, UNIQUE, PRIMARY KEY, CHECK (condition), DEFAULT value, REFERENCES ref_table (col) [ON DELETE | UPDATE action]
table_constraint:
PRIMARY KEY (col1, col2), FOREIGN KEY (col) REFERENCES ..., UNIQUE (col), CHECK (condition)

Индекс

CREATE [UNIQUE] INDEX [IF NOT EXISTS] index_name
ON table_name (column_name [ASC | DESC] [, ...])
[INCLUDE (included_column, ...)]  -- T-SQL, PostgreSQL 11+
[WHERE condition];                 -- filtered index: T-SQL, PostgreSQL (partial index)

Представление

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW view_name [(alias_col, ...)] AS
SELECT ...;
-- WITH [LOCAL | CASCADE] CHECK OPTION — в ANSI, поддерживается в PostgreSQL, Oracle

Схема

CREATE SCHEMA [IF NOT EXISTS] schema_name
[AUTHORIZATION user_name];

Домен (PostgreSQL, Firebird)

CREATE DOMAIN domain_name AS base_type
[CONSTRAINT constraint_name] [CHECK (condition)];

Последовательность (serial, identity support)

-- ANSI: 
CREATE SEQUENCE sequence_name
[AS data_type]  -- BIGINT по умолчанию
[START WITH value]
[INCREMENT BY value]
[MINVALUE value | NO MINVALUE]
[MAXVALUE value | NO MAXVALUE]
[CACHE value]
[CYCLE | NO CYCLE];

-- T-SQL (IDENTITY property — альтернатива):
-- column_name INT IDENTITY(start, increment) PRIMARY KEY

-- PostgreSQL/Oracle: может использоваться в DEFAULT:
-- column_name INT DEFAULT nextval('sequence_name')

Тип данных (PostgreSQL, Oracle)

CREATE TYPE type_name AS (
    field_name data_type,
    ...
);

-- Или ENUM:
CREATE TYPE enum_name AS ENUM ('val1', 'val2', ...);

Триггер (общая форма)

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {event [, ...]}
ON table_name
[REFERENCING {OLD [AS] old_alias | NEW [AS] new_alias}]
[FOR EACH {ROW | STATEMENT}]
[WHEN (condition)]
EXECUTE {FUNCTION function_name() | PROCEDURE proc_name()};
-- event: INSERT, UPDATE [OF col], DELETE
-- Поддержка: Oracle (PL/SQL), PostgreSQL (PL/pgSQL), SQL Server (T-SQL, но синтаксис отличается)

`ALTER`

Изменение объектов.

Таблица

ALTER TABLE table_name
    ADD [COLUMN] column_name data_type [column_constraint],
    DROP [COLUMN] column_name [CASCADE | RESTRICT],
    ALTER [COLUMN] column_name {SET DATA TYPE new_type | SET DEFAULT value | DROP DEFAULT | SET NOT NULL | DROP NOT NULL},
    ADD CONSTRAINT constraint_name constraint_definition,
    DROP CONSTRAINT constraint_name [CASCADE],
    RENAME COLUMN old_name TO new_name,            -- PostgreSQL, SQLite
    RENAME TO new_table_name;                      -- PostgreSQL, Oracle

В T-SQL:
ALTER COLUMN column_name new_type [NULL | NOT NULL]
DROP COLUMN column_name [WITH (ONLINE = ON)]
ADD CONSTRAINT ... / DROP CONSTRAINT ...
sp_rename 'old_name', 'new_name', 'COLUMN'

Индекс / представление / последовательность

ALTER INDEX index_name RENAME TO new_name;         -- PostgreSQL
ALTER SEQUENCE seq_name [RESTART [WITH value] | ...];
-- Для представлений: обычно DROP + CREATE или CREATE OR REPLACE

`DROP`

Удаление объектов.

DROP TABLE [IF EXISTS] table_name [CASCADE | RESTRICT];
DROP INDEX [IF EXISTS] index_name;
DROP VIEW [IF EXISTS] view_name [CASCADE];
DROP SCHEMA [IF EXISTS] schema_name [CASCADE | RESTRICT];
DROP SEQUENCE [IF EXISTS] sequence_name [CASCADE];
DROP TYPE [IF EXISTS] type_name [CASCADE];
DROP TRIGGER [IF EXISTS] trigger_name ON table_name;
-- CASCADE: рекурсивно удаляет зависимые объекты (например, FK, views)
-- RESTRICT (по умолчанию в ANSI): запрещает удаление при наличии зависимостей
-- IF EXISTS — поддерживается в PostgreSQL, MySQL, SQLite; в T-SQL — `IF OBJECT_ID('name','U') IS NOT NULL DROP TABLE ...`

`TRUNCATE`

Быстрая очистка таблицы (не DML!).

TRUNCATE TABLE table_name
[RESTART IDENTITY]      -- сброс сиквенсов, связанных с IDENTITY/serial (PostgreSQL, T-SQL)
[IDENTITY RESTART]      -- T-SQL синоним
[CONTINUE IDENTITY]     -- T-SQL: не сбрасывать
[CASCADE];              -- рекурсивно для дочерних таблиц (PostgreSQL)
-- Не вызывает триггеры DELETE (в большинстве СУБД)
-- Не может быть отменён отдельно в рамках транзакции в некоторых СУБД без explicit BEGIN

`RENAME` (не ANSI, но широко используется)

-- PostgreSQL, SQLite
ALTER TABLE old_name RENAME TO new_name;
ALTER TABLE table_name RENAME COLUMN old_col TO new_col;

-- Oracle
RENAME old_name TO new_name;

-- MySQL
RENAME TABLE old_name TO new_name;
ALTER TABLE table_name RENAME COLUMN old_col TO new_col; -- ≥8.0.3

2. DML — Data Manipulation Language

Работа с данными.

`INSERT`

INSERT INTO table_name [(col1, col2, ...)]
    VALUES (val1, val2, ...),
           (val3, val4, ...);

-- Или из SELECT:
INSERT INTO table_name [(col1, col2, ...)]
    SELECT ... FROM ...;

-- Возвращаемые значения (PostgreSQL, Firebird):
INSERT INTO ... RETURNING * | column [, ...];

-- T-SQL: OUTPUT clause
INSERT INTO ...
OUTPUT inserted.col1, inserted.col2 INTO @table_var
VALUES (...);

`UPDATE`

UPDATE table_name
SET col1 = expr1, col2 = expr2
[FROM additional_tables]          -- T-SQL, PostgreSQL (не ANSI)
[WHERE condition]
[RETURNING * | col [, ...]];      -- PostgreSQL, Oracle
-- T-SQL: OUTPUT inserted.*, deleted.*

`DELETE`

DELETE FROM table_name
[WHERE condition]
[RETURNING * | col [, ...]];      -- PostgreSQL, Oracle
-- T-SQL: OUTPUT deleted.*
-- MySQL: LIMIT N (для порционного удаления)

`MERGE` (ANSI — upsert)

MERGE INTO target_table AS T
USING source_table_or_query AS S
ON T.key = S.key
WHEN MATCHED [AND condition] THEN
    UPDATE SET col = S.col, ...
    -- [DELETE] — Oracle, SQL Server
WHEN NOT MATCHED [BY TARGET] [AND condition] THEN
    INSERT (col1, col2) VALUES (S.col1, S.col2)
WHEN NOT MATCHED BY SOURCE [AND condition] THEN
    DELETE
[RETURNING $action, ...];  -- SQL Server
-- Поддержка: SQL Server (полная), Oracle, PostgreSQL ≥15 (MERGE, ограниченная), Snowflake, BigQuery
-- В SQLite: `INSERT ... ON CONFLICT DO UPDATE/IGNORE`
-- В MySQL: `INSERT ... ON DUPLICATE KEY UPDATE`

3. DCL — Data Control Language

Управление доступом и привилегиями.

`GRANT`

GRANT privilege [, ...] ON object_type object_name TO {user | role} [, ...]
[WITH GRANT OPTION];  -- даёт право передавать привилегию дальше

-- Привилегии (зависят от объекта):
-- Для таблиц/представлений:
--   SELECT, INSERT, UPDATE [(col, ...)], DELETE, TRUNCATE, REFERENCES, TRIGGER
-- Для схем:
--   USAGE, CREATE
-- Для базы:
--   CONNECT, CREATE, TEMPORARY (TEMP)
-- Для домена/типа:
--   USAGE
-- Для функций:
--   EXECUTE

-- Примеры:
GRANT SELECT, INSERT ON TABLE employees TO analyst;
GRANT UPDATE (salary) ON employees TO manager;
GRANT USAGE ON SCHEMA public TO developer;
GRANT EXECUTE ON FUNCTION calc_bonus(INT) TO payroll_role;

-- ALL PRIVILEGES — передаёт все доступные привилегии на объект.
-- PUBLIC — особая роль: все пользователи.
GRANT SELECT ON employees TO PUBLIC;

`REVOKE`

REVOKE [GRANT OPTION FOR] privilege [, ...] ON object_type object_name
FROM {user | role} [, ...]
[CASCADE | RESTRICT];

-- CASCADE: отзывает привилегию и у тех, кому она была передана этим пользователем (если WITH GRANT OPTION был использован)
-- RESTRICT (по умолчанию): запрещает отозвать, если привилегия была передана дальше
-- Отзыв всех:
REVOKE ALL PRIVILEGES ON TABLE employees FROM analyst;

`CREATE ROLE` / `DROP ROLE` (не ANSI, но стандарт де-факто)

CREATE ROLE role_name [WITH option [, ...]];
-- PostgreSQL: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, INHERIT, REPLICATION, CONNECTION LIMIT n, PASSWORD 'pwd', VALID UNTIL 'timestamp'
-- SQL Server: CREATE LOGIN, CREATE USER, ALTER ROLE role_name ADD MEMBER user

DROP ROLE [IF EXISTS] role_name;

⚠️ В T-SQL управление доступом разделено:

CREATE LOGIN (серверный уровень),

CREATE USER (в контексте БД),

ALTER ROLE ... ADD MEMBER,

GRANT/REVOKE/DENY — DENY приоритетнее GRANT.

4. TCL — Transaction Control Language

Управление транзакциями.

`BEGIN` / `START TRANSACTION`

-- ANSI:
START TRANSACTION [ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}]
                  [READ {ONLY | WRITE}];

-- PostgreSQL, MySQL:
BEGIN [WORK | TRANSACTION];
-- Необязательно — в большинстве СУБД DML-команды неявно запускают транзакцию, если autocommit = OFF.

-- T-SQL:
BEGIN TRANSACTION [transaction_name];
-- Можно вкладывать (savepoints под капотом), но commit/rollback действуют на весь блок, если не указано savepoint.

`COMMIT`

COMMIT [WORK | TRANSACTION];           -- ANSI
-- T-SQL: COMMIT [TRANSACTION [name]]
-- PostgreSQL: COMMIT AND CHAIN — продолжает новую транзакцию

`ROLLBACK`

ROLLBACK [WORK | TRANSACTION];         -- откат всей транзакции
ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] savepoint_name;

`SAVEPOINT`

SAVEPOINT savepoint_name;
-- В T-SQL: SAVE TRANSACTION savepoint_name
-- Откат к точке:
ROLLBACK TO savepoint_name;  -- ANSI, PostgreSQL
ROLLBACK TRANSACTION savepoint_name;  -- T-SQL

-- Удаление точки (необязательно, удаляются при COMMIT/ROLLBACK):
RELEASE SAVEPOINT savepoint_name;  -- PostgreSQL
-- В T-SQL точка остаётся до конца транзакции.

Уровни изоляции (стандартные и поведение)

Уровень	Dirty Read	Non-Repeatable Read	Phantom Read	Сериализуемость
`READ UNCOMMITTED`	да	да	да	нет
`READ COMMITTED`	нет	да	да	нет
`REPEATABLE READ`	нет	нет	зависит¹	нет
`SERIALIZABLE`	нет	нет	нет	да

¹ — в PostgreSQL REPEATABLE READ предотвращает phantom reads за счёт snapshot isolation; в SQL Server — нет (требуется SERIALIZABLE).

5. DQL — Data Query Language

Извлечение данных. Основной оператор — SELECT.

Обобщённый синтаксис (логический порядок обработки):

[WITH cte_name [(col, ...)] AS (SELECT ...) [, ...]]
SELECT [ALL | DISTINCT] 
       [TOP n [PERCENT]]                    -- T-SQL
       [ALL | DISTINCT ON (expr)] expr AS alias, ...  -- PostgreSQL
FROM table_source [alias]
  [JOIN another_table ON condition | USING (col)]
WHERE condition
GROUP BY expr [, ...]
HAVING group_condition
WINDOW window_name AS (window_spec) [, ...]
ORDER BY expr [ASC | DESC] [NULLS {FIRST | LAST}]  -- NULLS — PostgreSQL, Oracle
LIMIT n [OFFSET m]               -- PostgreSQL, MySQL, SQLite
FETCH {FIRST | NEXT} n {ROW | ROWS} ONLY  -- ANSI, SQL Server ≥2012, PostgreSQL
FOR {UPDATE | SHARE} [OF table [, ...]] [NOWAIT | SKIP LOCKED];  -- блокировки

🔹 Физический порядок выполнения:
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → WINDOW → ORDER BY → LIMIT/FETCH.

`FROM` и `JOIN`

Типы JOIN (ANSI):

Тип	Синтаксис	Примечание
Внутреннее соединение	`INNER JOIN` или `JOIN`	Только совпадающие строки
Левое внешнее	`LEFT [OUTER] JOIN`	Все строки из левой таблицы
Правое внешнее	`RIGHT [OUTER] JOIN`	Все строки из правой таблицы
Полное внешнее	`FULL [OUTER] JOIN`	Все строки из обеих таблиц
Перекрёстное (декартово)	`CROSS JOIN`	Без условия — полное произведение
Самосоединение	`table t1 JOIN table t2 ON ...`	Та же таблица, разные алиасы

Расширения:

LATERAL (PostgreSQL, Oracle 12c+, SQL Server APPLY):

SELECT *
FROM employees e
LEFT JOIN LATERAL (
    SELECT * FROM salaries s
    WHERE s.emp_id = e.id
    ORDER BY s.effective_date DESC
    LIMIT 1
) AS latest_sal ON true;

T-SQL OUTER APPLY / CROSS APPLY — аналог LATERAL:

SELECT *
FROM employees e
OUTER APPLY (
    SELECT TOP 1 * FROM salaries s
    WHERE s.emp_id = e.id
    ORDER BY s.effective_date DESC
) AS latest_sal;

`WHERE`

Фильтрация строк до группировки.
Поддерживает:

Сравнения (=, <>, <, >, <=, >=, !=)
Логические (AND, OR, NOT)
BETWEEN a AND b — включительно
IN (val1, val2, ...) / NOT IN (...)
LIKE 'pattern' / ILIKE (PostgreSQL, case-insensitive)
% — любое кол-во символов, _ — ровно один
SIMILAR TO (PostgreSQL, SQL:2008) — регулярные выражения (ограниченные)
~, ~*, !~, !~* (PostgreSQL — полноценные regex)
IS [NOT] NULL
EXISTS (subquery)
ANY / SOME / ALL с подзапросами:
value > ALL (SELECT ...) — строго больше любого

`GROUP BY`

Группировка строк. Требует агрегации для всех неключевых колонок в SELECT.

Агрегатные функции (стандартные):

Функция	Описание
`COUNT(*)`	число строк (включая NULL)
`COUNT(col)`	число ненулевых значений в col
`SUM(col)`, `AVG(col)`	сумма, среднее (только числовые)
`MIN(col)`, `MAX(col)`	мин/макс (работает с датами, строками)
`ARRAY_AGG(col)`	PostgreSQL: массив значений
`STRING_AGG(col, delimiter)`	PostgreSQL/SQL Server: строка через разделитель
`GROUP_CONCAT(col SEPARATOR delim)`	MySQL — аналог
`BOOL_AND(col)`, `BOOL_OR(col)`	PostgreSQL: логические агрегаты
`JSON_AGG(col)`	PostgreSQL/MySQL 5.7+: JSON-массив
`JSON_OBJECT_AGG(k, v)`	PostgreSQL/MySQL: JSON-объект

`GROUPING SETS`, `ROLLUP`, `CUBE` (ANSI, PostgreSQL, SQL Server, Oracle)

-- GROUPING SETS: явное перечисление групп
SELECT dept, region, SUM(sales)
FROM t
GROUP BY GROUPING SETS ( (dept, region), (dept), (region), () );

-- ROLLUP: иерархическая агрегация (сверху вниз)
GROUP BY ROLLUP (A, B, C) 
-- эквивалентно: (A,B,C), (A,B), (A), ()

-- CUBE: все комбинации
GROUP BY CUBE (A, B) 
-- эквивалентно: (A,B), (A), (B), ()

`GROUPING()` — идентификатор уровня агрегации

SELECT 
    GROUPING(dept) AS g_dept,
    dept,
    SUM(sales)
FROM t
GROUP BY ROLLUP (dept);
-- g_dept = 1, если dept — NULL из агрегата («итоговая» строка)

`HAVING`

Фильтрация после группировки. Допускает агрегаты:

SELECT dept, AVG(salary)
FROM employees
GROUP BY dept
HAVING COUNT(*) > 5 AND AVG(salary) > 70000;

`WINDOW` и оконные функции

Объявление окна:

SELECT 
    emp_id,
    salary,
    AVG(salary) OVER w AS dept_avg,
    ROW_NUMBER() OVER w AS rn
FROM employees
WINDOW w AS (PARTITION BY dept_id ORDER BY salary DESC);

Стандартные оконные функции:

Категория	Функции
Ранжирование	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE(n)`
Агрегатные	`SUM()`, `AVG()`, `MIN()`, `MAX()`, `COUNT()` — с `OVER`
Смещения	`LAG(col, offset, default)`, `LEAD(col, offset, default)`, `FIRST_VALUE()`, `LAST_VALUE()`, `NTH_VALUE(col, n)`
Статистические	`PERCENT_RANK()`, `CUME_DIST()`, `PERCENTILE_CONT(frac)`, `PERCENTILE_DISC(frac)`

Спецификация окна:

OVER (
    [PARTITION BY expr, ...]
    [ORDER BY expr [ASC | DESC] [NULLS {FIRST | LAST}]]
    [frame_clause]
)
-- frame_clause:
-- RANGE | ROWS | GROUPS
-- { UNBOUNDED PRECEDING | CURRENT ROW | offset PRECEDING | offset FOLLOWING }
-- [BETWEEN start AND end]
-- Например:
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
-- RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW  -- для временных типов

⚠️ RANGE интерпретирует смещение по значению, ROWS — по числу строк.
Пример: ORDER BY ts RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW — все строки за последний час, даже если их 1000.

`SELECT` расширения

`DISTINCT ON` (PostgreSQL)

SELECT DISTINCT ON (dept_id) dept_id, emp_id, hire_date
FROM employees
ORDER BY dept_id, hire_date DESC;  -- обязательно ORDER BY по ключу DISTINCT ON
-- Возвращает первую строку в каждой группе dept_id

`TOP` (T-SQL), `LIMIT` (ANSI/PostgreSQL/MySQL), `FETCH` (ANSI)

-- T-SQL:
SELECT TOP 10 * FROM orders ORDER BY total DESC;
SELECT TOP 10 PERCENT * FROM orders ORDER BY total DESC;

-- ANSI / PostgreSQL ≥8.4 / SQL Server ≥2012:
SELECT * FROM orders
ORDER BY total DESC
FETCH FIRST 10 ROWS ONLY;

-- С пропуском:
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

`FOR UPDATE`, `FOR SHARE`

-- Блокировка строк для обновления (PostgreSQL, Oracle):
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- T-SQL: использует табличные хинты:
SELECT * FROM accounts WITH (UPDLOCK, ROWLOCK) WHERE id = 1;

-- SKIP LOCKED / NOWAIT:
SELECT * FROM queue FOR UPDATE SKIP LOCKED;  -- брать только незаблокированные

6. Системные / служебные команды (не ANSI, но критичны)

Получение метаданных

СУБД	Команда / Запрос
PostgreSQL	`\dt`, `\d table`, `\dv`, `\df`, `SELECT * FROM information_schema.tables`, `pg_class`, `pg_attribute`
MySQL	`SHOW TABLES`, `DESCRIBE table`, `SHOW CREATE TABLE`, `information_schema`
SQL Server	`sp_help 'table'`, `SELECT * FROM sys.tables`, `sys.columns`, `sys.objects`
Oracle	`SELECT * FROM user_tables`, `all_tab_columns`, `dba_...`

Управление сессиями / настройками

-- ANSI: SET [SESSION | LOCAL] parameter = value;
SET TIME ZONE 'UTC';
SET search_path TO myschema, public;  -- PostgreSQL
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- T-SQL:
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

Условное выполнение (в скриптах)

PostgreSQL:

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'logs') THEN
    CREATE TABLE logs (...);
  END IF;
END $$;

T-SQL:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'logs' AND type = 'U')
    CREATE TABLE logs (...);

7. CTE — Common Table Expressions

Временные именованные результаты запроса, существующие только в рамках одного SELECT/INSERT/UPDATE/DELETE/MERGE.

Базовый синтаксис

WITH cte_name [(col_alias, ...)] AS (
    SELECT ...
)
SELECT ... FROM cte_name;

Особенности:

Можно объявлять несколько CTE через запятую:

WITH
  sales_2024 AS (SELECT * FROM sales WHERE year = 2024),
  top_customers AS (SELECT cust_id, SUM(amount) AS total FROM sales_2024 GROUP BY cust_id HAVING SUM(amount) > 10000)
SELECT c.name, tc.total
FROM customers c
JOIN top_customers tc ON c.id = tc.cust_id;

CTE могут ссылаться друг на друга только вперёд (последовательно).
В PostgreSQL/SQL Server можно использовать MATERIALIZED / NOT MATERIALIZED (подсказка оптимизатору):
```
WITH cte AS MATERIALIZED (SELECT ...) ...
```

Рекурсивные CTE (`WITH RECURSIVE`)

WITH RECURSIVE tree AS (
    -- Anchor member (база рекурсии)
    SELECT id, parent_id, name, 0 AS level
    FROM nodes
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member (шаг)
    SELECT n.id, n.parent_id, n.name, t.level + 1
    FROM nodes n
    INNER JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, name;

Обязательно UNION ALL (в ANSI; UNION не разрешён в рекурсивной части).
В PostgreSQL/SQL Server: ограничение глубины через MAXRECURSION (T-SQL) или SET work_mem / statement_timeout.
Для предотвращения зацикливания:
- PostgreSQL ≥14: SEARCH BREADTH FIRST BY id SET ordercol, CYCLE id SET is_cycle TO 'Y' DEFAULT 'N' USING pathcol
- Oracle: CONNECT BY NOCYCLE
- T-SQL: OPTION (MAXRECURSION n) (макс. 32767)

8. Подзапросы

Типы:

Тип	Описание	Пример
Скалярный	Возвращает одно значение (одна строка, один столбец)	`SELECT name, (SELECT AVG(salary) FROM employees) AS dept_avg FROM employees`
Коррелированный	Ссылается на внешний запрос	`SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)`
Табличный (в `FROM`)	Возвращает таблицу	`SELECT * FROM (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) AS dept_stats`
Существование (`EXISTS`)	Проверяет наличие строк	`SELECT name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id)`

Оптимизация:

EXISTS предпочтительнее IN при работе с NULL (в IN NULL «отравляет» результат: value NOT IN (a, NULL) → UNKNOWN).
NOT EXISTS надёжнее NOT IN.
Подзапросы в SELECT (скалярные) могут вызывать N+1 — лучше заменять JOIN или оконными функциями.

9. Множественные операции

Объединение результатов нескольких SELECT с одинаковой структурой.

Оператор	ANSI	PostgreSQL	SQL Server	MySQL	Замечание
Объединение без дубликатов	`UNION`	✓	✓	✓	Сортирует для удаления дублей
Объединение с дубликатами	`UNION ALL`	✓	✓	✓	Быстрее, не сортирует
Пересечение	`INTERSECT`	✓	✓	≤8.0.31: нет	Удаляет дубли
Пересечение с дублями	`INTERSECT ALL`	✓	✓	—
Разность	`EXCEPT`	✓	`MINUS` (Oracle)	`EXCEPT`	Удаляет дубли
Разность с дублями	`EXCEPT ALL`	✓	—	—

Синтаксис:

query1
UNION [ALL]
query2
[UNION [ALL] query3 ...]
[ORDER BY ...]  -- только в конце всего выражения
[LIMIT ...];

Важно:

Порядок столбцов и их типы должны быть совместимы (не обязательно идентичны — INTEGER и BIGINT совместимы).
Имена столбцов берутся из первого SELECT.
ORDER BY внутри отдельного SELECT недопустим без LIMIT (и то — только в PostgreSQL с OFFSET 0 или LIMIT ALL для эмуляции).

10. Работа с JSON

Стандарт SQL:2016 (`JSON_VALUE`, `JSON_QUERY`, `JSON_TABLE`)

-- Извлечение скалярного значения
SELECT JSON_VALUE(doc, '$.user.name') AS name FROM logs;

-- Извлечение объекта/массива как JSON-строки
SELECT JSON_QUERY(doc, '$.items') AS items FROM orders;

-- Преобразование JSON-массива в таблицу (ANSI)
SELECT jt.*
FROM orders,
JSON_TABLE(items, '$[*]' COLUMNS (
    id INT PATH '$.id',
    name VARCHAR(50) PATH '$.name',
    price DECIMAL(10,2) PATH '$.price'
)) AS jt;

PostgreSQL (операторы):

Оператор	Описание
`->`	возвращает `json`/`jsonb` (ключ или индекс), напр. `doc->'name'`
`->>`	возвращает `text`, напр. `doc->>'name'`
`#>`	путь как массив: `doc#>'{user,profile,city}'`
`#>>`	путь → `text`: `doc#>>'{user,profile,city}'`
`@>`	содержит: `doc @> '{"type":"order"}'::jsonb`
`<@`	содержится в
`?`	содержит ключ: `'{"a":1}'::jsonb ? 'a'`
`?	`,` ?&`

T-SQL:

-- Проверка валидности
SELECT ISJSON(json_col)

-- Извлечение
SELECT JSON_VALUE(json_col, '$.user.name')
SELECT JSON_QUERY(json_col, '$.items')  -- возвращает JSON-фрагмент

-- Преобразование в таблицу
SELECT * 
FROM OPENJSON(@json, '$.items')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    price DECIMAL(10,2) '$.price'
);

MySQL:

SELECT JSON_EXTRACT(doc, '$.user.name')           -- возвращает JSON-значение ("Timur")
SELECT doc->'$.user.name'                        -- синтаксический сахар
SELECT doc->>'$.user.name'                       -- как строка: Timur
SELECT JSON_UNQUOTE(JSON_EXTRACT(...))           -- аналог ->>
SELECT * FROM JSON_TABLE(...);

11. Полнотекстовый поиск

PostgreSQL (`tsvector`, `tsquery`)

-- Создание колонки или индекса:
ALTER TABLE articles ADD COLUMN title_ts tsvector;
UPDATE articles SET title_ts = to_tsvector('russian', title);
CREATE INDEX idx_title_ts ON articles USING GIN (title_ts);

-- Поиск:
SELECT * FROM articles
WHERE title_ts @@ to_tsquery('russian', 'база & данных');

-- Ранжирование:
SELECT *, ts_rank(title_ts, query) AS rank
FROM articles, to_tsquery('russian', 'SQL') AS query
WHERE title_ts @@ query
ORDER BY rank DESC;

MySQL (`FULLTEXT`)

ALTER TABLE articles ADD FULLTEXT(title, content);
SELECT *, MATCH(title, content) AGAINST('SQL база данных' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('SQL база данных');
-- Режимы: NATURAL LANGUAGE, BOOLEAN, WITH QUERY EXPANSION

SQL Server (`CONTAINS`, `FREETEXT`)

-- Требует полнотекстового каталога и индекса
SELECT * FROM articles
WHERE CONTAINS((title, content), 'FORMSOF(INFLECTIONAL, "develop") AND "database"');

12. Хранимые модули

`CREATE FUNCTION`

-- ANSI-совместимый стиль (PostgreSQL)
CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE SQL
AS $$
    SELECT a + b;
$$;

-- PL/pgSQL (многострочный)
CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    res BIGINT := 1;
    i INT;
BEGIN
    FOR i IN 1..n LOOP
        res := res * i;
    END LOOP;
    RETURN res;
END;
$$;

-- Возвращающая таблицу (PostgreSQL)
CREATE FUNCTION get_employees_by_dept(did INT)
RETURNS TABLE(id INT, name TEXT, salary NUMERIC)
LANGUAGE sql
AS $$
    SELECT id, name, salary
    FROM employees
    WHERE dept_id = did;
$$;
-- Использование: SELECT * FROM get_employees_by_dept(5);

`CREATE PROCEDURE`

-- PostgreSQL ≥11
CREATE PROCEDURE transfer_funds(from_acc INT, to_acc INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE id = to_acc;
    COMMIT;  -- разрешено только в PROCEDURE (не в FUNCTION!)
END;
$$;
CALL transfer_funds(1, 2, 100.00);

-- T-SQL
CREATE PROCEDURE TransferFunds
    @from_acc INT,
    @to_acc INT,
    @amount DECIMAL(18,2)
AS
BEGIN
    BEGIN TRANSACTION;
    UPDATE Accounts SET Balance -= @amount WHERE Id = @from_acc;
    UPDATE Accounts SET Balance += @amount WHERE Id = @to_acc;
    COMMIT;
END;
EXEC TransferFunds 1, 2, 100.00;

Параметры:

IN (по умолчанию) — входной
OUT — выходной (только в процедурах)
INOUT — вход/выход
VARIADIC (PostgreSQL) — для массивов: VARIADIC arr INT[]

13. Вспомогательные конструкции

`CASE`

-- Простой CASE (по значению)
CASE col
    WHEN 1 THEN 'один'
    WHEN 2 THEN 'два'
    ELSE 'много'
END

-- Поисковый CASE (по условию)
CASE
    WHEN salary < 30000 THEN 'низкий'
    WHEN salary BETWEEN 30000 AND 70000 THEN 'средний'
    ELSE 'высокий'
END

Функции обработки `NULL`

Функция	Описание	ANSI	Пример
`COALESCE(a, b, c)`	возвращает первый ненулевой аргумент	✓	`COALESCE(phone, email, 'нет контакта')`
`NULLIF(a, b)`	возвращает `NULL`, если `a = b`, иначе `a`	✓	`NULLIF(value, 0)` — избежать деления на 0
`ISNULL(a, b)`	T-SQL: аналог `COALESCE`, но только 2 аргумента	—	`ISNULL(name, '—')`
`NVL(a, b)`	Oracle: аналог `COALESCE` (2 аргумента)	—	`NVL(salary, 0)`

Функции сравнения

Функция	Описание
`GREATEST(a, b, c)`	максимум из аргументов (PostgreSQL, MySQL, Oracle)
`LEAST(a, b, c)`	минимум
`IIF(cond, true_val, false_val)`	T-SQL: инлайновый `CASE`

Приведение типов

CAST(expr AS type)
expr::type                      -- PostgreSQL, Redshift
TRY_CAST(expr AS type)          -- T-SQL, возвращает NULL при ошибке
CONVERT(type, expr [, style])   -- T-SQL (style для дат/строк)

Генерация данных

-- PostgreSQL: generate_series
SELECT generate_series(1, 10) AS n;
SELECT generate_series('2025-01-01'::DATE, '2025-12-31'::DATE, '1 day'::INTERVAL);

-- T-SQL: рекурсивный CTE или tally-таблица
WITH nums(n) AS (
    SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

14. Сравнение диалектов: ключевые отличия

Фича / СУБД	PostgreSQL (PL/pgSQL)	SQL Server (T-SQL)	Oracle (PL/SQL)	MySQL
Идентификаторы	`"quoted"`	`[quoted]` или `"quoted"`	`"quoted"`	`quoted`
Комментарии	`--`, `/* /`, `/*/`	`--`, `/* */`	`--`, `/* */`	`--` , `#`, `/* */`
Автоинкремент	`SERIAL`, `GENERATED ALWAYS AS IDENTITY`	`IDENTITY(1,1)`, `SEQUENCE`	`IDENTITY`, `SEQUENCE + TRIGGER`	`AUTO_INCREMENT`
Строка → число	`CAST('123' AS INT)`	`CAST('123' AS INT)`, `TRY_CAST`	`TO_NUMBER('123')`	`CAST('123' AS SIGNED)`
Дата/время	`'2025-11-18'::DATE`, `timestamptz`	`GETDATE()`, `SYSDATETIME()`, `DATETIME2`	`SYSDATE`, `TIMESTAMP WITH TIME ZONE`	`NOW()`, `CURDATE()`, `DATETIME(6)`
Оконные ф-ции	полная поддержка ANSI	полная (с 2012)	полная	с 8.0
Рекурсивные CTE	`WITH RECURSIVE`	`WITH`, без `RECURSIVE`	`WITH`, без `RECURSIVE`	с 8.0
Динамический SQL	`EXECUTE format('...', arg)`	`EXEC sp_executesql @sql, @params, @p1=...`	`EXECUTE IMMEDIATE`	`PREPARE`, `EXECUTE`, `EXECUTE IMMEDIATE`
Обработка исключений	`EXCEPTION WHEN ... THEN`	`BEGIN TRY ... END TRY BEGIN CATCH`	`EXCEPTION WHEN ... THEN`	`DECLARE EXIT HANDLER FOR SQLEXCEPTION`
Возврат из ф-ции	`RETURN expr;`	`RETURN expr;` (в функциях), `SELECT` (в inline TVF)	`RETURN expr;`	`RETURN expr;`
Табличная ф-ция	`RETURNS TABLE (...)`	`RETURNS @t TABLE (...)` (multi-statement), `RETURNS TABLE AS RETURN (...)` (inline)	`PIPELINED TABLE FUNCTION`	`RETURNS TABLE (...)` (с 8.0)
Проверка объекта	`IF NOT EXISTS (SELECT FROM pg_tables ...)`	`IF NOT EXISTS (SELECT * FROM sys.objects ...)`	`SELECT COUNT(*) FROM user_tables WHERE ...`	`CREATE TABLE IF NOT EXISTS`
LIMIT/OFFSET	`LIMIT n OFFSET m`	`OFFSET m ROWS FETCH NEXT n ROWS ONLY`	`OFFSET m ROWS FETCH NEXT n ROWS ONLY` (с 12c)	`LIMIT n OFFSET m`
JSON-операторы	`->`, `->>`, `@>`	`JSON_VALUE`, `JSON_QUERY`	`JSON_VALUE`, `JSON_TABLE`	`->`, `->>`, `JSON_EXTRACT`

🔹 Примечание по переносимости:

Избегайте диалект-специфичных конструкций (TOP, LIMIT, ::, [ ]) в библиотечном коде.

Используйте ANSI JOIN с ON, а не старый синтаксис WHERE t1.id = t2.id.

Для автоинкремента — GENERATED ALWAYS AS IDENTITY (ANSI SQL:2016).

Справочник SQL​

1. DDL — Data Definition Language​

CREATE​

Таблица​

Индекс​

Представление​

Схема​

Домен (PostgreSQL, Firebird)​

Последовательность (serial, identity support)​

Тип данных (PostgreSQL, Oracle)​

Триггер (общая форма)​

ALTER​

Таблица​

Индекс / представление / последовательность​

DROP​

TRUNCATE​

RENAME (не ANSI, но широко используется)​

2. DML — Data Manipulation Language​

INSERT​

UPDATE​

DELETE​

MERGE (ANSI — upsert)​

3. DCL — Data Control Language​

GRANT​

REVOKE​

CREATE ROLE / DROP ROLE (не ANSI, но стандарт де-факто)​

4. TCL — Transaction Control Language​

BEGIN / START TRANSACTION​

COMMIT​

ROLLBACK​

SAVEPOINT​

Уровни изоляции (стандартные и поведение)​

5. DQL — Data Query Language​

Обобщённый синтаксис (логический порядок обработки):​

FROM и JOIN​

Типы JOIN (ANSI):​

Расширения:​

WHERE​

GROUP BY​

Агрегатные функции (стандартные):​

GROUPING SETS, ROLLUP, CUBE (ANSI, PostgreSQL, SQL Server, Oracle)​

GROUPING() — идентификатор уровня агрегации​

HAVING​

WINDOW и оконные функции​

Объявление окна:​

Стандартные оконные функции:​

Спецификация окна:​

SELECT расширения​

DISTINCT ON (PostgreSQL)​

TOP (T-SQL), LIMIT (ANSI/PostgreSQL/MySQL), FETCH (ANSI)​

FOR UPDATE, FOR SHARE​

6. Системные / служебные команды (не ANSI, но критичны)​

Получение метаданных​

Управление сессиями / настройками​

Условное выполнение (в скриптах)​

7. CTE — Common Table Expressions​

Базовый синтаксис​

Особенности:​

Рекурсивные CTE (WITH RECURSIVE)​

8. Подзапросы​

Типы:​

Оптимизация:​

9. Множественные операции​

Синтаксис:​

Важно:​

10. Работа с JSON​

Стандарт SQL:2016 (JSON_VALUE, JSON_QUERY, JSON_TABLE)​

PostgreSQL (операторы):​

T-SQL:​

MySQL:​

11. Полнотекстовый поиск​

PostgreSQL (tsvector, tsquery)​

MySQL (FULLTEXT)​

SQL Server (CONTAINS, FREETEXT)​

12. Хранимые модули​

CREATE FUNCTION​

CREATE PROCEDURE​

Параметры:​

13. Вспомогательные конструкции​

CASE​

Справочник SQL

1. DDL — Data Definition Language

`CREATE`

Таблица

Индекс

Представление

Схема

Домен (PostgreSQL, Firebird)

Последовательность (serial, identity support)

Тип данных (PostgreSQL, Oracle)

Триггер (общая форма)

`ALTER`

Таблица

Индекс / представление / последовательность

`DROP`

`TRUNCATE`

`RENAME` (не ANSI, но широко используется)

2. DML — Data Manipulation Language

`INSERT`

`UPDATE`

`DELETE`

`MERGE` (ANSI — upsert)

3. DCL — Data Control Language

`GRANT`

`REVOKE`

`CREATE ROLE` / `DROP ROLE` (не ANSI, но стандарт де-факто)

4. TCL — Transaction Control Language

`BEGIN` / `START TRANSACTION`

`COMMIT`

`ROLLBACK`

`SAVEPOINT`

Уровни изоляции (стандартные и поведение)

5. DQL — Data Query Language

Обобщённый синтаксис (логический порядок обработки):

`FROM` и `JOIN`

Типы JOIN (ANSI):

Расширения:

`WHERE`

`GROUP BY`

Агрегатные функции (стандартные):

`GROUPING SETS`, `ROLLUP`, `CUBE` (ANSI, PostgreSQL, SQL Server, Oracle)

`GROUPING()` — идентификатор уровня агрегации

`HAVING`

`WINDOW` и оконные функции

Объявление окна:

Стандартные оконные функции:

Спецификация окна:

`SELECT` расширения

`DISTINCT ON` (PostgreSQL)

`TOP` (T-SQL), `LIMIT` (ANSI/PostgreSQL/MySQL), `FETCH` (ANSI)

`FOR UPDATE`, `FOR SHARE`

6. Системные / служебные команды (не ANSI, но критичны)

Получение метаданных

Управление сессиями / настройками

Условное выполнение (в скриптах)

7. CTE — Common Table Expressions

Базовый синтаксис

Особенности:

Рекурсивные CTE (`WITH RECURSIVE`)

8. Подзапросы

Типы:

Оптимизация:

9. Множественные операции

Синтаксис:

Важно:

10. Работа с JSON

Стандарт SQL:2016 (`JSON_VALUE`, `JSON_QUERY`, `JSON_TABLE`)

PostgreSQL (операторы):

T-SQL:

MySQL:

11. Полнотекстовый поиск

PostgreSQL (`tsvector`, `tsquery`)

MySQL (`FULLTEXT`)

SQL Server (`CONTAINS`, `FREETEXT`)

12. Хранимые модули

`CREATE FUNCTION`

`CREATE PROCEDURE`

Параметры:

13. Вспомогательные конструкции

`CASE`