JOIN – соединение таблиц

При выборке данных из таблицы pages мы можем увидеть номер темы, к которой относится та или иная страницы, но не название темы. Чтобы увидеть названия тем, надо вывести вторую таблицу.

Как получить сводную таблицу, в которой для каждой страницы будет указано название ее темы? Фактически нам надо вывести два столбца. Столбец title из таблицы pages и столбец name из таблицы sections.

При этом должно быть выполнено сопоставление по номеру темы, который в одной таблице является внешним ключом, а в другой – первичным. Так, если в записи таблицы pages в поле theme указан внешний ключ 1, то из таблицы sections должна выбираться запись, чье значение поля первичного ключа _id равно 1. Далее из этой записи берется значение поля name.

В SQL для соединения данных из разных таблиц используется оператор JOIN. В случае с нашим примером запрос будет выглядеть так:

sqlite> SELECT pages.title, 
   ...> sections.name AS theme
   ...> FROM pages JOIN sections
   ...> ON pages.theme == sections._id;

Подвыражение AS theme можно опустить. Тогда в качестве заголовка столбца будет указано его оригинальное имя – name.

После SELECT указываются столбцы, которые необходимо вывести. Перед именем столбца пишется имя таблицы. Указывать таблицу не обязательно, если имя столбца уникальное:

sqlite> SELECT title, name 
   ...> FROM pages JOIN sections
   ...> ON theme = sections._id;

Здесь имя таблицы используется только с _id, так как столбец с таким именем есть в обоих таблицах.

Если после SELECT будет стоять звездочка, будут выведены все столбцы из обоих таблиц.

После FROM указываются обе сводимые таблицы через JOIN. В данном случае неважно, какую указывать до JOIN, какую после.

После ключевого слова ON записывается условие сведения. Условие сообщает, как соединять строки разных таблиц. В данном случае каждая запись из таблицы pages дополняется полями той записи из таблицы sections, чье поле _id содержит такое же значение, какое содержит поле theme таблицы pages.

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

Однако если часть ON заменить на WHERE с тем же условием, то соединение таблиц вернет нужный нам результат.

sqlite> SELECT pages.title, sections.name
   ...> FROM sections JOIN pages
   ...> WHERE pages.theme == sections._id;
title                name       
-------------------  -----------
What is Information  Information
Amount of Informati  Information
Binary System        Digital Sys
Boolean Lows         Boolean Alg

На самом деле здесь выполняется фильтрация результата предыдущего примера.

JOIN писать не обязательно. После FROM таблицы можно перечислить через запятую (это верно как при использовании WHERE, так и ON):

sqlite> SELECT pages.title, sections.name
   ...> FROM pages, sections
   ...> WHERE pages.theme == sections._id;

Можно комбинировать WHERE и JOIN ON. Например, мы хотим вывести страницы только второй и третьей тем:

sqlite> SELECT pages.title, sections.name
   ...> FROM pages JOIN sections
   ...> ON pages.theme == sections._id
   ...> WHERE pages.theme == 2 
   ...> OR pages.theme == 3;
title          name           
-------------  ---------------
Binary System  Digital Systems
Boolean Lows   Boolean Algebra

Соединение можно использовать совместно с группировкой. Узнаем, сколько в каждой теме статей:

sqlite> SELECT sections.name AS theme, 
   ...> count() AS qty_articles
   ...> FROM pages JOIN sections 
   ...> ON pages.theme == sections._id
   ...> GROUP BY sections.name
   ...> ORDER BY sections._id;
theme        qty_articles
-----------  ------------
Information  2           
Digital Sys  1           
Boolean Alg  1

В этом запросе сначала была получена сводная таблица, в которой была выполнена группировка по столбцу name и с помощью функции count() посчитано количество записей в каждой группе.

Допустим, мы хотим получить данные только по первой теме:

sqlite> SELECT sections.name AS theme, 
   ...> count() AS qty_articles
   ...> FROM pages JOIN sections 
   ...> ON pages.theme == sections._id
   ...> GROUP BY sections.name
   ...> WHERE sections._id == 1;
Error: near "WHERE": syntax error

С условием WHERE запрос возвращает ошибку, потому что WHERE выполняется до агрегации и группировки.

В этих случаях вместо WHERE используется условие HAVING:

sqlite> SELECT sections.name AS theme, 
   ...> count() AS qty_articles
   ...> FROM pages JOIN sections 
   ...> ON pages.theme == sections._id
   ...> GROUP BY sections.name
   ...> HAVING sections._id == 2 
   ...> OR sections._id == 3;
theme            qty_articles
---------------  ------------
Boolean Algebra  1           
Digital Systems  1 

Существует несколько разновидностей оператора JOIN. Для простого JOIN (на самом деле это сокращение от INNER JOIN) условие (pages.theme = sections._id) работало так, что в результате запроса было только то, что однозначно соответствует условию. Однако JOIN может быть внешним левосторонним или внешним правосторонним.

В таблице sections есть четвертая тема, но страниц на эту тему в таблице pages нет. Когда мы соединяли таблицы и считали количество статей, четвертая тема просто отбрасывалась, потому что ее запись ни разу не удовлетворяла условию pages.theme = sections._id.

Как быть если надо увидеть, что в базе данных есть темы, в некоторых нет статей? В таком случае мы должны одну из таблиц сделать как бы основной, более главной. Сводная таблица будет содержать все записи, удовлетворяющие условию, плюс записи "главной" таблицы, для которой не нашлось соответствий во второй. То есть в результирующей таблице будут представлены все строки одной из таблиц.

Поскольку SQLite не поддерживает RIGHT JOIN, то "главную" таблицу следует указывать до LEFT JOIN. Так как нам важны все темы из таблицы sections, именно она будет первой.

sqlite> SELECT sections.name, 
   ...> count(pages.title)
   ...> FROM sections LEFT JOIN pages
   ...> ON pages.theme == sections._id
   ...> GROUP BY sections.name;
name        count(pages.title)
----------  ------------------
Algorithm   0                 
Boolean Al  1                 
Digital Sy  1                 
Informatio  2 

Обратите внимание, что также в функцию count() мы передаем имя столбца. В этом случае будут считаться количество его не NULL значений в каждой группе. Если аргумент не передать, то напротив Algorithm будет стоять число 1, потому что без группировки сводная таблица содержит одну запись, где тема – Algorithm.

Функция count() без аргумента просто посчитает количество строк в каждой группе. Передавая ей имя столбца, мы заставляем ее считать не NULL значения в этом столбце в каждой группе.

Кроме оператора JOIN в SQL есть оператор UNION. Если JOIN выполняет соединение по горизонтали, то есть добавляет столбцы одной таблице к столбцам другой, то UNION – это объединение таблиц по вертикали, когда строки одной таблицы добавляются к строкам другой. Так объединяют похожие таблицы, имеющие одинаковые или почти одинаковые схемы.