Как делать выборку связанных данных в чистом SQL

Что проверяют

Умеете ли вы сами достать данные для проверки, а не просить разработчика. Для тестировщика это ежедневная работа: убедиться, что после регистрации создались записи во всех связанных таблицах, найти «сирот» и дубли. Следом обычно дают задачу написать запрос прямо на собеседовании.

Как отвечать

Основной инструмент это JOIN по внешнему ключу:

• INNER JOIN: только строки, у которых нашлась пара. Пользователь без роли в выборку не попадёт;
• LEFT JOIN: все строки левой таблицы, у несовпавших правые колонки заполнены NULL;
• RIGHT JOIN: то же зеркально, на практике его переписывают в LEFT, поменяв таблицы местами;
• FULL OUTER JOIN: все строки обеих таблиц. В MySQL его нет, эмулируют через UNION двух LEFT JOIN.

Для тестирования особенно полезен anti-join, поиск нарушенных связей:

SELECT o.id FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Это заказы, чей пользователь не существует. То же пишется через NOT EXISTS. Кроме соединений назовите подзапросы, агрегацию с GROUP BY ... HAVING COUNT(*) > 1 для поиска дублей и CTE через WITH, когда запрос состоит из нескольких шагов.

Покажите, что понимаете кратность связи: при соединении один ко многим строки размножаются, и COUNT или SUM по такой выборке даёт завышенный результат.

Где спотыкаются

Ставят условие на правую таблицу в WHERE после LEFT JOIN. Строки с NULL отфильтровываются, и запрос молча превращается в INNER JOIN. Такое условие нужно переносить в ON.

Пишут NOT IN (SELECT user_id FROM ...), а в подзапросе встречается NULL. Результат становится пустым, и тест ложно зеленеет. У NOT EXISTS такой ловушки нет.

Забывают про размножение строк и потом ищут баг в приложении, хотя дубли создал собственный запрос. Сверяйте количество строк до и после соединения.