Исполняемые доки SQLite: LEFT JOIN

Q: Что делает LEFT JOIN в SQLite?

LEFT JOIN возвращает все строки из левой таблицы плюс совпадающие строки из правой, если такие есть. Если совпадения нет — строка из левой таблицы всё равно попадёт в результат, а столбцы правой будут заполнены значением NULL. LEFT OUTER JOIN — это то же самое, слово OUTER в SQLite опционально.

Q: Чем LEFT JOIN отличается от INNER JOIN в SQLite?

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

Q: Почему мой LEFT JOIN в SQLite ведёт себя как INNER JOIN?

Чаще всего виноват WHERE с условием по правой таблице, которое не учитывает NULL. Условия по правой таблице должны быть в ON, а не в WHERE — либо пишите WHERE right.col IS NULL, чтобы найти строки без пары. Условие вида WHERE right.col = 'x' молча выкидывает все несовпавшие строки, и от LEFT JOIN ничего не остаётся.

LEFT JOIN сохраняет всё, что слева

INNER JOIN возвращает только те строки, где совпадение есть с обеих сторон. Часто это именно то, что нужно — но не всегда. Бывает, что само отсутствие совпадения и есть ответ на вопрос: пользователи без заказов, товары, которые ни разу не продавались, посты без комментариев. Вот для таких случаев и нужен LEFT JOIN в SQLite.

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

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

У Клео нет заказов, но она всё равно попадает в результат — со значением NULL в столбце total. Замените LEFT JOIN на INNER JOIN, и Клео исчезнет совсем.

Как это устроено в голове

Читайте запрос сверху вниз и воспринимайте левую таблицу как опору. Каждая строка из users обязательно окажется в результате, что бы ни случилось. А LEFT JOIN для каждого пользователя задаёт вопрос: «есть ли подходящая строка в orders?»

Совпадение нашлось → к строке пользователя приклеиваются столбцы из совпавшей строки.
Совпадений несколько → на каждое совпадение получаем отдельную строку результата (у Ады два заказа, поэтому она появляется дважды).
Совпадений нет → получаем одну строку, где все столбцы из правой таблицы заполнены значением NULL.

Именно ради последнего случая LEFT JOIN и придумали. NULL здесь означает не «мы не знаем», а «справа нечего приклеивать».

LEFT OUTER JOIN — это та же самая операция. Ключевое слово OUTER в SQLite необязательное, и обычно его просто опускают.

Как найти строки без пары

Классический сценарий применения LEFT JOIN — найти строки в левой таблице, у которых нет пары справа. Фокус в том, чтобы фильтровать по столбцу из правой таблицы, который в реальных данных всегда NOT NULL (как правило, это первичный ключ), и после соединения проверить его на NULL:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

Возвращается только Клео. JOIN подтягивает данные о заказах там, где они есть, а условие WHERE o.id IS NULL оставляет только те строки, где подтянуть ничего не удалось. Иногда такой приём называют «анти-джойн».

ON или WHERE: коварная ловушка

Это самая частая ошибка при работе с LEFT JOIN, поэтому стоит на ней остановиться отдельно. Условия можно писать как в ON, так и в WHERE — но во внешних соединениях они ведут себя совершенно по-разному.

ON срабатывает во время соединения. Условия здесь решают, какие строки правой таблицы считаются совпадением.
WHERE срабатывает после того, как JOIN уже сформировал строки. Это фильтр уже готового результата.

Посмотрите, что произойдёт, если условие на правую таблицу поставить в WHERE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Неправильно: незаметно превращает LEFT JOIN в INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

У Клео нет ни одного заказа, поэтому в её строке o.status равен NULL, а сравнение NULL = 'shipped' не даёт истину — она вылетает из результата. У Бориса статус 'pending', его тоже отбрасывает. В итоге LEFT JOIN тихо превратился в INNER JOIN.

Решение — перенести условие в ON, чтобы оно фильтровало совпадения, а не строки результата:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

