SQL: операторы, NULL и фильтрация

Группы команд, логический порядок SELECT, трёхзначная логика NULL, разница WHERE/HAVING и оконные функции — с ловушками, на которых валятся на собеседовании.

Что делает SQL и группы команд

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

Команды делят на четыре группы:

DDL (Data Definition Language) — определяет схему: CREATE, ALTER, DROP.
DML (Data Manipulation Language) — работает с данными: SELECT, INSERT, UPDATE, DELETE.
TCL (Transaction Control Language) — управляет транзакциями: COMMIT, ROLLBACK, SAVEPOINT.
DCL (Data Control Language) — управляет правами: GRANT, REVOKE.

Важно не просто перечислить, а понимать разницу рисков: DML меняет строки и обычно откатывается, а DDL меняет структуру и в MySQL и Oracle фиксирует транзакцию неявно — откатить ALTER уже не выйдет. В PostgreSQL DDL транзакционный: миграцию можно выполнить целиком в одной транзакции и откатить при ошибке.

Логический порядок выполнения SELECT

Текст пишут как SELECT … FROM … WHERE …, но логически шаги идут иначе: FROM и JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

Из этого следует практичное правило: на этапе WHERE ещё нет ни групп, ни алиасов из SELECT — поэтому фильтровать по агрегату в WHERE нельзя, а ссылаться в WHERE на алиас столбца из SELECT обычно тоже нельзя (он вычисляется позже).

ORDER BY, наоборот, выполняется после SELECT, поэтому в нём алиасы уже доступны.

NULL и трёхзначная логика

NULL — это «значение неизвестно/отсутствует», а не пустая строка и не ноль. Любое сравнение с NULL даёт не TRUE и не FALSE, а UNKNOWN, поэтому строка не проходит фильтр.

Из-за этого = NULL не работает — нужны IS NULL / IS NOT NULL. Особенно коварен NOT IN с подзапросом, где встречается NULL: всё выражение становится UNKNOWN и результат оказывается пустым.

Агрегаты игнорируют NULL: count(column) считает только не-NULL значения, тогда как count(*) считает строки. Это частый источник расхождений в отчётах.

-- неверно: вернёт пусто
select * from users where deleted_at = null;
-- верно:
select * from users where deleted_at is null;

-- count(*) считает строки, count(email) — не-NULL email
select count(*), count(email) from users;

WHERE против HAVING

WHERE фильтрует строки до группировки — поэтому его стоит использовать всегда, когда можно: чем раньше отсекли данные, тем меньше работы дальше и тем вероятнее задействуется индекс.

HAVING фильтрует уже готовые группы и нужен для условий по агрегатам (count, sum, avg). Писать обычное условие по строке в HAVING — ошибка: оно отработает позже и без помощи индекса.

select user_id, count(*) as orders
from orders
where status = 'PAID'   -- сначала отсекли строки
group by user_id
having count(*) > 10;     -- затем отсекли группы

Подзапросы и CTE

Подзапрос — это запрос внутри запроса. Скалярный подзапрос возвращает одно значение и подставляется как выражение; подзапрос в IN/EXISTS используется для фильтрации. EXISTS обычно эффективнее IN, когда важен лишь факт наличия строки (и он безопасен относительно NULL, в отличие от NOT IN).

Коррелированный подзапрос ссылается на строку внешнего запроса и потому логически выполняется для каждой такой строки — это удобно читать, но легко получить медленный план; часто его переписывают в JOIN.

CTE (WITH ... AS) — это именованный подзапрос, вынесенный вперёд: он не быстрее сам по себе, но делает сложный запрос читаемым и позволяет переиспользовать промежуточный результат и писать рекурсию (WITH RECURSIVE для деревьев/иерархий).

-- EXISTS: есть ли у пользователя хоть один оплаченный заказ
select u.*
from users u
where exists (
  select 1 from orders o
  where o.user_id = u.id and o.status = 'PAID'
);

-- CTE для читаемости
with paid as (
  select user_id, count(*) cnt from orders
  where status = 'PAID' group by user_id
)
select u.email, paid.cnt
from users u join paid on paid.user_id = u.id;

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

Агрегат с GROUP BY схлопывает группу строк в одну. Оконная функция считает то же самое, но СТРОКИ СОХРАНЯЕТ: рядом с каждой строкой появляется значение, посчитанное по её «окну» — наборе соседних строк, заданном через OVER (PARTITION BY ... ORDER BY ...).

PARTITION BY делит строки на независимые окна (как GROUP BY, но без схлопывания), а ORDER BY внутри OVER задаёт порядок внутри окна — от него зависят ранжирование и накопительные итоги (running total).

Три ранжирующие функции путают чаще всего. ROW_NUMBER() нумерует сплошь (1, 2, 3, 4). RANK() при равных значениях даёт одинаковый ранг и оставляет дырку (1, 2, 2, 4). DENSE_RANK() — одинаковый ранг без дырки (1, 2, 2, 3). Отсюда классический ответ про «вторую по величине зарплату»: DENSE_RANK() = 2 корректно учитывает дубликаты, а LIMIT 1 OFFSET 1 без DISTINCT при двух одинаковых максимумах вернёт ту же зарплату.

Важное следствие порядка выполнения: окна считаются после WHERE, GROUP BY и HAVING, поэтому фильтровать по результату оконной функции прямо в WHERE нельзя — запрос заворачивают в подзапрос или CTE и фильтруют снаружи.

-- вторая по величине зарплата с учётом дубликатов
with ranked as (
  select salary, dense_rank() over (order by salary desc) as rnk
  from employee
)
select distinct salary from ranked where rnk = 2;

-- топ-3 заказа каждого пользователя, строки не схлопываются
select user_id, id, total
from (
  select user_id, id, total,
         row_number() over (partition by user_id order by total desc) as rn
  from orders
) t
where rn <= 3;

Частые ошибки

Типичные ловушки: сравнивать NULL через =, путать count(*) и count(column), фильтровать агрегат в WHERE, ожидать, что NOT IN корректно отработает при NULL в наборе.

Ещё две, на которых спотыкаются регулярно: ссылаться в WHERE на алиас из SELECT (его там ещё нет) и фильтровать по оконной функции без обёртки в подзапрос.

Как отвечать на собеседовании

Начните с логического порядка SELECT (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) и привяжите к нему всё остальное — это каркас, из которого сами собой выводятся ответы: почему WHERE не видит агрегатов и алиасов, почему ORDER BY видит, почему по окну нельзя фильтровать в WHERE.

Про NULL скажите главное: это не значение, а неизвестность, сравнение даёт UNKNOWN, отсюда IS NULL вместо = NULL и ловушка NOT IN с NULL в наборе. Разведите count(*) и count(column).

Разницу WHERE/HAVING объясняйте не формально, а через стоимость: WHERE отсекает строки до группировки и может использовать индекс, HAVING фильтрует уже готовые группы. Завершите оконными функциями: назовите ROW_NUMBER/RANK/DENSE_RANK и покажите на «второй зарплате», что понимаете поведение при дубликатах.