В чём разница между INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL OUTER 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) тоже исключаются

Виды JOIN в PostgreSQL

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

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 или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.