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

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

INNER JOIN возвращает только те строки, для которых нашлось совпадение в обеих таблицах по условию из ON. Всё, что не совпало, отбрасывается. Кстати, в SQLite JOIN и INNER JOIN — это одно и то же, INNER подразумевается по умолчанию.

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

INNER JOIN оставит только сматченные строки. LEFT JOIN сохранит все строки из левой таблицы, а в колонках правой подставит NULL, если пары не нашлось. Логика выбора простая: если ненайденная пара означает «пропустить строку» — берём INNER JOIN, если «всё равно показать» — LEFT JOIN.

Q: Можно ли в SQLite соединить три таблицы через INNER JOIN?

Да, просто дописываем ещё один JOIN ... ON .... Каждый новый join прицепляет очередную таблицу к промежуточному результату. Жёсткого лимита нет, но после четырёх-пяти таблиц запрос становится трудно читать — в таких случаях обычно выручает CTE (WITH ...).

Q: Когда лучше использовать USING вместо ON?

USING (column) — это сокращение на случай, когда колонка для соединения называется одинаково в обеих таблицах. Запись короче, и в результате остаётся одна общая колонка вместо двух одинаковых. Если же имена столбцов разные или нужно более хитрое условие — пишем через ON.

Соединение таблиц склеивает две таблицы воедино

В реляционных базах данные специально разносят по разным таблицам: клиенты — в одной, заказы — в другой, товары — в третьей. Так каждый факт хранится ровно в одном месте. Но как только нужно ответить на реальный вопрос ("какой клиент что заказал?"), кусочки приходится собирать обратно. Именно этим и занимается соединение таблиц SQL.

INNER JOIN — основная рабочая лошадка. Он сопоставляет строки из двух таблиц там, где выполняется условие, а всё остальное просто отбрасывает.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

Три клиента, три заказа, но у Chen заказов нет — поэтому Chen в выборку не попадает. В этом и суть «inner»: остаются только строки, для которых нашлось совпадение.

Как это работает: сопоставление строк и фильтрация

INNER JOIN удобно читать так: берём каждую строку из первой таблицы, перебираем все строки из второй и оставляем пару только тогда, когда условие в ON истинно. По сути это огромное декартово произведение, к которому потом применяется фильтр. На практике SQLite, конечно, действует иначе (по возможности задействует индексы), но эта модель отлично подходит, чтобы предсказать результат запроса.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

Несколько привычек, которые стоит выработать:

Используйте псевдонимы таблиц (customers AS c), если упоминаете их больше одного раза. Это убирает визуальный шум.
Указывайте таблицу у столбца (c.name, o.total), когда колонка с таким именем может быть в обеих таблицах.
Порядок в ON o.customer_id = c.id не имеет значения — c.id = o.customer_id работает точно так же.

INNER JOIN или просто JOIN

В SQLite (и в стандарте SQL) JOIN сам по себе означает INNER JOIN. Ключевое слово INNER можно не писать.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Оба варианта дают один и тот же план запроса и одни и те же строки. Явное INNER JOIN — маленький, но приятный плюс к читаемости, особенно когда в одном запросе встречаются разные типы соединений: рядом с LEFT JOIN через пару строк сразу видно, что именно вы имели в виду.

ON или USING

Если столбцы для соединения называются одинаково в обеих таблицах, USING (column) короче, чем ON a.col = b.col:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) решает сразу две задачи: соединяет строки по равенству customer_id и схлопывает столбец, чтобы он встретился в результате один раз. Используйте этот вариант, когда имена колонок в обеих таблицах действительно совпадают. Если же имена разные (orders.customer_id = customers.id) или условие сложнее обычного равенства — берите ON.

INNER JOIN трёх таблиц в SQLite

Чтобы соединить три таблицы и больше, просто добавляйте новые блоки JOIN ... ON .... Каждый следующий JOIN присоединяет очередную таблицу к уже собранному промежуточному результату.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Блокнот', 2), (10, 'Ручка', 5), (11, 'Кружка', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

