JOIN и операции множеств: вопросы для собеседования (часть 5)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 21–25 из 40
21Нужно посчитать число пользователей, которые сделали хотя бы 1 заказ (таблицы `users(user_id)` и `orders(user_id, order_id)`). Какой запрос посчитает правильно?
A`SELECT COUNT(*) FROM users u INNER JOIN orders o ON u.user_id = o.user_id`
B`SELECT COUNT(u.user_id) FROM users u LEFT JOIN orders o ON u.user_id = o.user_id`
C`SELECT COUNT(DISTINCT u.user_id) FROM users u JOIN orders o ON u.user_id = o.user_id`
D`SELECT COUNT(DISTINCT o.order_id) FROM orders o WHERE o.user_id IS NOT NULL`
Ответ: В связке один-ко-многим `COUNT(*)` после `JOIN` считает строки (заказы), поэтому для уникальных пользователей нужен `COUNT(DISTINCT ...)`.
Если у одного пользователя несколько заказов, `INNER JOIN` создаст несколько строк на одного пользователя — `COUNT(*)` посчитает заказы, а не пользователей. `LEFT JOIN` без `WHERE` оставит и тех, у кого нет заказов, и опять же даст не то число. Чтобы получить число уникальных пользователей с заказами, используйте `COUNT(DISTINCT u.user_id)` после соединения. Альтернатива — `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id)`.
Подробный разбор → 22Три способа найти пользователей без заказов: `LEFT JOIN + IS NULL`, `NOT EXISTS`, `NOT IN`. Столбец `orders.user_id` содержит `NULL`-значения. Какой подход даст неожиданный результат?
AВсе три подхода всегда возвращают одинаковый результат независимо от значений `NULL` в данных таблиц
BТолько `LEFT JOIN ... IS NULL` корректно обработает `NULL`, а `NOT EXISTS` и `NOT IN` дадут синтаксическую ошибку
C`NOT IN` вернёт пустой результат, если в столбце `orders.user_id` встречается хотя бы один `NULL`
DТолько `NOT EXISTS` вернёт пустой результат, если в столбце `orders.user_id` встречается хотя бы один `NULL`
Ответ: Если в подзапросе встречается `NULL`, выражение `NOT IN` вернёт пустой набор, потому что сравнение с `NULL` даёт `UNKNOWN` и ни одна строка не проходит фильтр.
Выражение `x NOT IN (1, 2, NULL)` раскрывается в `x<>1 AND x<>2 AND x<>NULL`. Последнее сравнение всегда даёт `UNKNOWN`, а `TRUE AND UNKNOWN = UNKNOWN` — ни одна строка не пройдёт. `NOT EXISTS` и `LEFT JOIN + IS NULL` не страдают от этой проблемы: они проверяют наличие строки, а не равенство значений. Рекомендация: для анти-соединений избегать `NOT IN`, если в подзапросе возможны `NULL`.
Подробный разбор → 23Запрос 1: `SELECT * FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.amount > 1000`. Запрос 2: `SELECT * FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 1000`. Чем отличаются результаты?
AОба запроса вернут одинаковый результат: всех пользователей и их заказы на сумму строго больше `1000` рублей
BПервый запрос вернёт ошибку: условия на правую таблицу в блоке `WHERE` после `LEFT JOIN` записывать недопустимо
CПервый запрос отбросит пользователей без подходящих заказов, второй сохранит их со значением `NULL` в колонках
DПервый запрос сохранит всех пользователей с `NULL` в заказах, а второй превратит соединение во `INNER JOIN`
Ответ: Условие на правую таблицу в `WHERE` после левого соединения отфильтрует пользователей без заказов, а в `ON` — сохранит их со значениями `NULL`.
При левом соединении сначала выполняется условие из `ON`, а затем к результату добавляются строки из левой таблицы без пары — в правых столбцах будет `NULL`. Если условие на сумму стоит в `WHERE`, строки с `NULL` отфильтруются и левое соединение фактически превратится во внутреннее. Если же условие живёт в `ON`, оно сужает только подбор пар, а пользователи без подходящих заказов всё равно останутся в результате со значениями `NULL`. Это типичная ловушка с фильтрами по правой таблице при левом соединении.
Подробный разбор → 24Нужно сверить остатки товаров между учётной системой и складской. Некоторые товары есть только в одной из систем. Как получить полный список расхождений?
AПрименить `FULL OUTER JOIN` по `product_id` плюс `COALESCE(a.product_id, b.product_id)` для выбора не-`NULL` ключа
BПрименить `INNER JOIN` по `product_id`: он покажет товары, которые есть только в одной из систем сразу автоматически
CПрименить `LEFT JOIN` в обе стороны и склеить результаты через `UNION ALL` без дедупликации совпадающих ключей
DПрименить `CROSS JOIN` и отфильтровать в `WHERE` строки, где `a.product_id` отличается от `b.product_id` по значению
Ответ: `FULL OUTER JOIN` сохраняет строки из обеих таблиц, а `COALESCE(a.product_id, b.product_id)` гарантирует, что идентификатор не потеряется.
`FULL OUTER JOIN` объединяет все строки: совпавшие по ключу, строки только из левой таблицы (с `NULL` справа) и строки только из правой (с `NULL` слева). Для сверки добавляют `WHERE a.qty IS DISTINCT FROM b.qty` — это покажет все расхождения. `COALESCE(a.product_id, b.product_id)` нужен, чтобы получить идентификатор независимо от того, в какой таблице товар присутствует. `INNER JOIN` отбросит односторонние товары, `UNION ALL` без дедупликации удвоит совпадения, а `CROSS JOIN` создаст лишние комбинации.
Подробный разбор → 25Таблица `tiers` хранит ценовые диапазоны: `tier_name`, `min_price`, `max_price`. Нужно каждому продукту присвоить тариф по его цене. Как написать соединение с `products`?
AСоединить через равенство цены и одной из границ: `ON p.price = t.min_price` для попадания на нижний край
BЗаписать диапазонное условие в `ON`: `ON p.price >= t.min_price AND p.price < t.max_price` для попадания в интервал
CСделать `CROSS JOIN`, а в `WHERE` фильтровать по совпадению названий: `WHERE p.tier_name = t.tier_name` после соединения
DИспользовать `LEFT JOIN ON p.tier_id = t.tier_id`: внешний ключ обязателен для соединения по диапазону цен
Ответ: Условие `ON` поддерживает неравенства: `ON p.price >= t.min_price AND p.price < t.max_price` соединит продукт с тарифом, в диапазон которого попадает цена.
В блоке `ON` допустимы любые булевы условия, не только равенство. Соединение по диапазону связывает строки по попаданию значения в интервал: `ON p.price >= t.min_price AND p.price < t.max_price`. Сочетание `>=` и `<` гарантирует, что смежные диапазоны не пересекаются и продукт попадёт ровно в один тариф. Если диапазоны перекрываются, продукт продублируется, поэтому стоит проверять корректность границ. На больших таблицах такие соединения работают медленно без подходящих индексов и часто переписываются через оконные функции или предрасчёт.
Подробный разбор →