📊 SQL для бэкендера
На собеседовании бэкендеру почти всегда дают SQL-задачу: две-три таблицы и просьба написать запрос прямо в редакторе. Проверяют не память на синтаксис, а понимание, в каком порядке база выполняет запрос и что происходит с NULL. На работе это те же вопросы: почему отчёт потерял половину пользователей и откуда в выборке дубли. Все примеры ниже запущены на PostgreSQL 16, в других базах синтаксис почти тот же
🧠 Как это устроено
Порядок выполнения
Запрос пишется сверху вниз, а выполняется в другом порядке. Это логический порядок: планировщик может переставлять операции ради скорости, но результат обязан совпасть с таким выполнением
WITH— вычисляются CTEFROMиJOIN— собирается набор строкWHERE— отбрасываются строкиGROUP BY— строки сворачиваются в группы, считаются агрегатыHAVING— отбрасываются группы- оконные функции
SELECT— вычисляются выходные столбцы и псевдонимыDISTINCT, затемUNIONи соседиORDER BYLIMITиOFFSET
Из этого списка выводятся ответы на несколько частых вопросов. Псевдоним из SELECT нельзя использовать в WHERE: на шаге 3 его ещё нет, и Postgres ответит column "dbl" does not exist. В ORDER BY можно, он идёт после. Postgres разрешает псевдоним и в GROUP BY, но это его расширение, а в HAVING выражение придётся повторить. Оконную функцию нельзя фильтровать в WHERE того же запроса по той же причине, нужен подзапрос или CTE
Виды JOIN
Все примеры дальше идут на двух таблицах. В users четыре человека: у Глеба нет заказов, у Веры не указан город. В orders семь заказов, у одного нет user_id, у Бориса один заказ отменён
| JOIN | Что вернёт | Строк в примере |
|---|---|---|
INNER JOIN | только пары, где условие совпало | 6 |
LEFT JOIN | все строки слева, справа NULL, если пары нет | 7 |
RIGHT JOIN | зеркально LEFT, на практике его переписывают в LEFT | 7 |
FULL JOIN | все строки обеих таблиц | 8 |
CROSS JOIN | каждая строка с каждой, 4 × 7 = 28 | 28 |
JOIN умножает строки. Если у пользователя три заказа, он появится в результате трижды, и sum по колонке из users вырастет втрое. Так в отчётах появляются завышенные цифры
GROUP BY и HAVING
После GROUP BY в SELECT можно писать только столбцы группировки и агрегаты. Остальное Postgres не пропустит: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function. Исключение одно: если сгруппировали по первичному ключу, остальные столбцы этой таблицы можно выводить, база знает, что они однозначны
WHERE фильтрует строки до группировки, HAVING — группы после. Условие на столбец группировки в HAVING формально работает, но база сначала сгруппирует строки, которые потом выкинет
Подзапросы и CTE
CTE (WITH name AS (...)) — именованный подзапрос, который читается сверху вниз. Раньше его называли «барьером оптимизации»: до PostgreSQL 12 CTE всегда вычислялся отдельно, и фильтр из внешнего запроса внутрь не проникал. С 12-й версии нерекурсивный CTE без побочных эффектов, использованный один раз, встраивается во внешний запрос, и план совпадает с обычным подзапросом. Вернуть старое поведение можно словом MATERIALIZED
explain (costs off)
with p as (select * from orders where status = 'paid')
select * from p where user_id = 1;
-- Seq Scan on orders
-- Filter: ((status = 'paid'::text) AND (user_id = 1))С as materialized в плане появится CTE Scan on p: сначала все оплаченные заказы, потом фильтр по пользователю. На маленькой таблице разницы нет, на большой это потерянный индекс
WITH RECURSIVE обходит деревья: оргструктуру, категории, комментарии с ответами
with recursive sub as (
select id, name, 1 as depth from employees where id = 2
union all
select e.id, e.name, s.depth + 1
from employees e join sub s on e.manager_id = s.id
)
select name, depth from sub;Первая часть — стартовая строка, вторая присоединяет подчинённых к уже найденным, пока новые строки появляются
Оконные функции
Агрегат сворачивает строки, оконная функция считает по группе, но оставляет каждую строку. partition by делит строки на окна, order by внутри задаёт порядок
select user_id, id, amount,
row_number() over w as rn,
rank() over w as rnk,
dense_rank() over w as drnk
from orders
where user_id is not null
window w as (partition by user_id order by amount desc);| user_id | amount | rn | rnk | drnk |
|---|---|---|---|---|
| 1 | 1500 | 1 | 1 | 1 |
| 1 | 1500 | 2 | 1 | 1 |
| 1 | 500 | 3 | 3 | 2 |
row_number нумерует подряд и при равных значениях порядок между ними не определён. rank даёт равным одно место и пропускает следующее, dense_rank не пропускает. Ещё пригодятся sum() over (order by ...) для накопительного итога и lag() или lead(), чтобы взять значение из предыдущей или следующей строки
🛠️ На практике
Задачи ниже повторяются на собеседованиях чаще остальных
Пользователи без заказов. Через NOT EXISTS или LEFT JOIN ... WHERE o.id IS NULL. Оба запроса вернули Глеба
select name from users u
where not exists (select 1 from orders o where o.user_id = u.id);Топ-N в каждой группе. Два самых крупных заказа каждого пользователя: пронумеровать в окне, отфильтровать снаружи. Второй столбец в order by окна делает результат стабильным при равных суммах
with ranked as (
select o.*, row_number() over (partition by user_id order by amount desc, id) as rn
from orders o
)
select user_id, id, amount from ranked where rn <= 2;Для одной строки на группу в Postgres есть короче: select distinct on (user_id) ... order by user_id, created_at desc вернёт последний заказ каждого пользователя
Вторая по величине сумма. select max(amount) from orders where amount < (select max(amount) from orders) или select distinct amount ... order by amount desc offset 1 limit 1. Без distinct второй вариант вернёт ту же максимальную сумму, если она встречается дважды
Дубли. group by user_id, amount having count(*) > 1 находит повторы. В примере их два: у Ани два заказа по 1500 и у Бориса два по 700
Несколько счётчиков за один проход. count(*) filter (where status = 'paid') и count(*) filter (where status = 'cancelled') в одном select вместо двух запросов или sum(case when ...)
SQL · Накопительный итог и разница с прошлым заказом
select created_at, amount,
sum(amount) over (order by created_at, id) as running,
amount - lag(amount) over (order by created_at, id) as diff
from orders
where status = 'paid';
-- created_at | amount | running | diff
-- 2026-10-01 | 500 | 500 |
-- 2026-10-03 | 1500 | 2000 | 1000
-- 2026-10-05 | 700 | 2700 | -800⚠️ Где ошибаются
Фильтр по правой таблице в WHERE после LEFT JOIN. Хотели посчитать оплаченные заказы у всех пользователей и написали left join orders o on o.user_id = u.id where o.status = 'paid'. У Глеба заказов нет, o.status равен NULL, условие не выполнилось, и Глеб пропал: LEFT JOIN молча стал INNER. Условие на правую таблицу ставят в ON: on o.user_id = u.id and o.status = 'paid', тогда Глеб остаётся с нулём
*`count() вместо count(o.id) после LEFT JOIN.** count(*) считает строки, и у Глеба будет 1, хотя заказов ноль. count(o.id) пропускает NULL` и даёт честный 0
NOT IN с NULL в подзапросе. where id not in (select user_id from orders) вернул пустой результат, хотя у Глеба заказов нет. В orders есть строка с user_id = NULL, а 4 <> NULL даёт не true, а NULL, и всё условие тоже NULL. Помогает NOT EXISTS или where user_id is not null в подзапросе
Сравнение с NULL через = и <>. where city <> 'Москва' вернул только Бориса: у Веры город NULL, и сравнение не дало true. Если NULL надо считать обычным значением, пиши city is distinct from 'Москва', тогда вернётся и Вера. where city = null не найдёт ничего никогда, нужно is null
Средние и NULL. avg(x) по значениям 10, NULL, 20 даёт 15: агрегаты пропускают NULL. Если отсутствие значения должно считаться нулём, это решение бизнеса, и его пишут явно: avg(coalesce(x, 0)) даст 10
🎯 Вопросы с собеседований
Чем WHERE отличается от HAVING?
WHERE отбрасывает строки до группировки и не видит агрегатов. HAVING отбрасывает группы после GROUP BY, в нём можно писать sum(amount) > 1000. Условие на столбец группировки, вроде user_id = 1, лучше ставить в WHERE: база не будет группировать лишние строки
Чем INNER JOIN отличается от LEFT JOIN?
INNER JOIN оставляет только пары, где условие совпало. LEFT JOIN оставляет все строки левой таблицы, а у кого пары нет, в столбцах правой будет NULL. Отсюда типичная ошибка: условие на правую таблицу в WHERE выкидывает эти строки, и LEFT становится INNER
Почему псевдоним из SELECT нельзя использовать в WHERE?
WHERE выполняется раньше SELECT, и в этот момент псевдонима ещё нет. Порядок такой: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Поэтому в ORDER BY псевдоним работает, а в WHERE выражение нужно повторить или вынести в подзапрос
Чем отличаются row_number, rank и dense_rank?
Все три нумеруют строки внутри окна. row_number даёт уникальные номера подряд даже при равных значениях. rank даёт равным одно место и пропускает следующие: 1, 1, 3. dense_rank не пропускает: 1, 1, 2. Для «топ-3 в каждой группе» берут row_number, если нужно ровно три строки, и dense_rank, если нужны три уровня значений
Что вернёт NOT IN, если в подзапросе есть NULL?
Ни одной строки. x NOT IN (1, NULL) раскрывается в x <> 1 AND x <> NULL, а сравнение с NULL даёт NULL, поэтому всё условие не бывает true. Безопасный вариант — NOT EXISTS, он работает построчно и на NULL не ломается
Чем CTE отличается от подзапроса и замедляет ли он запрос?
По результату ничем, CTE читается проще и может быть рекурсивным. До PostgreSQL 12 CTE всегда вычислялся отдельно и мешал оптимизатору. С 12-й версии CTE, на который ссылаются один раз, встраивается во внешний запрос, и план тот же. Если CTE использован дважды, он по умолчанию материализуется, это можно поменять через NOT MATERIALIZED
Как найти сотрудников, которые получают больше своего руководителя?
Соединить таблицу саму с собой: from employees e join employees m on m.id = e.manager_id where e.salary > m.salary. Здесь e сотрудник, а m его руководитель. Сотрудники без руководителя выпадут из INNER JOIN, и это правильно, сравнивать их не с кем
Чем UNION отличается от UNION ALL?
UNION убирает дубли, для этого базе нужно отсортировать или захешировать весь результат. UNION ALL просто склеивает наборы и работает быстрее. Если дублей быть не может или они нужны, бери UNION ALL
📚 Что почитать
- SELECT в документации PostgreSQL — порядок выполнения и правила для
GROUP BYи псевдонимов из первых рук - Оконные функции — короткий учебник с примерами
partition byи фильтрацией через подзапрос - WITH-запросы — рекурсивные CTE и когда CTE встраивается или материализуется
- Операторы сравнения — как ведёт себя
NULLи зачемIS DISTINCT FROM