Как работают виды JOIN: left join, right join, outer join, inner join в SQL?

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

Понимаете ли вы, сколько строк вернёт запрос и откуда в результате берутся NULL и дубли. Следом обычно дают задачу: найти пользователей без заказов или объяснить, почему условие в WHERE превратило LEFT JOIN во внутреннее соединение.

Как отвечать

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

Слово OUTER необязательное: LEFT JOIN и LEFT OUTER JOIN это одно и то же, а «outer join» из вопроса это общее название для LEFT, RIGHT и FULL.

Главное, что стоит проговорить: join возвращает строку на каждую совпавшую пару. Пользователь с тремя заказами даст три строки, и если потом посчитать SUM(u.bonus), бонус утроится.

Поиск строк без пары это anti join: LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL или NOT EXISTS, второй вариант обычно понятнее.

Для Python-разработчика стоит связать это с ORM. В Django select_related делает INNER JOIN по обязательному внешнему ключу и LEFT OUTER JOIN по допускающему NULL. В SQLAlchemy join() даёт внутреннее соединение, outerjoin() левое, а с full=True полное. В pandas тот же выбор задаёт параметр how у merge.

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

Фильтруют правую таблицу в WHERE после LEFT JOIN: WHERE o.status = 'paid' отбрасывает строки с NULL, и соединение фактически становится внутренним. Условие на правую сторону переносят в ON.

Не ждут размножения строк в связи один ко многим и получают завышенные суммы и счётчики.

Забывают, что NULL = NULL не истина. Строки с пустым ключом не соединятся ни в одном виде join, их нужно обрабатывать отдельно.