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 INNER 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 SELECT user_id FROM promo_a_users);`
Ответ: Исключающее ИЛИ для двух наборов можно собрать как объединение двух разностей через `EXCEPT` и `UNION`.
`INTERSECT`/`INNER JOIN` вернут пользователей, которые есть в обоих списках (это противоположность задачи). `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Написать отдельные запросы для совпавших и несовпавших строк: `INNER JOIN`, затем два анти-соединения
DНаписать `LEFT JOIN` в одну сторону, `RIGHT JOIN` — в другую и объединить через `UNION ALL` без дубликатов
Ответ: Эмуляция: `LEFT JOIN` + `UNION ALL` + анти-правая часть (строки только из `system_b`). Или: `INNER JOIN` + левое анти-соединение + правое анти-соединение через `UNION ALL`.
Классический способ: `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` (не `UNION`) сохраняет все строки без дедупликации. Альтернатива: три блока через `UNION ALL` — `INNER JOIN`, `LEFT JOIN ... WHERE b.id IS NULL`, `RIGHT JOIN ... WHERE a.id IS NULL` — нагляднее, но длиннее.
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` — `SUM(DISTINCT o.amount)` устранит все дубликаты корректно
CНужно агрегировать каждую таблицу в подзапросе до `JOIN` — соединять уже готовые агрегаты
DНужно заменить `INNER JOIN` на `LEFT JOIN` — внешнее соединение не создаёт дубликатов
Ответ: Два `JOIN` «один ко многим» к одной таблице дают каскадный fan-out: 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Вы соединили `orders` с `order_items` и `payments` по `order_id`, а затем посчитали `SUM(paid_amount)`. Сумма оказалась завышенной. Что вероятнее всего произошло и как исправить?
AПроблема в том, что используется `INNER JOIN`; нужно заменить на `LEFT JOIN`.
BПроблема из-за `NULL` в `paid_amount`; нужно обернуть сумму в `COALESCE`.
CСтроки умножились (мультипликация) из-за нескольких строк в `order_items` и `payments`; нужно агрегировать каждую таблицу до уровня `order_id` перед `JOIN`.
DНужно заменить `JOIN` на `UNION`, чтобы убрать дубликаты.
Ответ: При соединении нескольких таблиц уровня one-to-many по одному ключу строки могут умножаться и завышать суммы.
Если у заказа 3 позиции в `order_items` и 2 платежа в `payments`, то после соединения получится 6 строк на один заказ. Тогда `SUM(paid_amount)` посчитает один и тот же платёж несколько раз. Типичный фикс: сначала агрегировать `order_items` до одной строки на `order_id` (например, сумма или количество позиций) и отдельно агрегировать `payments` до одной строки на `order_id`, и только потом соединять эти агрегаты с `orders`.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram