SQL для продвинутых: оконные функции, CTE, JSON и сравнение диалектов PostgreSQL, MySQL, SQLite, Oracle, H2

SQL кажется стандартным — пока не переключаешься между базами данных. Один и тот же запрос на пяти разных СУБД может выглядеть совершенно по-разному. В этой шпаргалке — сравнение ключевых возможностей SQL в PostgreSQL, MySQL, SQLite, Oracle и H2 с акцентом на продвинутые функции: CTE, оконные функции, JSON, полнотекстовый поиск и upsert.

1. CTE — Common Table Expressions

CTE позволяет разбить сложный запрос на именованные подзапросы. Удобно для читаемости и переиспользования.

Простой CTE (все СУБД — одинаково)

WITH recent_orders AS (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    WHERE order_date > '2026-01-01'
    GROUP BY customer_id
)
SELECT c.name, ro.total
FROM customers c
JOIN recent_orders ro ON c.id = ro.customer_id;

Рекурсивный CTE

Oracle — уникален: ключевое слово RECURSIVE не используется. CTE считается рекурсивным по факту ссылки на себя. Также Oracle поддерживает SEARCH DEPTH FIRST BY / SEARCH BREADTH FIRST BY и CYCLE — для контроля обхода иерархий:

-- Oracle: обход дерева сотрудников с защитой от циклов
WITH emp_tree (id, name, manager_id, lvl) AS (
    SELECT id, name, manager_id, 1 FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, et.lvl + 1
    FROM employees e
    JOIN emp_tree et ON e.manager_id = et.id
)
SEARCH DEPTH FIRST BY id SET order_col
CYCLE id SET is_cycle TO 'Y' DEFAULT 'N'
SELECT * FROM emp_tree;

2. Оконные функции

Оконные функции выполняют вычисления по «окну» строк без сворачивания результата в одну строку (в отличие от GROUP BY).

Базовый синтаксис (все СУБД)

SELECT
    department,
    employee,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    SUM(salary) OVER (PARTITION BY department) AS dept_total,
    salary * 100.0 / SUM(salary) OVER (PARTITION BY department) AS pct_of_dept
FROM employees;

Сравнение возможностей

Примеры специфичных фич

H2 — IGNORE NULLS в LAG/LEAD:

-- H2: пропустить NULL при поиске предыдущего значения
LAG(salary, 1, 0) IGNORE NULLS OVER (PARTITION BY dept ORDER BY date)

Oracle/MySQL — QUALIFY (фильтрация результата оконной функции без подзапроса):

-- Выбрать топ-3 сотрудника в каждом отделе без вложенного SELECT
SELECT dept, name, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
QUALIFY rnk <= 3;

PostgreSQL — FILTER в OVER:

-- Сумма только «активных» заказов в окне
SUM(amount) FILTER (WHERE status = 'active') OVER (PARTITION BY customer_id)

3. Строковые функции — главные различия

Опасные отличия

LENGTH() в MySQL считает байты! Для UTF-8 строки «Привет» вернёт 12 (6 символов × 2 байта в UTF-8). Используйте CHAR_LENGTH().

|| в MySQL — логическое ИЛИ, а не конкатенация! Включить режим SET sql_mode='PIPES_AS_CONCAT' или всегда использовать CONCAT().

Oracle CONCAT() принимает ровно 2 аргумента — для трёх и более: CONCAT(CONCAT(a, b), c).

4. Дата и время — зоопарк функций

Форматирование — полный хаос. Каждая СУБД использует свой синтаксис спецификаторов формата:

• PostgreSQL/Oracle: 'YYYY-MM-DD'
• MySQL: '%Y-%m-%d'
• SQLite: '%Y-%m-%d'
• H2: 'yyyy-MM-dd' (Java-стиль!)

5. Типы данных и автоинкремент

Особенности

SQLite: INTEGER PRIMARY KEY автоматически становится автоинкрементом — отдельного типа SERIAL нет.

Oracle: используйте VARCHAR2 (не VARCHAR). Для Boolean в SQL до версии 23c — только NUMBER(1) с CHECK.

PostgreSQL JSONB: бинарный JSON с поддержкой индексов GIN — уникальная фича, которой нет ни в одной другой СУБД.

6. JSON — работа с документами

Доступ к полям

Создание и модификация