Теперь в результат попадают все пользователи. Ada видит свой отгруженный заказ; Boris получает NULL (его заказ в статусе ожидания не прошёл по условию совпадения); Cleo тоже NULL (заказов нет вовсе). Именно это нам и нужно, когда вопрос звучит так: «покажи всех пользователей и их отгруженные заказы, если такие есть».

Простое правило: условия по левой таблице можно смело писать в WHERE. А вот условия по правой таблице почти всегда должны жить в ON — если только вы намеренно не ищете строки без совпадений через IS NULL.

Подсчёт строк через LEFT JOIN

Типичная задача — посчитать количество связанных строк для каждой родительской записи, включая те, у которых их ноль. INNER JOIN такие нули просто выкинет. А вот связка LEFT JOIN плюс COUNT по столбцу из правой таблицы даёт корректный результат:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

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

Пара моментов, на которые стоит обратить внимание:

COUNT(o.id) считает только непустые строки из правой таблицы. У Клео получится 0, а не 1 — потому что COUNT игнорирует NULL. Если написать COUNT(*), у Клео будет 1 (строка-то есть, просто в ней одни NULL). В 99% случаев нужен именно COUNT(right.id).
COALESCE(SUM(o.total), 0) превращает NULL-сумму у Клео в 0. Без этого в её строке будет NULL вместо выручки — формально верно, но выглядит так себе.

LEFT JOIN с несколькими таблицами в SQLite

LEFT JOIN отлично объединяются в цепочку. Каждый следующий джойн берёт текущий результат и подсоединяет к нему ещё одну таблицу. Но запомните важное правило: как только какая-то колонка стала nullable из-за LEFT JOIN, все последующие таблицы, которые цепляются к ней, тоже нужно подключать через LEFT JOIN. Иначе очередной INNER JOIN молча выкинет те самые строки, которые вы и хотели сохранить.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

Возвращаются те же три пользователя. У Ады есть и заказ, и отправление. У Бориса есть заказ, но отправления нет — carrier равен NULL. У Клео нет заказа вовсе, поэтому и o.total, и s.carrier приходят как NULL. Цепочка из LEFT JOIN сохраняет каждого пользователя — неважно, на каком звене связи данные обрываются.

Когда стоит выбрать LEFT JOIN

LEFT JOIN в SQLite — ваш выбор, когда вопрос по сути про левую таблицу, а правая таблица лишь дополняет её информацией. Формулировки вроде «все пользователи и их заказы, если они есть» или «все товары и их последний отзыв» один в один ложатся на LEFT JOIN.

INNER JOIN берите, когда обе стороны одинаково обязательны: запрос «заказы вместе с данными пользователя» теряет смысл для заказа без пользователя, и здесь как раз нужна фильтрация, которую даёт inner join.

Если вы вдруг пишете LEFT JOIN ... WHERE right.col IS NOT NULL — на самом деле вам нужен INNER JOIN. А если пишете LEFT JOIN ... WHERE right.col IS NULL — это анти-join, и тут всё правильно.

Дальше: self-join

Иногда таблица, к которой нужно присоединиться, — это та же самая таблица, по которой вы уже делаете запрос. Сотрудники и их руководители, категории и их родители, пары пользователей из одного города. Это и есть self-join, ему посвящена следующая страница.

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

Что делает LEFT JOIN в SQLite?

LEFT JOIN возвращает все строки из левой таблицы плюс совпадающие строки из правой, если такие есть. Если совпадения нет — строка из левой таблицы всё равно попадёт в результат, а столбцы правой будут заполнены значением NULL. LEFT OUTER JOIN — это то же самое, слово OUTER в SQLite опционально.

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

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

Почему мой LEFT JOIN в SQLite ведёт себя как INNER JOIN?

Чаще всего виноват WHERE с условием по правой таблице, которое не учитывает NULL. Условия по правой таблице должны быть в ON, а не в WHERE — либо пишите WHERE right.col IS NULL, чтобы найти строки без пары. Условие вида WHERE right.col = 'x' молча выкидывает все несовпавшие строки, и от LEFT JOIN ничего не остаётся.

Связанные концепции

LEFT JOIN в SQLite: примеры и работа с NULL