Объединение таблиц SQL с JOIN на примерах

Из таблицы pages мы можем узнать идентификатор темы статьи, но не её название. Чтобы добавить названия тем, требуется подключить вторую таблицу.

Как создать единую таблицу, в которой каждая страница будет связана с названием своей темы? Для этого нужно отобразить два столбца: title из таблицы pages и name из таблицы sections.

При этом важно сопоставить данные по идентификатору темы, который в одной таблице выступает в качестве внешнего ключа, а в другой — первичного. Например, если у страницы тема указана как 1, то нужно выбрать строку из sections, где _id равен 1. Затем из этой строки извлекается name.

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

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.

Пропуск части 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 можно исключить полностью, просто указав таблицы через запятую (применимо и для 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() для подсчёта записей в каждой группе.

Хотите получить данные только по первой теме? Использование WHERE приведет к ошибке из-за его выполнения до агрегации:

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 здесь нужно использовать 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. Но существуют и левый внешней (LEFT JOIN) и правый внешней JOIN (RIGHT JOIN).

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

Если необходимо отобразить темы, на которые нет статей, можно сделать одну из таблиц основной. Объединённая таблица будет содержать все соответствия, плюс все строки "главной" таблицы, которые не имели соответствий. Строки основной таблицы всегда будут присутствовать в результате.

SQLite использует только 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  

Также подсчитывается количество не NULL значений в столбце, переданном в count(). Без аргумента результат обманчив: напротив Algorithm было бы 1 из-за одной строки где тема — Algorithm.

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

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