-- Создать JSON-объект
-- PostgreSQL
SELECT JSONB_BUILD_OBJECT('name', name, 'age', age) FROM users;
-- MySQL
SELECT JSON_OBJECT('name', name, 'age', age) FROM users;
-- Oracle
SELECT JSON_OBJECT('name' VALUE name, 'age' VALUE age) FROM users;

-- Обновить поле в JSON
-- PostgreSQL
UPDATE t SET data = JSONB_SET(data, '{address,city}', '"Moscow"');
-- MySQL
UPDATE t SET data = JSON_SET(data, '$.address.city', 'Moscow');
-- Oracle
UPDATE t SET data = JSON_TRANSFORM(data, SET '$.address.city' = 'Moscow');

Oracle — точечная нотация: SELECT t.data.name.first FROM users t — без кавычек, без функций. Уникальная фича.

7. UPSERT — вставить или обновить

Примеры

PostgreSQL / SQLite:

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

-- Ничего не делать
INSERT INTO users VALUES (1, 'Alice', 'a@ex.com')
ON CONFLICT (id) DO NOTHING;

MySQL:

INSERT INTO users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

Oracle:

MERGE INTO users t
USING (SELECT 1 AS id, 'Alice' AS name, 'a@ex.com' AS email FROM dual) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.name = s.name, t.email = s.email
WHEN NOT MATCHED THEN INSERT (id, name, email) VALUES (s.id, s.name, s.email);

H2 (простой синтаксис):

MERGE INTO users (id, name, email) KEY (id) VALUES (1, 'Alice', 'alice@example.com');

8. Пагинация — LIMIT, OFFSET, FETCH

MySQL LIMIT m, n — смещение первым аргументом!

-- Строки 20-29 (пропустить 20, взять 10)
-- PostgreSQL / SQLite:
SELECT * FROM t LIMIT 10 OFFSET 20;
-- MySQL:
SELECT * FROM t LIMIT 20, 10;  -- смещение первым!

Oracle ROWNUM — фильтруется до сортировки. Для правильной пагинации нужен подзапрос:

-- Правильная пагинация в Oracle
SELECT * FROM (
    SELECT t.*, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM t
) WHERE rn BETWEEN 21 AND 30;

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

Примеры

PostgreSQL:

-- Создать индекс
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('russian', body));

-- Поиск с ранжированием и подсветкой
SELECT ts_rank(to_tsvector('russian', body), query) AS rank,
       ts_headline('russian', body, query) AS snippet
FROM articles,
     to_tsquery('russian', 'управление & проектами') AS query
WHERE to_tsvector('russian', body) @@ query
ORDER BY rank DESC;

SQLite FTS5:

-- Создать виртуальную таблицу
CREATE VIRTUAL TABLE articles_fts USING fts5(title, body);

-- Поиск
SELECT *, highlight(articles_fts, 1, '<b>', '</b>') AS title_hl
FROM articles_fts
WHERE articles_fts MATCH 'управление проектами'
ORDER BY rank;

10. Регулярные выражения

-- PostgreSQL: извлечь email из текста
SELECT (REGEXP_MATCH(text, '[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}'))[1]
FROM messages;

-- Oracle: подсчитать количество цифр в строке
SELECT REGEXP_COUNT(phone, '\d') FROM contacts;

Сводка: главные неожиданности

1. LENGTH() в MySQL = байты, не символы. CHAR_LENGTH() — для символов
2. || в MySQL = логическое ИЛИ, не конкатенация. Используйте CONCAT()
3. Oracle: RECURSIVE не пишется — CTE рекурсивен по факту ссылки на себя
4. SQLite: INTEGER PRIMARY KEY = автоинкремент — отдельного типа нет
5. Oracle: BOOLEAN нет в SQL до версии 23c
6. PostgreSQL JSONB — единственный бинарный JSON с индексами и операторами @>, ?
7. Oracle: точечная нотация для JSON — col.field без кавычек
8. MySQL LIMIT m, n — смещение первым аргументом
9. Oracle ROWNUM фильтруется до сортировки — нужен ROW_NUMBER() в подзапросе
10. Форматирование дат — у каждой СУБД свой диалект

*Связанные статьи: SQL для начинающих: шпаргалка по базовым запросам, 50+ инструментов разработчика 2025, Шпаргалка по Git: основные команды, Шпаргалка Regex: регулярные выражения