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`
DКлючевое слово `LATERAL` преобразует `LEFT JOIN` во внутреннее соединение для подзапроса
Ответ: `LATERAL` позволяет подзапросу в `FROM` ссылаться на столбцы из таблиц, перечисленных левее — здесь `u.id` из `users`.
Без `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`, потом фильтрация по `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` по `day` — он оставит только корректные дни.
BИспользовать `FULL JOIN` по `day` и собирать дату через `COALESCE`, а отсутствующие метрики заменять через `COALESCE(..., 0)`.
CИспользовать `LEFT JOIN` от `daily_orders` к `daily_spend` — этого достаточно для всех дней.
DСклеить таблицы через `UNION`, чтобы значения автоматически встали в нужные колонки.
Ответ: `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` может ссылаться на внешние таблицы
BЗапрос вернёт ошибку: без `LATERAL` подзапрос в `FROM` не может ссылаться на `u.id`
CЗапрос выполнится, но подзапрос проигнорирует условие `user_id = u.id` и вернёт все заказы
DЗапрос выполнится, но вернёт только один заказ для всех пользователей вместо последнего для каждого
Ответ: Без ключевого слова `LATERAL` подзапрос в `FROM` не может ссылаться на другие таблицы из того же `FROM` — запрос вернёт ошибку ссылки на `u.id`.
В стандартном SQL подзапрос в `FROM` (derived table) изолирован от внешнего контекста. Ссылка `u.id` внутри такого подзапроса вызовет ошибку: `invalid reference to FROM-clause entry for table u`. Чтобы разрешить корреляцию, нужно добавить `LATERAL`: `FROM users u, LATERAL (SELECT ... WHERE user_id = u.id ...) last_order`. Это явная декларация зависимости подзапроса от внешней таблицы.
30Есть справочник `products(product_id)` и лог продаж `sales(product_id, order_id)`. Нужно найти (1) товары из справочника без продаж и (2) продажи по товарам, которых нет в справочнике. Какой подход подходит лучше всего?
AСделать `INNER JOIN` и отфильтровать `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`.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram