JOIN и операции множеств: вопросы для собеседования (часть 3)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 11–15 из 40
11Продажи хранятся в двух таблицах: `online_sales` и `offline_sales` с одинаковой структурой. Нужно получить общую выручку по каждому продукту. Какой паттерн корректен?
AСоединить таблицы через `INNER JOIN` по `product_id` и сложить суммы в одном `SUM` для общего итога по продуктам
BИспользовать `FULL OUTER JOIN` по `product_id`: соединение само сложит суммы из обеих таблиц по продуктам
CОбъединить строки через `UNION ALL`, затем сгруппировать общий результат по `product_id` и применить `SUM`
DНаписать два отдельных `SELECT` с `GROUP BY` и склеить через `UNION` с дедупликацией одинаковых сумм по продуктам
Ответ: `UNION ALL` склеивает строки из обеих таблиц, а внешний `GROUP BY` с `SUM` считает общий итог: `SELECT product_id, SUM(amount) FROM (...UNION ALL...) GROUP BY product_id`.
Паттерн «UNION ALL + GROUP BY»: сначала `UNION ALL` объединяет сырые строки из нескольких источников, затем внешний `SELECT` с `GROUP BY` агрегирует. `JOIN` здесь не подходит — если продукт есть в обеих таблицах, `INNER JOIN` дублирует строки и завысит сумму. `UNION` без `ALL` удалит строки с одинаковыми суммами — тоже ошибка. `FULL OUTER JOIN` не складывает значения автоматически.
Подробный разбор → 12Таблица `sessions` и таблица `purchases` содержат поля `user_id` и `date`. Нужно соединить данные так, чтобы каждой сессии соответствовала покупка того же пользователя в тот же день. Как корректнее всего записать `JOIN`?
AОба условия в `ON`: `ON a.user_id = b.user_id AND a.date = b.date` для соединения по составному ключу
BВ `ON` оставить только `a.user_id = b.user_id`, а равенство дат вынести в `WHERE`: для `LEFT JOIN` результат отличается
CОба условия записать в `WHERE` через запятую: `WHERE a.user_id = b.user_id, a.date = b.date` без раздела `ON`
DВ `ON` соединить по `user_id`, а условие на дату добавить в `HAVING a.date = b.date` после агрегации по дате
Ответ: Несколько условий соединения указываются в `ON` через `AND`, например `ON a.user_id = b.user_id AND a.date = b.date`.
Если совпадение требуется по нескольким столбцам, все условия указываются в `ON` через `AND`. Это аналог соединения по составному ключу. Для `INNER JOIN` перенос условия в `WHERE` даёт тот же результат, но для `LEFT JOIN` — нет: условие в `WHERE` отфильтрует строки с `NULL` и превратит внешнее соединение во внутреннее. Поэтому условия соединения всегда указываются в `ON`.
Подробный разбор → 13Аналитик переписал запрос, поменяв порядок таблиц в цепочке `JOIN`. Запрос стал выполняться быстрее. Почему это могло произойти?
AПорядок таблиц в `FROM` строго определяет порядок соединения: первая таблица становится ведущей в плане
BТаблицы соединяются справа налево: последняя таблица обрабатывается первой, и её положение влияет на скорость
CОптимизатор `PostgreSQL` обычно выбирает порядок сам, но при большом числе таблиц перебор отключается
DПорядок `JOIN` влияет на сам результат запроса: меняя его, можно получить другой набор строк
Ответ: Оптимизатор PostgreSQL обычно сам выбирает порядок соединения, но при большом числе таблиц может не найти лучший план — и ручная перестановка помогает.
Для `INNER JOIN` порядок таблиц не влияет на результат — это коммутативная операция. Оптимизатор перебирает варианты порядка соединения и выбирает лучший план. Но при 8+ таблицах полный перебор отключается параметром `join_collapse_limit`, и оптимизатор следует порядку из запроса. В этом случае ручная перестановка может улучшить план. Для внешних соединений порядок влияет на результат — их оптимизатор не переставляет.
Подробный разбор → 14Какое утверждение про `RIGHT JOIN` верно в аналитических запросах?
A`A RIGHT JOIN B` эквивалентен `B LEFT JOIN A` после перестановки таблиц местами
B`RIGHT JOIN` исполняется быстрее `LEFT JOIN` на одних и тех же данных за счёт оптимизатора
C`RIGHT JOIN` снимает дубликаты по ключу соединения автоматически, как `SELECT DISTINCT`
D`RIGHT JOIN` представляет собой разновидность `UNION ALL` для двух таблиц без потерь
Ответ: `RIGHT JOIN` — это симметричный вариант `LEFT JOIN`: меняете местами таблицы и получаете эквивалентный запрос.
По смыслу `RIGHT JOIN` сохраняет все строки из правой таблицы. Это эквивалентно `LEFT JOIN`, если переставить таблицы: `A RIGHT JOIN B` ↔ `B LEFT JOIN A`. Поэтому `RIGHT JOIN` часто избегают: он ухудшает читаемость, а функционально почти не даёт преимуществ.
Подробный разбор → 15Какое условие обязательно, чтобы запрос с `UNION` был корректным?
AОбе выборки должны быть отсортированы одинаковым `ORDER BY`, иначе строки не получится склеить в один результат.
BОбе выборки должны вернуть одинаковое количество строк, иначе одна из частей не поместится в результирующий набор.
CОбе выборки должны вернуть одинаковое количество колонок с совместимыми типами в соответствующих позициях.
DОбе выборки должны использовать одинаковые имена таблиц, иначе оптимизатор не сможет объединить наборы строк.
Ответ: `UNION` объединяет результаты построчно, поэтому у обеих частей должно совпадать число колонок и совместимость их типов.
Операции над наборами (`UNION`, `INTERSECT`, `EXCEPT`) работают между двумя запросами, которые возвращают одинаковое число колонок. Типы соответствующих колонок должны быть совместимы (например, целые с целыми, строковые со строковыми). Названия колонок берутся из первого запроса, а сортировка вообще не обязана совпадать. Количество строк и имена таблиц не имеют значения для корректности `UNION`.
Подробный разбор →