re:re:actable
Урок 033 минуты чтения

Чем отличаются INNER JOIN и LEFT JOIN

На этой странице
Кратко

INNER JOIN возвращает только строки, для которых нашлась пара; LEFT JOIN — все строки левой таблицы, а для строк без пары столбцы правой заполняет NULL. Ключевой нюанс — где стоит условие: условие в ON у LEFT JOIN определяет, какие правые строки присоединить, а условие в WHERE фильтрует уже готовый результат. Поэтому WHERE right.col = … отбрасывает строки с NULL и превращает LEFT JOIN в INNER JOIN. Для «записей без пары» используют LEFT JOIN … WHERE right.id IS NULL или NOT EXISTS.

Вопрос

Чем отличаются INNER JOIN и LEFT JOIN и как правильно ставить условия?

Разница на примере

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

ON или WHERE

-- задача: все пользователи и их ОПЛАЧЕННЫЕ заказы
 
-- ✗ пользователи без оплаченных заказов пропали
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';
 
-- ✓ условие на правую таблицу — в ON
SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid';
Где условиеДля INNER JOINДля LEFT JOIN
На правую таблицу в ONТо же, что WHEREОграничивает, что присоединить; левые строки сохраняются
На правую таблицу в WHEREТо же, что ONОтбрасывает строки с NULL → фактически INNER
На левую таблицу в WHEREФильтрФильтр (правильное место)
На левую таблицу в ONФильтрНе фильтрует левые строки — они останутся с NULL справа

Строки без пары

-- пользователи без заказов
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;            -- проверяем NOT NULL-столбец правой таблицы (PK)
 
-- то же, обычно понятнее
SELECT u.*
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Подробнее — semi join и anti join.

Цепочка LEFT JOIN

SELECT u.name, o.id, p.title
FROM users u
LEFT JOIN orders o      ON o.user_id = u.id
JOIN order_items oi     ON oi.order_id = o.id     -- ✗ INNER после LEFT убивает пользователей без заказов
LEFT JOIN products p    ON p.id = oi.product_id;

После LEFT JOIN следующие соединения с «опциональной» частью тоже должны быть LEFT (или их группируют в скобки/подзапрос).

COALESCE для отсутствующих значений

SELECT u.name, COALESCE(sum(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Частые ошибки

  • Фильтр по правой таблице в WHERE после LEFT JOIN.
  • INNER JOIN после LEFT JOIN в цепочке.
  • WHERE o.some_nullable_col IS NULL для поиска «без пары» — путает отсутствие пары с NULL в данных.
  • count(*) после LEFT JOIN считает и строки без пары (нужен count(o.id)).

Что важно помнить

  • LEFT JOIN сохраняет все левые строки.
  • Условие на правую таблицу — в ON, на левую — в WHERE.
  • «Без пары» — IS NULL по PK правой таблицы или NOT EXISTS.

Мини-шпаргалка

  • JOIN — только пары.
  • LEFT JOIN ... ON ... AND right.cond.
  • WHERE right.id IS NULL — anti join.
  • count(right.id), COALESCE(sum(...), 0).

Документация

PostgreSQL: Joined Tables.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам