В чём разница между INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL OUTER JOIN?

Виды JOIN в PostgreSQL

JOIN — операция объединения строк из двух таблиц по условию. Тип JOIN определяет, что происходит со строками, не нашедшими пары.

INNER JOIN

Возвращает только строки, у которых есть совпадение в обеих таблицах. Строки без пары отбрасываются с обеих сторон.

SELECT u.name, o.id AS order_id, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- Пользователи без заказов НЕ попадут в результат
-- Заказы без пользователя (нарушение FK) тоже исключаются

LEFT JOIN (LEFT OUTER JOIN)

Возвращает все строки левой таблицы. Для строк, не нашедших пары в правой таблице, колонки правой таблицы заполняются NULL.

SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Все пользователи присутствуют; у тех, кто не делал заказов, order_id = NULL

-- Частый паттерн: найти пользователей БЕЗ заказов
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

Зеркало LEFT JOIN — возвращает все строки правой таблицы. На практике почти всегда заменяется LEFT JOIN с переставленными таблицами — это удобнее для чтения.

SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
-- Все заказы присутствуют; у «осиротевших» заказов name = NULL

-- Эквивалентно:
SELECT u.name, o.id AS order_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

FULL OUTER JOIN

Объединяет LEFT и RIGHT JOIN: возвращает все строки из обеих таблиц. Несопоставленные строки с каждой стороны дополняются NULL.

SELECT u.name, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;
-- Пользователи без заказов: order_id = NULL
-- Заказы без пользователей: name = NULL

-- Найти несоответствия с обеих сторон:
SELECT u.name, o.id
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id
WHERE u.id IS NULL OR o.id IS NULL;

CROSS JOIN

Декартово произведение — каждая строка первой таблицы соединяется с каждой строкой второй. Без условия ON/WHERE результат содержит N*M строк.

-- Все комбинации размеров и цветов
SELECT s.name AS size, c.name AS color
FROM sizes s
CROSS JOIN colors c;

Визуальная схема

• INNER: пересечение двух множеств.
• LEFT: левое множество целиком + пересечение.
• RIGHT: правое множество целиком + пересечение.
• FULL OUTER: объединение обоих множеств.

Подводные камни

• Условие в WHERE превращает LEFT JOIN в INNER JOIN: LEFT JOIN ... WHERE right.col = 'x' отфильтрует строки с NULL. Фильтр по правой таблице нужно перенести в ON.
• При декартовом произведении (забытое условие ON в CROSS JOIN или неявный JOIN через запятую) результат взрывается экспоненциально.
• FULL OUTER JOIN работает медленно на больших таблицах: PostgreSQL не может использовать nested loop; чаще применяется hash join или merge join.
• Дублирование строк: если в правой таблице несколько записей совпадают с одной записью в левой, левая строка будет повторена — неожиданное поведение при агрегации.
• USING (column) вместо ON t1.col = t2.col удобно, но скрывает оригинальное имя таблицы — SELECT * вернёт колонку только один раз.
• NULL != NULL: условие ON t1.col = t2.col не совпадёт, если обе колонки NULL; используйте IS NOT DISTINCT FROM для сравнения с учётом NULL.

• Отвечать про inner/left/right/full outer join как про абстрактный SQL без учета MVCC, WAL, planner или конкретной версии PostgreSQL.
• Переносить поведение MySQL или ClickHouse на PostgreSQL без проверки документации.
• Не связывать механизм с последствиями для индексов, транзакций, блокировок или эксплуатации.
• Показывать синтаксис, который не поддерживается PostgreSQL.

• Кандидат объясняет inner/left/right/full outer join через реальный механизм PostgreSQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.