База SQLite и основы SQL. Часть 6 (объединение JOIN)
13-11-2021Время чтения ~ 5 мин.SQL 2147
Объединение таблиц — одна из самых крутых фишек SQL. Чтобы использовать их максимально эффективно нужно заранее продумывать структуру базы. Пример с таблицами t1, r1 и q1 показывает основной принцип, но на практике чаще мы сталкиваемся с немного другой задачей, где таблица хранит лишь ссылку на запись в другой таблице.
Первый такой случай мы уже рассматривали — пересечение. Когда есть справочная таблица, например status. Возникает задача вывести данные из родительской таблицы, но с условием из справочной.
Пусть у нас будет таблица записей для сайта. В ней будет поле, где хранится номер статуса публикации. Сами статусы публикации пусть хранятся в отдельной таблице.
-- таблица записей
CREATE TABLE pages (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
title TEXT,
content TEXT DEFAULT '',
status_id INTEGER NOT NULL
);
-- таблица статусов
CREATE TABLE status (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name TEXT
);
-- добавим данные в статусы
INSERT INTO status (name) VALUES('publish');
INSERT INTO status (name) VALUES('draft');
-- добавим записи
INSERT INTO pages (title, status_id) VALUES ('Первая', 1);
INSERT INTO pages (title, status_id) VALUES ('Вторая', 2);
INSERT INTO pages (title, status_id) VALUES ('Третья', 1);
Проверим что получилось:
SELECT * FROM pages; id title content status_id ------------------------------- 1 Первая 1 2 Вторая 2 SELECT * FROM status; id name -------- 1 publish 2 draft
Теперь составим запрос, который выводит записи, которые были опубликованы:
SELECT * FROM pages AS p, status AS s WHERE p.status_id = s.id AND s.name = 'publish'; id title content status_id id:1 name ------------------------------------------- 1 Первая 1 1 publish 3 Третья 1 1 publish
Работает этот запрос точно также, как и рассмотренные ранее. Вначале происходит внутреннее соединение таблиц через FROM, а потом по этой таблице проверяются условия WHERE. Проверить это можно с помощью запроса:
SELECT * FROM pages, status;
Мы получим объединённую таблицу с 6 строчками (3*2).
Такой подход, когда родительская таблица хранит ссылки на справочные, достаточно типовой и часто будет встречаться в практике.
Использование INNER JOIN
Объединение таблиц через FROM и WHERE — один из самых популярных способов. Сейчас он считается устаревшим, поскольку в SQL есть специальные команды для объединения таблиц. Одна из них INNER JOIN (внутреннее соединение), которая выполняет ровно тоже самое.
SELECT * FROM pages AS p INNER JOIN status AS s ON p.status_id = s.id AND s.name = 'publish';
Вообще многие боятся команд JOIN, поскольку их синтаксис немного выбивается из привычного, а также потому что есть несколько команд JOIN, которые работают по разному.
Чтобы понять этот запрос, представьте себе что две таблицы располагаются рядом. Левая таблица — pages, а правая — status. Левая таблица — вроде как главная, то есть мы присоединяем к ней те, что справа. Таким образом начальная часть запроса SELECT * FROM pages AS p принципиально ничем не отличается от любого SQL-запроса для одной таблицы.
В запросе после INNER JOIN указывается вторая таблица — это похоже на обычный FROM. А дальше ключевое слово ON, которое по смыслу повторяет WHERE — здесь описываются условия объединения. И здесь же мы можем указать дополнительные условия для второй таблицы.
Если же условий много, то в INNER JOIN указывается только условие объединения, а дополнительные условия фильтрации в отдельном WHERE.
SELECT * FROM pages AS p INNER JOIN status AS s ON p.status_id = s.id WHERE s.name = 'publish';
Такой запрос является более логичным и более «правильным». Разберём как он работает.
Первая итерация — получение всех записей pages от FROM.
Вторая итерация — это объединение таблиц через INNER JOIN с условием ON:
SELECT * FROM pages AS p INNER JOIN status AS s ON p.status_id = s.id; id title content status_id id:1 name ------------------------------------------ 1 Первая 1 1 publish 2 Вторая 2 2 draft 3 Третья 1 1 publish
И дальше мы применяем условие фильтрации WHERE, где отсеиваем из этой таблицы, всё кроме publish.
Если нужно объединить три и более таблицы, то они также указываются последовательно. Вот пример с нашими тестовыми таблицами:
SELECT t.id AS id, t.name AS T, r.name AS R, q.name AS Q FROM t1 AS t INNER JOIN r1 AS r ON t.id = r.id INNER JOIN q1 AS q ON t.id = q.id; id T R Q ------------- 1 T1 R1 Q1 2 T2 R2 Q2 3 T3 R3 Q3
Команда INNER JOIN выполняет операцию пересечения. Здесь происходит объединение таблиц, когда поля совпадают (их значения).
Стоит отметить, что слово INNER можно опустить. Поэтому последний запрос будет эквивалентен этому:
SELECT t.id AS id, t.name AS T, r.name AS R, q.name AS Q FROM t1 AS t JOIN r1 AS r ON t.id = r.id JOIN q1 AS q ON t.id = q.id;
LEFT OUTER JOIN — левое внешнее соединение
Операция LEFT OUTER JOIN используется для случаев, когда в итоговую таблицу нужно добавить все записи из левой таблицы. Просто заменим последний запрос на LEFT OUTER JOIN:
SELECT t.id AS id, t.name AS T, r.name AS R, q.name AS Q FROM t1 AS t LEFT OUTER JOIN r1 AS r ON t.id = r.id LEFT OUTER JOIN q1 AS q ON t.id = q.id; id T R Q --------------- 1 T1 R1 Q1 2 T2 R2 Q2 3 T3 R3 Q3 4 T4 NULL NULL 5 T5 NULL NULL
Изменился только алгоритм объединения — мы получили всю выборку левой таблицы, а правые добавились только те, что есть. Там, где данных нет, поле оказалось отмечено как NULL (пусто).
Именно поэтому такое объединение часто называют левым. При этом слово OUTER можно опустить, поэтому чаще встречается такая запись:
SELECT t.id AS id, t.name AS T, r.name AS R, q.name AS Q FROM t1 AS t LEFT JOIN r1 AS r ON t.id = r.id LEFT JOIN q1 AS q ON t.id = q.id;
Объединение LEFT JOIN используется для случаев, когда нужно получить все записи из главной таблицы, а правые таблицы лишь расширяют её на свои поля.
SQLite поддерживает «синтаксический сахар» в виде команды USING. Она (редко) применяется для случаев, когда имена полей в таблицах совпадают и вместо условия ON t.id = r.id можно просто указать имя поля:
SELECT t.id AS id, t.name AS T, r.name AS R FROM t1 AS t LEFT JOIN r1 AS r USING (id);
Команда USING может использоваться и с INNER JOIN. На мой взгляд использование USING не совсем оправдано, поскольку выполняет условие ON, только скрытно. К тому же если имена полей различаются, то всё равно придётся использовать ON.
Объединение CROSS JOIN
Ещё одним вариантом объединения таблиц в SQLite будет CROSS JOIN — его называют декартовым произведением. Здесь происходит комбинирование строк из первой таблицы с каждой строкой из второй таблицы.
SELECT * FROM t1 CROSS JOIN r1; id name id:1 name:1 ----------------------- 1 T1 1 R1 1 T1 2 R2 1 T1 3 R3 2 T2 1 R1 2 T2 2 R2 2 T2 3 R3 и т.д. 15 строк
Это мы уже видели раньше, когда указывали во FROM две таблицы. В данном случае CROSS JOIN скорее является «новым синтаксисом». Стоит отметить, что у CROSS JOIN нельзя указывать условия объединения. Поэтому отфильтровать результат можно только с помощью WHERE:
SELECT * FROM t1 CROSS JOIN r1 WHERE t1.id = 1; id name id:1 name:1 ----------------------- 1 T1 1 R1 1 T1 2 R2 1 T1 3 R3
Итого
С объединением таблиц «плавают» многие разработчики. Чтобы разобраться в этом вопросе, я советую разбирать работу запроса по итерациям. Тогда будет понятно что получается на каждом этапе и это даст понимание всего процесса.