JOIN и операции множеств: вопросы для собеседования (часть 6)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 26–30 из 40
26Нужно для каждого пользователя достать 3 последних заказа. Запрос: `SELECT u.name, o.* FROM users u, LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) o`. Что делает `LATERAL`?
AОбычный подзапрос в `FROM` уже умеет ссылаться на столбцы внешнего `FROM`, поэтому `LATERAL` здесь не требуется
BКлючевое слово `LATERAL` применяется к `JOIN ON` и добавляет дополнительные условия соединения для строк правой таблицы
CПодзапрос с `LATERAL` может ссылаться на столбцы таблиц, указанных левее в том же `FROM` — здесь `u.id` из `users`
DКлючевое слово `LATERAL` автоматически превращает `LEFT JOIN` во внутреннее соединение, исключая строки без совпадений
Ответ: `LATERAL` позволяет подзапросу в `FROM` ссылаться на столбцы из таблиц, перечисленных левее.
Без `LATERAL` подзапрос в `FROM` изолирован и не может ссылаться на другие таблицы того же `FROM`. `LATERAL` снимает это ограничение: подзапрос выполняется для каждой строки левой таблицы, имея доступ к её столбцам. Это аналог коррелированного подзапроса, но в позиции `FROM`, что позволяет возвращать несколько строк и столбцов. Паттерн «top-N per group» через `LATERAL` часто эффективнее оконных функций.
Подробный разбор → 27Два запроса ищут пользователей без заказов: `LEFT JOIN orders ON ... WHERE orders.id IS NULL` и `WHERE NOT EXISTS (SELECT 1 FROM orders WHERE ...)`. Что верно о производительности в PostgreSQL?
AОптимизатор `PostgreSQL` обычно сводит оба варианта к плану `Anti Join`, разница в скорости минимальна
BВариант с `LEFT JOIN ... IS NULL` быстрее: `NOT EXISTS` запускает подзапрос для каждой строки
CВариант с `NOT EXISTS` быстрее: он прерывает поиск в подзапросе на первом совпадении
DОба варианта выполняются последовательно: сначала полный `JOIN`, потом фильтр по `IS NULL`
Ответ: Оптимизатор PostgreSQL обычно распознаёт оба паттерна как анти-соединение и строит одинаковый план — разница в скорости минимальна.
Современные оптимизаторы (PostgreSQL, SQL Server, Oracle) умеют преобразовывать `LEFT JOIN ... IS NULL`, `NOT EXISTS` и даже `NOT IN` (без `NULL`) в один оператор `Anti Join`. В выводе `EXPLAIN` это видно как `Hash Anti Join` или `Merge Anti Join`. На практике для PostgreSQL разница в скорости между первыми двумя подходами пренебрежимо мала. `NOT IN` может проиграть из-за обработки `NULL`. Рекомендация: выбирать наиболее читаемый вариант.
Подробный разбор → 28Есть две таблицы с дневными метриками: `daily_orders(day, orders)` и `daily_spend(day, spend)`. В одни дни есть траты без заказов, в другие — заказы без трат. Нужно получить отчёт по всем дням с подстановкой 0 там, где метрика отсутствует. Какой подход верный?
A`INNER JOIN` по дате с подстановкой нулей в недостающие метрики средствами самого соединения
B`LEFT JOIN` от daily_orders к daily_spend для покрытия дней и обеих метрик в финальном отчёте
C`FULL JOIN` по дате со сбором ключа через COALESCE и заменой пропусков на ноль в метриках
D`UNION ALL` по дате с автоматической раскладкой метрик по соответствующим колонкам отчёта
Ответ: `FULL JOIN` сохраняет дни с обеих сторон, а `COALESCE` помогает собрать ключ и заменить `NULL` на 0.
Для полного покрытия дней нужен `FULL JOIN`, иначе часть дат потеряется (`INNER JOIN` оставит только пересечение, `LEFT JOIN` — только даты из левой таблицы). После `FULL JOIN` ключ `day` может быть `NULL` с одной стороны, поэтому удобно взять `COALESCE(o.day, s.day)`. Для метрик используйте `COALESCE(o.orders, 0)` и `COALESCE(s.spend, 0)`, чтобы в отчёте не было `NULL` там, где данных нет.
Подробный разбор → 29Запрос: `SELECT u.name, last_order.* FROM users u, (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1) last_order`. Заметьте: `LATERAL` не указан. Что произойдёт?
AЗапрос выполнится: подзапрос в `FROM` видит таблицы внешнего блока без ключевого слова `LATERAL`
BОшибка выполнения: без `LATERAL` подзапрос в `FROM` не может ссылаться на колонку `u.id` из соседней таблицы
CЗапрос выполнится, но `WHERE user_id = u.id` будет проигнорирован, и подзапрос вернёт один заказ для всех
DЗапрос выполнится с декартовым произведением: по `LIMIT 1` строке на каждого `u` через перебор сочетаний
Ответ: Без ключевого слова `LATERAL` подзапрос в `FROM` не видит другие таблицы того же `FROM`, поэтому ссылка `u.id` приведёт к ошибке.
В стандартном SQL подзапрос в `FROM` (derived table) изолирован от внешнего контекста: ссылка `u.id` внутри него вызывает ошибку вроде `invalid reference to FROM-clause entry for table u`. Чтобы разрешить корреляцию, нужно явно написать `FROM users u, LATERAL (SELECT ... WHERE user_id = u.id ...) last_order`. Только `LATERAL` декларирует, что подзапрос вычисляется для каждой строки внешней таблицы. Идея «подзапрос проигнорирует условие» или «вернёт декартово произведение» неверна — парсер просто откажет в исполнении.
Подробный разбор → 30Есть справочник `products(product_id)` и лог продаж `sales(product_id, order_id)`. Нужно найти (1) товары из справочника без продаж и (2) продажи по товарам, которых нет в справочнике. Какой подход подходит лучше всего?
AСделать `INNER JOIN` `products` и `sales` и отфильтровать `WHERE sales.product_id IS NULL`
BСделать `LEFT JOIN` `products` на `sales` и взять строки `WHERE sales.product_id IS NULL`
CСделать `FULL JOIN` и взять строки, где `products.product_id IS NULL` или `sales.product_id IS NULL`
DСделать `UNION` двух таблиц по `product_id` без соединения и взять различающиеся строки
Ответ: `FULL JOIN` полезен для сверки двух наборов, когда нужно увидеть несовпадения с обеих сторон.
`LEFT JOIN` найдёт только товары без продаж (проблема со стороны `products`). Но продажи с неизвестными `product_id` вы так не увидите. Для одновременной проверки двух направлений используют `FULL JOIN` и фильтруют строки, где одна из сторон не сматчилась: `products.product_id IS NULL OR sales.product_id IS NULL`.
Подробный разбор →