OUTER JOIN

Outer join сохраняет строки одной или обеих сторон, даже если соответствие не найдено. Недостающие столбцы заполняются NULL.

LEFT JOIN

SELECT c.id, c.name, p.id AS purchase_id
FROM customer c
LEFT JOIN purchase p
    ON p.customer_id = c.id
ORDER BY c.id, p.id;

Все покупатели останутся. Покупатель без покупок получит одну строку с purchase_id = NULL.

Полезный запрос «у кого нет покупок»:

SELECT c.id, c.name
FROM customer c
LEFT JOIN purchase p ON p.customer_id = c.id
WHERE p.id IS NULL;

Если в правой таблице подходящих строк несколько, левая строка всё равно повторяется.

Условие в ON и WHERE

Это главное различие, которое нужно помнить:

-- сохраняет всех покупателей;
-- справа присоединяются только оплаченные покупки
FROM customer c
LEFT JOIN purchase p
  ON p.customer_id = c.id
 AND p.status = 'PAID'

-- удаляет строки без покупки и фактически делает результат похожим на INNER JOIN
FROM customer c
LEFT JOIN purchase p ON p.customer_id = c.id
WHERE p.status = 'PAID'

В первом варианте фильтр участвует в поиске соответствия. Во втором он применяется после join, а выражение NULL = 'PAID' не истинно.

RIGHT JOIN

RIGHT JOIN сохраняет все строки правой стороны. Его всегда можно переписать как LEFT JOIN, поменяв таблицы местами. В длинном запросе единое направление LEFT JOIN часто проще читать.

FULL OUTER JOIN

FULL OUTER JOIN сохраняет:

• совпавшие пары;
• строки только из левой таблицы;
• строки только из правой таблицы.

SELECT a.external_key, b.external_key
FROM import_a a
FULL OUTER JOIN import_b b
    ON b.external_key = a.external_key;

Так удобно сверять два набора. Поддержка FULL OUTER JOIN зависит от СУБД. Переносимая эмуляция требует аккуратно объединить left-only, совпавшие и right-only строки через UNION ALL.

Трёхзначная логика

Условие SQL получает TRUE, FALSE или UNKNOWN. В ON парой считаются только строки с TRUE; в WHERE остаются только строки с TRUE. Поэтому NULL требует IS NULL, а не = NULL.

На собеседовании

Как WHERE может сломать left join? Фильтр по nullable-столбцу правой стороны отбрасывает строки, созданные без соответствия.

Что возвращает full join? Все совпадения и несовпавшие строки обеих сторон.

Связанные заметки: INNER JOIN, Многотабличные запросы операция JOIN, SQL.