Читать удобно сверху вниз: покупатели связаны с заказами, заказы — с позициями. Каждая строка результата — это одна комбинация «покупатель — заказ — позиция». Всё, что не нашло пару хотя бы на одном шаге, выпадает из выборки. Это и есть правило INNER JOIN, применённое на каждом звене цепочки.

Фильтрация через WHERE

ON определяет, как соединять строки между собой. WHERE фильтрует уже полученный результат. Для inner join это, по сути, одно и то же: дополнительное условие можно положить как в ON, так и в WHERE — строки получатся одинаковые. Но по соглашению условия соединения держат в ON, а фильтры по строкам — в WHERE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

Читается это так: «соедини customers и orders, а потом оставь только клиентов из Великобритании с заказом дороже 20». Две роли — два разных условия. Когда через пару дней снова откроешь этот запрос, скажешь себе спасибо. (Как только дойдёт дело до LEFT JOIN, разница между ON и WHERE перестанет быть косметической — но об этом уже на следующей странице.)

Несколько условий в ON

В ON можно писать любое булево выражение, а не только одно равенство. Это пригодится, когда связь между таблицами идёт сразу по нескольким колонкам или когда нужно отфильтровать правую таблицу прямо на этапе соединения.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

Отменённый заказ исчезает, потому что второе условие не выполняется. Для inner join можно с тем же успехом написать WHERE o.status = 'paid' — результат будет тот же. Просто вариант с ON держит логику «что считать совпадением» рядом с самим соединением.

Типичные ошибки при соединении таблиц

Вот на чём чаще всего спотыкаются:

Забыли про ON. Конструкция FROM a INNER JOIN b без ON — это синтаксическая ошибка в SQLite. (А вот обычная запятая — FROM a, b — компилируется и даёт декартово произведение, чего вы почти наверняка не хотели.)
Неожиданные дубликаты. Если у клиента три заказа, его имя появится в результате три раза. Это не баг, а нормальное поведение join'а. Если нужна одна строка на клиента — агрегируйте через GROUP BY.
Пропавшие строки. Если клиент должен был оказаться в выдаче, но его там нет — значит, условие соединения не сработало. Проверяйте NULL в столбцах, по которым соединяете, или переходите на LEFT JOIN.
Неоднозначные имена столбцов. Запрос SELECT id FROM customers JOIN orders ON ... упадёт с ошибкой, потому что id есть в обеих таблицах. Уточняйте: c.id или o.id.

Дальше: LEFT JOIN

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

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

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

INNER JOIN возвращает только те строки, для которых нашлось совпадение в обеих таблицах по условию из ON. Всё, что не совпало, отбрасывается. Кстати, в SQLite JOIN и INNER JOIN — это одно и то же, INNER подразумевается по умолчанию.

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

INNER JOIN оставит только сматченные строки. LEFT JOIN сохранит все строки из левой таблицы, а в колонках правой подставит NULL, если пары не нашлось. Логика выбора простая: если ненайденная пара означает «пропустить строку» — берём INNER JOIN, если «всё равно показать» — LEFT JOIN.

Можно ли в SQLite соединить три таблицы через INNER JOIN?

Да, просто дописываем ещё один JOIN ... ON .... Каждый новый join прицепляет очередную таблицу к промежуточному результату. Жёсткого лимита нет, но после четырёх-пяти таблиц запрос становится трудно читать — в таких случаях обычно выручает CTE (WITH ...).

Когда лучше использовать USING вместо ON?

USING (column) — это сокращение на случай, когда колонка для соединения называется одинаково в обеих таблицах. Запись короче, и в результате остаётся одна общая колонка вместо двух одинаковых. Если же имена столбцов разные или нужно более хитрое условие — пишем через ON.

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

SQLite INNER JOIN: объединение таблиц по ключу