SQLite LEFT JOIN: как работает левое соединение

LEFT JOIN (левое соединение) — это инструмент SQLite, позволяющий работать с несколькими связанными таблицами

Как и INNER JOIN, LEFT JOIN — опциональное предложение оператора SELECT, которое используется для извлечения данных из связанных таблиц

Предположим, у нас есть две таблицы: A и B

Таблица A содержит столбцы m и f

Таблица B содержит столбцы n и f

Чтобы выполнить объединение A и B с помощью предложения LEFT JOIN, используется следующий оператор:

SELECT a, b
FROM A
LEFT JOIN B ON A.f = B.f
WHERE search_condition;

Выражение A.f = B.f является условным выражением. Помимо оператора равенства (=), можно использовать другие операторы сравнения, такие как больше (>), меньше (<) и т. д

Оператор возвращает результирующий набор, который включает:

• строки таблицы A (левой таблицы), для которых есть соответствующие строки в таблице B;
• строки таблицы A и строки таблицы B, заполненные значениями NULL, если строка из таблицы A не имеет соответствующих строк в таблице B.

Все строки таблицы A включаются в результирующий набор, независимо от совпадений в таблице B

Если в операторе присутствует предложение WHERE, условие search_condition применяется после завершения сопоставления предложения LEFT JOIN

Визуальная логика LEFT JOIN

Все строки таблицы A включаются в результирующий набор

Поскольку вторая строка (a2, 2) не имеет соответствующей строки в таблице B, предложение LEFT JOIN создаёт фиктивную строку, заполненную значениями NULL

Диаграмма Венна наглядно показывает эту логику: левый круг (таблица A) входит в результат целиком, а из правого круга (таблица B) берётся только пересечение

LEFT OUTER JOIN и LEFT JOIN обозначают одно и то же, оба варианта равнозначны

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Примеры запросов SQLite LEFT JOIN

Для демонстрации я буду использовать таблицы artists и albums из учебной базы данных

Один альбом принадлежит одному исполнителю. Однако один исполнитель может иметь ноль или более альбомов

Для поиска исполнителей без альбомов с помощью LEFT JOIN мы выбираем исполнителей и соответствующие альбомы. Если у исполнителя нет альбомов, столбец AlbumId будет равен NULL

Чтобы отобразить таких исполнителей, есть два варианта:

• использовать предложение ORDER BY для вывода строк, у которых AlbumId имеет значение NULL, в начале списка;
• использовать предложение WHERE и оператор IS NULL для вывода только тех исполнителей, у которых нет альбомов.

Запрос с ORDER BY

Следующий оператор использует предложение LEFT JOIN вместе с предложением ORDER BY:

SELECT artists.ArtistId, AlbumId
FROM artists
LEFT JOIN albums ON albums.ArtistId = artists.ArtistId
ORDER BY AlbumId;

Строки, у которых AlbumId равен NULL, окажутся в начале результата — это исполнители без альбомов

Запрос с WHERE и IS NULL

Следующий оператор использует предложение LEFT JOIN вместе с предложением WHERE:

SELECT artists.ArtistId, AlbumId
FROM artists
LEFT JOIN albums ON albums.ArtistId = artists.ArtistId
WHERE AlbumId IS NULL;

Этот подход более наглядный: он возвращает исключительно тех исполнителей, у которых нет альбомов, минуя лишние строки

Типичные ошибки при использовании LEFT JOIN

На моём опыте с LEFT JOIN легко допустить несколько распространённых ошибок

Если условие для правой таблицы добавить в WHERE, LEFT JOIN фактически станет INNER JOIN, поскольку строки с NULL будут отфильтрованы. Чтобы сохранить все строки левой таблицы, условия следует указывать в ON

Путаница с NULL. Значение NULL в столбце правой таблицы не означает ошибку — это штатный результат LEFT JOIN, сигнализирующий об отсутствии совпадения. Мой совет — для проверки используйте IS NULL или IS NOT NULL, а не = NULL

Неправильный порядок таблиц. LEFT JOIN возвращает все строки именно левой таблицы (той, что стоит перед ключевым словом JOIN). Если нужно сохранить все строки другой таблицы, поменяйте их местами или используйте RIGHT JOIN

LEFT OUTER JOIN и LEFT JOIN — в чём отличие

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

Что такое LEFT OUTER JOIN — это другая команда? Нет, LEFT OUTER JOIN и LEFT JOIN — это одно и то же. Слово OUTER необязательно и не меняет поведение запроса

Как найти строки без совпадений в правой таблице? Используйте LEFT JOIN и добавьте в предложение WHERE условие WHERE правая_таблица.столбец IS NULL. Именно так находят, например, исполнителей без альбомов

Почему условие фильтрации лучше ставить в ON, а не в WHERE? Условие в ON применяется до объединения и не убирает строки с NULL из результата. Условие в WHERE применяется после объединения и может случайно отфильтровать строки левой таблицы, у которых нет совпадений

Можно ли объединять более двух таблиц через LEFT JOIN? Да. Можно последовательно добавлять несколько предложений LEFT JOIN в один запрос, каждый раз указывая условие соединения через ON