SQL LEFT JOIN: все строки левой таблицы

Урок 5.3 · Время чтения: ~7 мин · Задачи для практики ↓

LEFT JOIN в SQL возвращает каждую строку левой таблицы и совпадающие строки правой; где совпадения нет, столбцы правой таблицы равны NULL. В этом уроке вы изучите синтаксис LEFT JOIN, научитесь находить строки без совпадения и узнаете об ошибке в WHERE, которая незаметно превращает LEFT JOIN в INNER JOIN.

INNER JOIN отбрасывает строки без совпадения. Но часто нужны именно они: фильмы, которых нет в наличии, клиенты без заказов в этом месяце, товары, которые ни разу не продавались. LEFT JOIN их сохраняет.

Что возвращает LEFT JOIN

• Каждая строка левой таблицы (той, что после FROM) появляется в результате хотя бы один раз.
• Если у строки есть совпадения в правой таблице, она объединяется с каждым из них, как в INNER JOIN.
• Если совпадения нет, строка появляется один раз, а все столбцы правой таблицы равны NULL.

LEFT JOIN и LEFT OUTER JOIN - одно и то же; слово OUTER необязательно.

Базовый синтаксис

SELECT left_table.column1, right_table.column2
FROM left_table
LEFT JOIN right_table ON left_table.key_column = right_table.key_column;

Фильмы и их копии

В Sakila 1000 фильмов, но не все есть в наличии. С LEFT JOIN фильмы без копий остаются в результате:

SELECT f.film_id, f.title, i.inventory_id, i.store_id
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
ORDER BY i.inventory_id IS NULL DESC, f.title
LIMIT 10;

Результат: первыми идут фильмы без копий; у них inventory_id и store_id равны NULL.

С INNER JOIN тот же запрос потерял бы эти 42 фильма.

Поиск строк без совпадения

Чтобы найти только строки без совпадения, добавьте условие WHERE, проверяющее столбец правой таблицы на NULL. Проверяйте ключевой столбец правой таблицы: он равен NULL только тогда, когда совпадения не нашлось.

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
WHERE i.inventory_id IS NULL
ORDER BY f.title;

Результат: 42 фильма, которых нет ни в одном магазине.

Такой шаблон называется антисоединением (anti-join). Он отвечает на вопросы «чего не хватает». Ещё пример - копии, которые ни разу не брали в аренду:

SELECT i.inventory_id, f.title, i.store_id
FROM inventory AS i
INNER JOIN film AS f ON f.film_id = i.film_id
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE r.rental_id IS NULL;

Результат: одна копия, которую ни разу не брали в аренду.

Ловушка WHERE

Фильтр по правой таблице в WHERE удаляет строки, где столбцы этой таблицы равны NULL, и LEFT JOIN начинает вести себя как INNER JOIN. Сравните два запроса: оба должны вывести всех клиентов с их платежами начиная с 23 августа 2005 года.

-- Неверно: клиенты без таких платежей пропадают
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
WHERE p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

-- Верно: условие - часть соединения
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
                      AND p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

Результат: первый запрос возвращает только клиентов, которые платили в этот период. Второй сохраняет всех 599 клиентов; у тех, кто не платил в конце августа, amount и payment_date равны NULL.

Правило: условия на правую таблицу, которые должны ограничивать совпадения, пишутся в ON. Условия на левую таблицу, которые должны убирать строки из результата, пишутся в WHERE. Единственное условие на правую таблицу, которому место в WHERE, - IS NULL для антисоединения.

Порядок таблиц важен

В INNER JOIN перестановка таблиц не меняет результат. В LEFT JOIN меняет: таблица после FROM - та, все строки которой сохраняются. film LEFT JOIN inventory сохраняет все фильмы; inventory LEFT JOIN film сохраняет все копии, а у каждой копии есть фильм, поэтому фильмы без копий теряются.

Ключевые выводы:

• LEFT JOIN сохраняет каждую строку левой таблицы; строки без совпадения получают NULL в столбцах правой таблицы.
• LEFT JOIN и LEFT OUTER JOIN - одно и то же.
• LEFT JOIN ... WHERE right_key IS NULL находит строки без совпадения (антисоединение).
• Фильтр по правой таблице в WHERE превращает LEFT JOIN в INNER JOIN; переносите его в ON.
• Сохраняются строки таблицы, указанной после FROM.

Часто задаваемые вопросы

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

INNER JOIN возвращает только строки, у которых есть совпадение в обеих таблицах. LEFT JOIN возвращает ещё и строки левой таблицы без совпадения, заполняя столбцы правой таблицы значениями NULL.

LEFT JOIN и LEFT OUTER JOIN - одно и то же?

Да. OUTER необязательно, и обе формы возвращают одинаковый результат в любой СУБД.

Почему мой LEFT JOIN работает как INNER JOIN?

Скорее всего, в WHERE есть условие на столбец правой таблицы. Для строк без совпадения этот столбец равен NULL, условие не выполняется, и строки удаляются. Перенесите условие в ON.