JOIN и операции множеств: вопросы для собеседования (часть 4)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 16–20 из 40
16У вас есть два набора пользователей одного типа: `new_users_ru(user_id)` и `new_users_kz(user_id)`. Нужно получить общий список пользователей для дальнейшего соединения с `orders`. Что сделать на первом шаге?
A`SELECT user_id FROM new_users_ru UNION SELECT user_id FROM new_users_kz;`
B`SELECT r.user_id FROM new_users_ru r INNER JOIN new_users_kz k ON r.user_id = k.user_id;`
C`SELECT r.user_id FROM new_users_ru r LEFT JOIN new_users_kz k ON r.user_id = k.user_id;`
D`SELECT r.user_id, k.user_id FROM new_users_ru r CROSS JOIN new_users_kz k;`
Ответ: Когда нужно объединить однотипные строки из двух таблиц в один список, используют `UNION` или `UNION ALL`, а не `JOIN`.
`UNION` складывает два набора пользователей в один список и убирает дубликаты, `UNION ALL` сохраняет повторы. Любой `JOIN` между таблицами решает другую задачу — он сопоставляет строки по ключу: `INNER JOIN` даст только пересечение, `LEFT JOIN` склеит значения только справа, `CROSS JOIN` вернёт декартово произведение. Ни один из этих вариантов не даёт нужный объединённый список пользователей.
Подробный разбор → 17Без оконных функций нужно для каждого дня посчитать изменение выручки по сравнению с предыдущим днём. Таблица `daily_revenue` содержит `date` и `revenue`. Как это сделать?
AСоединить таблицу саму с собой по равенству дат: `ON a.date = b.date` и затем вычислить разницу
BИспользовать `CROSS JOIN` таблицы с собой и в `WHERE` оставить пары с минимальной разницей дат
CИспользовать `UNION ALL` текущего и сдвинутого запроса для формирования пар строк по дате
DСоединить таблицу с собой по условию `ON b.date = a.date - INTERVAL '1 day'` для получения предыдущего дня
Ответ: Self-join со сдвигом: `FROM daily_revenue a JOIN daily_revenue b ON b.date = a.date - INTERVAL '1 day'` даёт пару текущего и предыдущего дня для вычисления разницы.
До появления оконных функций соединение таблицы с собой было основным способом сравнить строку с предыдущей. Соединяем таблицу с собой: `a` — текущий день, `b` — предыдущий через условие `b.date = a.date - INTERVAL '1 day'`. Разница: `a.revenue - b.revenue`. Первый день потеряет пару — `INNER JOIN` его отбросит, `LEFT JOIN` сохранит с `NULL`. Современная альтернатива: `LAG(revenue) OVER(ORDER BY date)`.
Подробный разбор → 18Есть таблицы `payments(user_id)` и `refunds(user_id)`. Нужно получить пользователей, у которых был платёж, но не было ни одного возврата. Какой запрос корректнее всего описывает задачу?
A`SELECT user_id FROM payments INTERSECT SELECT user_id FROM refunds`
B`SELECT p.user_id FROM payments p INNER JOIN refunds r ON p.user_id = r.user_id`
C`SELECT user_id FROM payments EXCEPT SELECT user_id FROM refunds`
D`SELECT user_id FROM payments UNION ALL SELECT user_id FROM refunds`
Ответ: `EXCEPT` возвращает разность множеств: значения из первой выборки, которых нет во второй.
Для условия «платили, но не возвращали» нужна именно разность наборов пользователей, и `EXCEPT` делает это напрямую на уровне множеств. `INTERSECT` и `INNER JOIN` дают пересечение — пользователей, у которых были и платежи, и возвраты, что прямо противоположно задаче. `UNION ALL` объединит обе таблицы и не отфильтрует возвраты. Альтернативой `EXCEPT` может быть `LEFT JOIN ... WHERE r.user_id IS NULL` или `NOT IN`, но именно `EXCEPT` короче всего описывает разность.
Подробный разбор → 19Есть два списка пользователей: `push_subscribers(user_id)` и `email_subscribers(user_id)`. Нужно получить пользователей, которые есть в обоих списках. Что проще и надёжнее использовать?
A`SELECT user_id FROM push_subscribers INTERSECT SELECT user_id FROM email_subscribers;`
B`SELECT user_id FROM push_subscribers UNION SELECT user_id FROM email_subscribers;`
C`SELECT user_id FROM push_subscribers EXCEPT SELECT user_id FROM email_subscribers;`
D`SELECT p.user_id FROM push_subscribers p LEFT JOIN email_subscribers e ON p.user_id = e.user_id WHERE e.user_id IS NULL;`
Ответ: `INTERSECT` возвращает пересечение двух наборов — значения, которые есть и там, и там.
Если цель — пересечение множеств `user_id`, то `INTERSECT` выражает задачу напрямую: он вернёт только тех пользователей, которые присутствуют в обеих таблицах. `UNION` даёт объединение, а `EXCEPT` — разность множеств (в одной таблице, но не в другой).
Подробный разбор → 20Как переписать запрос без `RIGHT JOIN`, используя только `LEFT JOIN`? Исходный запрос: `SELECT u.user_id, o.order_id FROM users u RIGHT JOIN orders o ON u.user_id = o.user_id;`
A`SELECT u.user_id, o.order_id FROM users u LEFT JOIN orders o ON u.user_id = o.user_id;`
B`SELECT u.user_id, o.order_id FROM orders o LEFT JOIN users u ON u.user_id = o.user_id;`
C`SELECT u.user_id, o.order_id FROM users u FULL JOIN orders o ON u.user_id = o.user_id;`
D`SELECT u.user_id, o.order_id FROM users u INNER JOIN orders o ON u.user_id = o.user_id;`
Ответ: `RIGHT JOIN` эквивалентен `LEFT JOIN`, если поменять таблицы местами в `FROM`.
`RIGHT JOIN` сохраняет все строки из правой таблицы, в исходном запросе это таблица заказов. То же самое можно получить через `LEFT JOIN`, если поставить таблицу заказов слева, а пользователей справа: `FROM orders o LEFT JOIN users u ...`. `FULL JOIN` оставит и заказы без пользователя, и пользователей без заказов — это уже другая семантика. `INNER JOIN` отбросит заказы, у которых нет соответствующего пользователя, и тоже не эквивалентен исходному запросу.
Подробный разбор →