Какие виды JOIN существуют в SQL и чем отличаются left join, right join и inner join

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

Понимаете ли вы поведение строк без пары и не путаете ли фильтр в ON с фильтром в WHERE.

Как отвечать

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

Отдельно LATERAL в PostgreSQL: подзапрос справа видит колонки левой строки, что позволяет для каждой записи взять, например, три последних связанных.

Главная практическая тонкость: условие в ON применяется до соединения, условие в WHERE после. Поэтому в LEFT JOIN фильтр по правой таблице, поставленный в WHERE, отбрасывает строки с NULL и превращает соединение в INNER. Условие по правой таблице должно жить в ON.

Ещё полезно знать, что физически соединение выполняется вложенным циклом, хеш-соединением или слиянием, и выбор делает планировщик по статистике. Это видно в EXPLAIN и объясняет, почему на маленькой таблице быстро, а на большой нет.

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

Ставят условие по правой таблице в WHERE при LEFT JOIN и теряют строки.

Не могут объяснить разницу LEFT и INNER на данных без пары.

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

Забывают условие соединения и получают декартово произведение.