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 FROM new_users_ru r FULL JOIN new_users_kz k ON r.user_id = k.user_id;`
Ответ: Когда нужно объединить однотипные строки из двух таблиц, используйте `UNION`/`UNION ALL`, а не `JOIN`.
`JOIN` между `new_users_ru` и `new_users_kz` будет сравнивать пользователей между собой: `INNER JOIN` даст пересечение, а `LEFT/FULL JOIN` даст «склеивание» по ключу. Но задача здесь другая — сложить два списка в один. Это делается через `UNION` (если нужны уникальные пользователи) или `UNION ALL` (если нужно сохранить дубликаты).
17Без оконных функций нужно для каждого дня посчитать изменение выручки по сравнению с предыдущим днём. Таблица `daily_revenue` содержит `date` и `revenue`. Как это сделать?
AСоединить таблицу саму с собой по равенству дат: `ON a.date = b.date` и вычислить разницу
BИспользовать `CROSS JOIN` таблицы с собой и в `WHERE` оставить пары с минимальной разницей дат
CСоединить таблицу с собой по условию `ON b.date = a.date - INTERVAL '1 day'` для получения предыдущего дня
DИспользовать `UNION ALL` текущего и сдвинутого запроса для формирования пар строк
Ответ: Self-join со сдвигом: `FROM daily_revenue a JOIN daily_revenue b ON b.date = a.date - INTERVAL '1 day'` даёт пару текущего и предыдущего дня для вычисления разницы.
До появления оконных функций self-join был основным способом сравнить строку с предыдущей. Соединяем таблицу с собой: `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 user_id FROM payments UNION SELECT user_id FROM refunds;`
C`SELECT p.user_id FROM payments p INNER JOIN refunds r ON p.user_id = r.user_id;`
D`SELECT user_id FROM payments EXCEPT SELECT user_id FROM refunds;`
Ответ: `EXCEPT` возвращает разность множеств: значения из первой выборки, которых нет во второй.
Для «платили, но не возвращали» нужна именно разность наборов пользователей. `EXCEPT` делает это напрямую на уровне множеств. `INNER JOIN` и `INTERSECT` дадут пользователей, которые есть в обеих таблицах, то есть как раз тех, у кого были и платежи, и возвраты.
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 UNION SELECT user_id, order_id FROM orders;`
Ответ: `RIGHT JOIN` эквивалентен `LEFT JOIN`, если поменять таблицы местами.
`RIGHT JOIN` сохраняет все строки из правой таблицы (`orders`). То же самое можно сделать через `LEFT JOIN`, если поставить `orders` слева: `FROM orders o LEFT JOIN users u ...`. Такой стиль обычно читается проще и реже приводит к путанице в аналитических запросах.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram