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(DISTINCT u.user_id) FROM users u INNER JOIN orders o ON u.user_id = o.user_id;`
B`SELECT COUNT(*) FROM users u INNER JOIN orders o ON u.user_id = o.user_id;`
C`SELECT COUNT(u.user_id) FROM users u LEFT JOIN orders o ON u.user_id = o.user_id;`
D`SELECT COUNT(*) FROM orders;`
Ответ: В связке one-to-many `COUNT(*)` после `JOIN` считает строки (заказы), поэтому для пользователей нужен `COUNT(DISTINCT ...)`.
Если у одного пользователя несколько заказов, `INNER JOIN` создаст несколько строк на одного пользователя. Поэтому `COUNT(*)` посчитает заказы, а не пользователей. Чтобы получить число уникальных пользователей с заказами, используйте `COUNT(DISTINCT u.user_id)` после соединения.
22Три способа найти пользователей без заказов: `LEFT JOIN + IS NULL`, `NOT EXISTS`, `NOT IN`. Столбец `orders.user_id` содержит `NULL`-значения. Какой подход даст неожиданный результат?
AВсе три подхода всегда возвращают одинаковый результат независимо от данных в таблицах
BТолько `LEFT JOIN + IS NULL` корректно обработает `NULL` — остальные два способа дадут ошибку
C`NOT IN` вернёт пустой результат, если в `orders.user_id` есть хотя бы один `NULL`
DТолько `NOT EXISTS` вернёт пустой результат при наличии `NULL` в `orders.user_id`
Ответ: `NOT IN` при наличии `NULL` в подзапросе вернёт пустой набор, потому что сравнение с `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Первый запрос вернёт ошибку — в `ON` нельзя добавлять условия на столбцы правой таблицы
CПервый запрос отбросит пользователей без заказов — `WHERE` после `LEFT JOIN` превращает его в `INNER`
DПервый запрос уберёт всех пользователей без заказов, а второй сохранит их с `NULL` в столбцах заказа
Ответ: Условие в `WHERE` после `LEFT JOIN` отфильтрует строки с `NULL` — пользователи без заказов пропадут. Условие в `ON` ограничит только соединение, сохранив всех пользователей.
При `LEFT JOIN` сначала выполняется соединение по `ON`, затем добавляются строки из левой таблицы без пар — с `NULL` в правых столбцах. Если `o.amount > 1000` стоит в `WHERE`, строки с `NULL` (пользователи без подходящих заказов) отфильтруются — `LEFT JOIN` фактически станет `INNER JOIN`. Если условие в `ON`, оно влияет только на подбор пар, а пользователи без совпадений останутся с `NULL`.
24Нужно сверить остатки товаров между учётной системой и складской. Некоторые товары есть только в одной из систем. Как получить полный список расхождений?
AИспользовать `FULL OUTER JOIN` по `product_id` и `COALESCE` для выбора не-`NULL` значения идентификатора
BИспользовать `INNER JOIN` по `product_id` — он автоматически покажет расхождения между системами
CИспользовать `LEFT JOIN` в обе стороны и объединить результаты через `UNION` без дедупликации
DИспользовать `CROSS JOIN` и в `WHERE` отфильтровать строки, где `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)` нужен, чтобы получить идентификатор товара независимо от того, в какой таблице он присутствует.
25Таблица `tiers` хранит ценовые диапазоны: `tier_name`, `min_price`, `max_price`. Нужно каждому продукту присвоить тариф по его цене. Как написать соединение?
AНаписать `JOIN` по точному совпадению цены с границами диапазона: `ON p.price = t.min_price`
BНаписать условие диапазона в `ON`: `ON p.price >= t.min_price AND p.price < t.max_price`
CНаписать `CROSS JOIN` и добавить `WHERE p.price = t.tier_name` для фильтрации по названию тарифа
DНаписать `LEFT JOIN` по `tier_id` — для диапазонных условий нужен явный внешний ключ
Ответ: Условие `ON` поддерживает неравенства: `ON p.price >= t.min_price AND p.price < t.max_price` соединит продукт с тарифом, в диапазон которого попадает цена.
В `ON` допустимы любые условия, не только равенство. Range join соединяет строки по попаданию значения в диапазон. Важно следить за границами: `>=` и `<` гарантируют, что смежные диапазоны не пересекаются. Если диапазоны перекрываются, продукт попадёт в несколько тарифов — строка продублируется. Такие соединения могут быть медленными на больших таблицах без индексов.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram