Какие виды join существуют, как они работают и нужен ли внешний ключ для join

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

Умеете ли вы сами достать данные из связанных таблиц и понимаете ли, откуда в результате берутся лишние строки или NULL. Вопрос про внешний ключ проверяет, отличаете ли вы ограничение целостности от условия соединения.

Как отвечать

Основные виды:

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

Внешний ключ для JOIN не нужен. Соединять можно по любым колонкам и даже по неравенству: ON o.created_at BETWEEN p.start_date AND p.end_date. Внешний ключ это ограничение: он не даст записать заказ на несуществующего пользователя. Скорости он сам по себе не добавляет, а PostgreSQL даже индекс под колонку внешнего ключа не создаёт автоматически, в отличие от MySQL с InnoDB.

Для тестировщика JOIN это рабочий инструмент. LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL находит «сирот», например заказы удалённых пользователей. Соединение с GROUP BY и HAVING COUNT(*) > 1 ловит дубли, а сверка двух таблиц через FULL JOIN показывает, что потерялось при миграции данных.

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

Отвечают, что без внешнего ключа JOIN не работает или работает неправильно. Это два независимых механизма, и в аналитических базах внешних ключей часто нет вовсе.

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

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