Ответ

INNER JOIN возвращает только те записи, для которых найдено совпадение в обеих соединяемых таблицах по указанному условию. При использовании OUTER JOIN (LEFT, RIGHT, FULL) результат содержит все строки из одной или обеих таблиц, даже если в другой таблице соответствия нет.

LEFT OUTER JOIN: все строки из левой таблицы + совпадения из правой, если есть (иначе NULL).
RIGHT OUTER JOIN: все строки из правой таблицы + совпадения из левой, если есть (иначе NULL).
FULL OUTER JOIN: все строки из обеих таблиц, где нет совпадения — противоположные значения NULL.

Когда какой JOIN использовать:

Используйте INNER JOIN, если нужны только совпадающие строки.
LEFT JOIN — если важны все строки первой (левой) таблицы вне зависимости от наличия соответствия.
RIGHT JOIN — аналогично, но для правой таблицы.
FULL OUTER JOIN — полный объединённый список (полезно для поиска несовпадений).

Примеры:

-- INNER JOIN
SELECT u.id, u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT OUTER JOIN
SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN
SELECT u.id, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Вопрос с подвохом

Какой результат даст LEFT JOIN, если правая таблица пуста?

Часто отвечают: "LEFT JOIN вернет пустой результат, так как нет совпадений". Правильно: LEFT JOIN в любом случае вернет все строки из левой таблицы, а столбцы из правой будут NULL.

Пример:

-- Таблица users есть записи, orders пуста
SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id=o.user_id;
-- Результат: все пользователи, o.amount = NULL

Примеры реальных ошибок из-за незнания тонкостей темы

История

Проект аналитики продаж. Для вывода полного списка клиентов и их заказов использовался INNER JOIN, пропуская клиентов, у которых не было заказов. В отчёте потерялись "важные" клиенты из-за их отсутствия в правой таблице. Решение: использовать LEFT JOIN.

История

Платформа учета сотрудников и отделов. Использование RIGHT JOIN вместо LEFT JOIN привело к путанице при добавлении новых отделов: некоторые сотрудники перестали попадать в отчет. Причина — перепутан порядок соединения.

История

Миграция схемы данных. После изменений один из JOIN'ов превратился в CROSS JOIN из-за опечатки в условии соединения. Итог — резкий рост объема выборки и "раздувание" данных. Вывод: всегда указывать ON при соединениях и тщательно проверять тип JOIN.