JOIN и операции множеств: вопросы для собеседования (часть 8)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 36–40 из 40
36Есть два списка таргетинга: `promo_a_users(user_id)` и `promo_b_users(user_id)`. Нужно получить пользователей, которые попали ровно в один список (исключающее ИЛИ), чтобы не задвоить коммуникации. Какой вариант верный?
A`SELECT user_id FROM promo_a_users INTERSECT SELECT user_id FROM promo_b_users` оставит общих пользователей двух списков
B`SELECT user_id FROM promo_a_users UNION SELECT user_id FROM promo_b_users` соберёт всех пользователей из обоих списков с дедупом
C`SELECT a.user_id FROM promo_a_users a JOIN promo_b_users b ON a.user_id = b.user_id` соединит совпадающие записи по ключу
D`(SELECT user_id FROM promo_a_users EXCEPT SELECT user_id FROM promo_b_users) UNION (SELECT user_id FROM promo_b_users EXCEPT promo_a_users)`
Ответ: Исключающее ИЛИ для двух наборов собирается как объединение двух разностей через `EXCEPT` и `UNION`.
`INTERSECT` и внутреннее соединение по `user_id` вернут пользователей, которые есть в обоих списках, — это противоположность задачи. `UNION` вернёт всех пользователей из обоих списков, включая пересечение, поэтому задвоение коммуникаций как раз случится. Чтобы получить только тех, кто есть ровно в одном из двух списков, берут разность в обе стороны через `EXCEPT` и затем объединяют результаты через `UNION`.
Подробный разбор → 37СУБД не поддерживает `FULL OUTER JOIN` (например, MySQL версии до 8.0). Как корректно эмулировать его для сверки таблиц `system_a` и `system_b` по `product_id`?
AНаписать `LEFT JOIN` и заменить `NULL` через `COALESCE`: такой приём заявляется как полный аналог `FULL OUTER JOIN`
BНаписать `CROSS JOIN` и отфильтровать несовпадающие строки в `WHERE`: такой приём предлагают как замену соединения
CНаписать `LEFT JOIN` для совпадений плюс `RIGHT JOIN` с условием `WHERE a.id IS NULL`, объединённые через `UNION ALL`
DНаписать только `INNER JOIN` по `product_id` и считать его эквивалентом `FULL OUTER JOIN`: совпавшие строки уже включены
Ответ: Эмуляция: `LEFT JOIN` плюс `UNION ALL` со строками только из `system_b` (правое анти-соединение).
Классический способ: `SELECT * FROM a LEFT JOIN b USING(id) UNION ALL SELECT * FROM a RIGHT JOIN b USING(id) WHERE a.id IS NULL`. Первая часть даёт все строки из `a` со совпадениями из `b`. Вторая — только строки из `b` без пары. `UNION ALL` сохраняет все строки без дедупликации. `LEFT JOIN` с `COALESCE` теряет строки, которые есть только в `b`. `CROSS JOIN` со фильтром даёт декартово произведение, а не сверку. Один `INNER JOIN` теряет несовпавшие строки с обеих сторон.
Подробный разбор → 38Таблица `eligible_users(user_id)` может содержать повторяющиеся `user_id`. Таблица `blocked_users(user_id)` — список блокировок. Нужно получить уникальный список пользователей из `eligible_users`, которых нет в `blocked_users`, без добавления `DISTINCT`. Что выбрать?
A`SELECT e.user_id FROM eligible_users e LEFT JOIN blocked_users b ON e.user_id = b.user_id WHERE b.user_id IS NULL;`
B`SELECT e.user_id FROM eligible_users e INNER JOIN blocked_users b ON e.user_id = b.user_id;`
C`SELECT user_id FROM eligible_users UNION ALL SELECT user_id FROM blocked_users;`
D`SELECT user_id FROM eligible_users EXCEPT SELECT user_id FROM blocked_users;`
Ответ: `EXCEPT` работает как операция над множествами и по умолчанию возвращает уникальные значения, поэтому помогает убрать дубликаты без `DISTINCT`.
Анти-джойн через `LEFT JOIN ... WHERE b.user_id IS NULL` вернёт столько строк, сколько было в `eligible_users`, включая повторяющиеся `user_id`. Если нужен именно уникальный список и вы не хотите добавлять `DISTINCT`, то `EXCEPT` решает сразу две задачи: убирает пользователей из `blocked_users` и возвращает результат как множество (без повторов).
Подробный разбор → 39Запрос: `SELECT u.id, SUM(o.amount), COUNT(r.id) FROM users u JOIN orders o ON o.user_id = u.id JOIN reviews r ON r.user_id = u.id GROUP BY u.id`. У пользователя 3 заказа и 4 отзыва. Что не так и как исправить?
AКаскадное дублирование исключено: `GROUP BY` в конце запроса дедуплицирует строки автоматически перед агрегацией
BДобавить `DISTINCT` внутрь агрегата: `SUM(DISTINCT o.amount)` устранит каскадные дубликаты сумм заказов корректно
CАгрегировать каждую таблицу в отдельном подзапросе до `JOIN`, а затем соединять готовые агрегаты по `user_id`
DЗаменить `INNER JOIN` на `LEFT JOIN`: внешнее соединение само по себе не порождает каскадных дубликатов строк
Ответ: Два `JOIN` «один ко многим» к одной таблице дают каскадное умножение строк: 3 × 4 = 12. `SUM` завышен в 4 раза, `COUNT` — в 3 раза. Решение — агрегировать в подзапросах до соединения.
Когда `users` соединяется с `orders` (3 строки) и с `reviews` (4 строки), получается 12 комбинаций для одного пользователя. `SUM(o.amount)` считает каждый заказ 4 раза, `COUNT(r.id)` — каждый отзыв 3 раза. `SUM(DISTINCT)` не поможет, если суммы заказов совпадают между собой. Правильный подход — `JOIN (SELECT user_id, SUM(amount) ... GROUP BY user_id) o`: агрегировать заказы и отзывы по отдельности в подзапросах, а потом соединять готовые агрегаты.
Подробный разбор → 40Вы соединили заказы с `order_items` и `payments` по `order_id`, а потом посчитали `SUM(paid_amount)`. Сумма оказалась завышенной. Что вероятнее всего произошло и как исправить?
AИспользуется `INNER JOIN`; заменить его на `LEFT JOIN`, тогда платежи перестанут дублироваться при соединении трёх таблиц
BСтроки умножились из-за нескольких записей в `order_items` и `payments`; агрегировать каждую до уровня `order_id` и затем делать `JOIN`
CДело в `NULL` в столбце `paid_amount`; обернуть сумму в `COALESCE` и заменить пустые значения на ноль для устранения завышения
DЗаменить `JOIN` на `UNION` между подзапросами и оставить ключ заказа, тогда повторяющиеся платежи уйдут автоматически
Ответ: При соединении нескольких таблиц с отношением «один-ко-многим» по одному ключу строки могут размножаться и завышать суммы.
Если у заказа три позиции в `order_items` и два платежа в `payments`, после соединения получится шесть строк на один заказ. Тогда `SUM(paid_amount)` посчитает один и тот же платёж несколько раз. Типичный фикс — сначала агрегировать `order_items` до одной строки на `order_id` и отдельно агрегировать `payments` до одной строки на `order_id`, и только затем соединять эти агрегаты с заказами. Замена типа соединения или `COALESCE` проблему не решают.
Подробный разбор →