LEFT JOIN и строки без пары

INNER JOIN молча выбрасывает строки без пары. Но часто интересны именно они: «покупатели, которые ни разу не заказывали», «группы без куратора», «студенты, ещё не получившие оценок».

LEFT JOIN

Остаются все строки левой таблицы. Если пары в правой таблице нет — её столбцы будут NULL.

SELECT g.name, t.last_name AS curator
FROM study_groups g
LEFT JOIN teachers t ON t.id = g.curator_id
ORDER BY t.last_name NULLS FIRST, g.name;

У групп без куратора curator — NULL.

Поиск строк без пары

Самый полезный приём: LEFT JOIN + WHERE правая.id IS NULL.

SELECT c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Это покупатели, которые ни разу ничего не заказали.

RIGHT JOIN и FULL JOIN

• RIGHT JOIN — зеркальное отражение LEFT JOIN: полностью остаётся правая таблица. Обычно понятнее поменять таблицы местами и написать LEFT JOIN.
• FULL JOIN — остаются строки без пары с обеих сторон.

PostgreSQL поддерживает FULL OUTER JOIN.

Осторожно: условие в WHERE или в ON?

Если в LEFT JOIN условие для правой таблицы написать в WHERE, строки без пары (NULL) условие не пройдут — и LEFT JOIN превратится в обычный JOIN. Условие на правую таблицу должно стоять в ON:

SELECT c.first_name, COUNT(o.id) AS cancelled
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
GROUP BY c.id, c.first_name
ORDER BY cancelled DESC
LIMIT 5;

COUNT(o.id) не считает NULL, поэтому у покупателя без отменённых заказов будет 0.

Запомните

• LEFT JOIN — левая таблица остаётся полностью, у строк без пары правая сторона NULL.
• Строки без пары: LEFT JOIN … WHERE правая.id IS NULL.
• Условие на правую таблицу — в ON, иначе LEFT JOIN перестанет работать.