Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Язык SQL — очень мощное и гибкое средство, позволяющее работа с реляционными базами данных. Одной из самых интересных возможностей языка SQL является возможность объединения двух и более таблиц в одну при помощи команды SELECT и ключевого слова JOIN. Хочу заметить, что объединение таблиц довольно дорогая операция и, чем больше строк в таблице, которые мы объединяем, тем эта операция обходится дороже, так как СУБД начинает вычислять так называемый предикат объединения для каждой строки. В SQL насчитывается пять видов объединения таблиц, но, к сожалению, в SQLite из этих пяти реализовано только три.

В этой записи мы рассмотрим общие принципы объединения таблиц в SQL и разберемся с особенностями реализации ключевого слова JOIN на примере баз данных под управлением SQLite3. А дальше мы рассмотрим каждый из видов объединения таблиц, реализованных в SQLite по отдельности, чтобы понять различия и особенности внутреннего и внешнего объединения таблиц. В процессе объяснения вы,как всегда, увидите примеры, демонстрирующие работы запроса SELECT с использованием JOIN.

Объединение таблиц в SQL запросах SELECT: LEFT JOIN, LEFT OUTER JOIN, INNER JOIN, CROSS JOIN. Разница между запросами JOIN.

На наш взгляд запросы JOIN – это самое интересное, что может делать команда SELECT. Мы рассмотрели объединение запросов в базах данных, которые реализуются при помощи UNION, а далее еще рассмотрим сравнение результатов выборки, но всё это не так интересно, как объединение таблиц в базах данных SQLite. Объединение таблиц реализуется при помощи ключевого слова JOIN.

Стандарт SQL делит объединение таблиц на три вида: внутреннее объединение таблиц (INNER JOIN), внешнее объединение таблиц (LEFT OUTER JOIN, RIGHT JOIN, FULL JOIN) и перекрестное объединение таблиц (CROSS JOIN). Принцип работы любого объединения схож, но результаты будут всегда или почти всегда отличаться.

Принцип работы запросов на объединения таблиц в SQL и реляционных базах данных заключается в том, что внутри одного SQL запроса SELECT выполняется два или более подзапроса (в зависимости от того, сколько мы хотим объединить таблиц), подзапросы разделяются между собой ключевым словом JOIN. У этого JOIN есть ограничение ON (во всяком случае официальная документация SQLite называет ON ограничением), которое называют предикатом объединения. Предикат объединения – это всегда какое-то условие, с помощью которого РСУБД определяет какие строки из двух таблиц ей нужно объединять. А вот с тем, как объединять строки, SQLite разбирается специальным модификаторам: INNER, LEFT OUTER или просто LEFT и CROSS.

Данное объяснение использует не совсем уместный в данном случае термин подзапрос, это сделано намеренно для того, чтобы объяснить принцип работы JOIN читателю, который еще не знаком с SQL. Термин подзапрос не совсем уместен, так как подзапрос SELECT всегда возвращает какую-то результирующую таблицу, а когда мы объединяем таблицы при помощи JOIN, чаще всего мы обращаемся к физическим таблицам базы данных (хотя никто не запрещает вам объединить существующую таблицу, с таблицей, которую вернет подзапрос SELECT).

Вообще, стандарт SQL выделяет гораздо больше модификаторов JOIN:

1. INNER JOIN – внутреннее объединение таблиц.
2. LEFT JOIN или LEFT OUTER JOIN – левое внешнее объединение таблиц.
3. RIGHT JOIN или RIGHT OUTER JOIN – правое внешнее объединение таблиц.
4. FULL JOIN – полное объединение таблиц.
5. CROSS JOIN – перекрестное объединение таблиц.

Но в базах данных SQLite есть только три вида объединения таблиц, о которых мы говорили ранее, их вполне достаточно для любых целей. Помните, мы рассматривали связи между таблицами и пытались нормализовать отношения? Когда наша база данных находится в первой нормальной форме мы и думать не думаем о том, как объединить таблицы в запросе SELECT, а вот когда отношение находится во второй нормальной форме или в третьей нормальной форме, у нас может появиться вопрос: как в одном запросе получить данные из двух или трех таблиц?

И этот вопрос хороший, так как он вызван ленью, той ленью, которая нас спасает от глупой и ненужной работы, которая заставляет что-то улучшать и совершенствовать. Как вы уже догадались, SELECT в комбинации с JOIN спасает нас от проблемы, описанной выше.

Ниже вы найдете небольшую шпаргалку по SQL запросу SELECT с использованием JOIN, в этой шпаргалке есть маленькие примеры и диаграммы, которые позволяют понять, как работает JOIN и как происходит объединение таблиц в базах данных.

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

Готовим таблицы для реализации примеров SQL запросов JOIN в базе данных SQLite

Давайте подготовим таблицы, чтобы в дальнейшем реализовать примеры объединения таблицы в базе данных при помощи SQL запросов JOIN. И понять в чем разница между: LEFT JOIN, LEFT OUTER JOIN, INNER JOIN и CROSS JOIN. Напомним, что в SQLite нет возможности объединить таблицы при помощи: RIGHT JOIN и FULL JOIN.

Итак, создадим таблицы в базе данных при помощи команды CREATE:

[php]

PRAGMA foreign_keys=on;

CREATE TABLE tracks (

id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

second INTEGER NOT NULL,

price REAL NOT NULL,

album_id INTEGER,

FOREIGN KEY (album_id) REFERENCES albums (id) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE albums (

id INTEGER PRIMARY KEY,

title TEXT NOT NULL,

artist_id INTEGER,

FOREIGN KEY (artist_id) REFERENCES artist (id) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE artist (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL

);

[/php]

Три таблицы, которые связаны связью один ко многим при помощи ограничения внешнего ключа FOREIGN KEY. Так же мы обеспечили целостность данных в базе данных при помощи всевозможных ограничений уровня таблицы и ограничений уровня столбца. У каждой таблицы есть индекс, как внутренний (столбец ROWID, который SQLite создает автоматически), так и обычный, созданный нами при помощи ограничения первичного ключа PRIMARY KEY.

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

[php]

— Добавляем данные в таблицу исполнителей

INSERT INTO artist (name)

VALUES (‘Вячеслав Бутусов’);

INSERT INTO artist (name)

VALUES (‘Сплин’);

INSERT INTO artist (name)

VALUES (‘Би-2’);

— Добавляем данные в таблицу альбомы

INSERT INTO albums (title, artist_id)

VALUES (‘25-й кадр’, 2);

INSERT INTO albums (title, artist_id)

VALUES (‘Биографика’, 1);

INSERT INTO albums (title, artist_id)

VALUES (‘Би-2’, 3);

— Добавим несколько альбомов,

— у которых нет ни исполнителей, ни трэков

INSERT INTO albums (title)

VALUES (‘Четвертый альбом’);

INSERT INTO albums (title)

VALUES (‘Пятый альбом’);

— добавляем данные в таблицу трэков

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Девушка по городу’, 193, 26.20, 2);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Песня идущего домой’, 170, 22.10, 2);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Полковнику никто не пишет’, 292, 32.15, 3);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Мой друг’, 291, 27.15, 3);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Моё сердце’, 249, 21.12, 1);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Линия жизни’, 180, 41.12, 1);

INSERT INTO tracks (title, second, price, album_id)

VALUES (‘Остаемся зимовать’, 218, 17.62, 1);

— Добавим пару трэков без исполнителей

INSERT INTO tracks (title, second, price)

VALUES (‘Мертвый город’, 180, 41.12);

INSERT INTO tracks (title, second, price)

VALUES (‘Звезда по имени Солнце’, 218, 17.62);

[/php]

Мы создали и наполнили таблицы, теперь мы можем приступать к объединению таблиц при помощи запросов SELECT с ключевыми словами JOIN во всех их проявлениях, доступных в SQL синтаксисе SQLite3.

Внутреннее объединение таблиц в базах данных SQLite: INNER JOIN в SQL

Начнем с самого простого и понятного объединения таблиц в SQL и базах данных SQLite3: с внутреннего объединения таблиц. Внутреннее объединение таблиц реализуется в SQL при помощи ключевой фразы INNER JOIN. Важной особенностью внутреннего объединения таблиц в SQL запросах SELECT является то, что уточняющая фраза INNER JOIN работает симметрично, а это значит, что не имеет никакого значения, какая таблица будет использована слева от INNER JOIN, а какая справа.

Когда вы хотите сделать внутреннее объединение таблиц в базах данных SQLite, то можете опускать ключевое слово INNER, так как конструкция INNER JOIN – это объединение таблиц по умолчанию в SQLite3. INNER JOIN в SQLite работает довольно просто: SQL запрос SELECT сравнивает строки из левой таблицы со строками правой таблицы, после сравнения SQLite3 выполняет проверку условия соединения или, как еще говорят, вычисляется предикат соединения. Если это вычисление дает значение TRUE, то в результирующую таблицу будет добавлена новая строка, если результат FALSE, то строка в объединённую таблицу добавлена не будет.

В SQLite, как и во многих других СУБД можно использовать ключевое слово USING ключевое слово USING в запросах JOIN используется для перечисления списка столбцов, которые должны существовать в обеих таблицах, общий синтаксис USING в JOIN можно представить, как:

[php]

SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...\

[/php]

Объединение таблиц INNER JOIN – самый распространенный способ объединения. Давайте реализуем команду SELECT, которая будет использовать конструкцию INNER JOIN для объединения таблиц базы данных:

[php]

SELECT * FROM tracks INNER JOIN

Albums ON tracks.album_id = albums.id;

id      title   second  price   album_id        id      title   artist_id

1       Девушка по городу       193     26.2    2       2       Биографика

2       Песня идущего домой     170     22.1    2       2       Биографика

3       Полковнику никто не пишет       292     32.15   3       3       Би-2

4       Мой друг        291     27.15   3       3       Би-2    3

5       Моё сердце      249     21.12   1       1       25-й кадр       2

6       Линия жизни     180     41.12   1       1       25-й кадр       2

7       Остаемся зимовать       218     17.62   1       1       25-й кадр

[/php]

Таким образом мы сделали внутреннее объединение таблиц при помощи ключевой фразы INNER JOIN, мы объединили таблицу трэков и таблицу альбомов. В результирующей таблице нет трэков, у которых нет исполнителей и нет альбомов, в которых нет трэков, это очень важно и на это стоит обратить внимание. Но заметьте, что это не совсем удобно – выводить все столбцы объединенных таблиц в результирующую, давайте это исправим:

[php]

SELECT tracks.title, second, price, albums.title FROM tracks INNER JOIN

Albums ON tracks.album_id = albums.id;

title   second  price   title

Девушка по городу       193     26.2    Биографика

Песня идущего домой     170     22.1    Биографика

Полковнику никто не пишет       292     32.15   Би-2

Мой друг        291     27.15   Би-2

Моё сердце      249     21.12   25-й кадр

Линия жизни     180     41.12   25-й кадр

Остаемся зимовать       218     17.62   25-й кадр

[/php]

Такое внутреннее объединение таблиц выглядит более интересным, здесь нет суррогатных столбцов id. В общем, если вы объединяете таблицы конструкцией JOIN, то после SELECT вам необходимо перечислить имена столбцов из обеих таблиц, которые будут отображаться в результирующей таблице. Обратите внимание: если при объединение таблиц имена столбцов одинаковы, то следует использовать квалификатор: table_name.col_name.

Мы можем задавать различные условия выборки данных клаузулой WHERE в том случае, когда объединяем таблице предикатом JOIN:

[php]

SELECT tracks.title, second, price, albums.title FROM tracks INNER JOIN

Albums ON tracks.album_id = albums.id

WHERE artist_id = 2;

title   second  price   title

Моё сердце      249     21.12   25-й кадр

Линия жизни     180     41.12   25-й кадр

Остаемся зимовать       218     17.62   25-й кадр

[/php]

В данном случае мы получили объединенные таблицы при помощи INNER JOIN, выбрали только те столбцы, которые хотели увидеть, плюс сделали фильтрацию данных, оставив только песни группы Сплин. Так работает конструкция INNER JOIN в SQL и базах данных SQLite3.

Подведем итог: внутреннее объединение таблиц INNER JOIN в результате дает таблицу, состоящую из строк объединяемых таблиц, для которых результат выполнения предиката объединения ON равен TRUE. Другими словами: в результирующей таблицы будут только те строки из левой таблицы, для которых есть связанные строки из правой. Работу INNER JOIN можно продемонстрировать в виде диаграммы.

Круг T1 – это таблица слева от INNER JOIN, круг T2 – это таблица справа от INNER JOIN, закрашенная область – это результирующая таблица, которую вернет команда SELECT, выполняющая операцию внутреннего объединения таблиц.

Левое объединение таблиц в базах данных SQLite3: LEFT JOIN и LEFT OUTER JOIN в SQL.

Мы рассмотрели внутреннее объединение таблиц в базах данных SQLite, теперь давайте перейдем к рассмотрению внешнего объединения, и разберемся в чем разница между внутренним объединение таблиц и внешним. В SQLite есть только один вид внешнего объединения таблиц: LEFT JOIN или LEFT OUTER JOIN.

Стандарт SQL на самом деле определяет три типа внешнего объединения: LEFT JOIN, RIGHT JOIN и FULL JOIN, но в SQLite3 есть только LEFT OUTER JOIN. Поговорим про левое объединение таблиц, хотя более полно это звучит так: левое внешнее объединение таблиц. Внешнее объединение таблиц работает точно так же, как и внутреннее объединение таблиц, но есть разница в выводе строк после проверки предиката соединения. Давайте посмотрим пример LEFT OUTER JOIN и заодно поймем отличие от INNER JOIN, а также особенность внешнего объединения таблиц:

[php]

SELECT tracks.title, second, price, albums.title FROM tracks LEFT OUTER JOIN

Albums ON tracks.album_id = albums.id;

title   second  price   title

Девушка по городу       193     26.2    Биографика

Песня идущего домой     170     22.1    Биографика

Полковнику никто не пишет       292     32.15   Би-2

Мой друг        291     27.15   Би-2

Моё сердце      249     21.12   25-й кадр

Линия жизни     180     41.12   25-й кадр

Остаемся зимовать       218     17.62   25-й кадр

Мертвый город   180     41.12

Звезда по имени Солнце  218     17.62

[/php]

Обратите внимание на результаты работы LEFT OUTER JOIN: в результирующей таблицы есть все строки из таблицы tracks. Давайте изменим запрос SELECT так, чтобы таблица albums была слева:

[php]

SELECT tracks.title, second, price, albums.title FROM albums LEFT OUTER JOIN

tracks ON albums.id = tracks.album_id;

title   second  price   title

Моё сердце      249     21.12   25-й кадр

Остаемся зимовать       218     17.62   25-й кадр

Линия жизни     180     41.12   25-й кадр

Песня идущего домой     170     22.1    Биографика

Девушка по городу       193     26.2    Биографика

Мой друг        291     27.15   Би-2

Полковнику никто не пишет       292     32.15   Би-2

Пятый альбом

Четвертый альбом

[/php]

Этими двумя примерами мы продемонстрировали разницу между внутренним объединением таблицы и внешним объединение: разницу между INNER JOIN и LEFT JOIN. В обоих случая SQLite вычисляет предикат объединения, который указан после ключевого слова ON, но разница в том, что при внешнем объединение LEFT OUTER JOIN в результирующей таблицы окажутся все строки левой таблицы, а к ним, если существуют, добавятся строки правой таблицы. Объяснение долгое, но понятное и написано простыми словами.

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

Круг T1 – это таблица слева от LEFT OUTER JOIN, круг T2 – это таблица справа от LEFT OUTER JOIN. Результирующая таблица на рисунке закрашена. По рисунку видно, что в результирующей таблицы будут все строки левой таблицы, а к ним будут добавлены значения из правой таблицы, если таковые существуют.

Стоит заметить, что LEFT JOIN работает несимметрично и для него имеет значение, какая из таблиц будет указана первой, так как в результирующей таблицы будут находиться все строки из первой таблицы. Это еще одно отличие LEFT JOIN от INNER JOIN. Для INNER JOIN порядок таблиц в SQL запросе не имеет значения.

Итак, мы разобрались с внешним объединением таблиц в SQL и подробно рассмотрели, как работает LEFT OUTER JOIN в базах данных SQLite3, также мы выяснили разницу между INNER JOIN и LEFT JOIN.

Перекрёстное объединение таблиц в SQL: CROSS JOIN в базах данных SQLite

Мы рассмотрели внутреннее объединение таблиц, реализуемое с помощью INNER JOIN, мы рассмотрели внешнее объединение таблиц, реализуемое в базах данных SQLite при помощи LEFT JOIN, теперь перейдем к перекрестному объединению таблиц, которое реализуется при помощи ключевой фразы CROSS JOIN.

Перекрестное объединение таблиц или CROSS JOIN является симметричной операцией, как и INNER JOIN, а это означает, что порядок написания таблиц в запросе не имеет значения, результирующая таблица будет одинакова. Перекрестное объединение таблиц в математике представляет собой декартово произведение, а это означает, что в результате выполнения операции CROSS JOIN SQLite будет соединять каждую строку первой таблицы с каждой строкой второй таблицы и в результате мы получим таблицу, в которой будут все сочетания строк, которые возможны.

К сожалению, продемонстрировать работу CROSS JOIN на диаграмме невозможно просто физически. Поэтому будем разбираться с тем, как работает перекрестное объединение таблиц в SQL в целом и в базах данных SQLite в частности на примере. Попробуем написать предложение SELECT, использующее ключевую фразу CROSS JOIN для объединения таблиц:

[php]

SELECT * FROM tracks

CROSS JOIN albums;

[/php]

Здесь мы не будем приводить результаты работы CROSS JOIN, так как считаем не лучшей затей заставлять вас просматривать 45 строк результирующей таблицы. Но нам не обязательно смотреть на значения всех столбцов результирующей таблицы, их мы можем ограничить (на самом деле мы можем ограничить количество строк выборки при помощи LIMIT, сделать группировку выборки при помощи GROUP BY, упорядочить выборку при помощи ORDER BY, всё это реализуется так же, как и в простых запросах на выборку данных) и указать только нужные столбцы, давайте сделаем такой CROSS JOIN:

[php]

SELECT tracks.title, albums.title FROM tracks

CROSS JOIN albums;

[/php]

Надеемся, что вы разобрались с тем, как работает CROSS JOIN в базах данных SQLite и у вас не возникнет трудностей с тем, чтобы написать SQL запрос, делающий перекрёстное объединение таблиц в базе данных.

Естественное объединение таблиц в SQL: NATURAL JOIN в SQLite

Вы можете использовать для всех запросов на объединение таблиц ключевое слово NATURAL: NATURAL LEFT JOIN, NATURAL INNER JOIN и NATURAL CROSS JOIN. Принцип действия NATURAL JOIN очень похож на принцип действия ограничения USING с той лишь разницей, что NATURAL JOIN автоматически осуществляет проверку на равенство между значениями каждого столбца, которые есть в обеих таблицах:

[php]

SELECT ... FROM table1 NATURAL JOIN table2...

[/php]

Примерно так можно описать общий синтаксис использования NATURAL JOIN в SQL и базах данных SQLite.

Примеры использования NATURAL JOIN в базах данных SQLite:

[php]

SELECT tracks.title, second, price, albums.title FROM tracks NATURAL LEFT JOIN Albums;

SELECT tracks.title, second, price, albums.title FROM tracks INNER JOIN Albums;

[/php]

Вся разница в том, что вам не следует вычислять значения предикатов ON и USING, когда вы используете NATURAL JOIN, если же всё-таки вы используете NATURAL JOIN вместе с ON или USING, то получите ошибку: Error: a NATURAL join may not have an ON or USING clause.

Объединение трех и более таблиц в SQL и базах данных SQLite

Операция JOIN очень мощная, но довольно-таки ресурсоемкая штука, без объединения таблиц нам бы не было смысла нормализовывать отношения до второй нормальной формы или до третьей нормальной (как бы мы получали сводные данные из связанных таблиц, если бы не было JOIN?), конечно, мы немного утрируем, но операции объединения таблиц очень облегчают нам работу с базами данных.

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

[php]

SELECT tracks.title, albums.title, artist.name  FROM tracks INNER JOIN

Albums ON tracks.album_id = albums.id

INNER JOIN artist ON albums.artist_id = artist.id;

title   title   name

Девушка по городу       Биографика      Вячеслав Бутусов

Песня идущего домой     Биографика      Вячеслав Бутусов

Полковнику никто не пишет       Би-2    Би-2

Мой друг        Би-2    Би-2

Моё сердце      25-й кадр       Сплин

Линия жизни     25-й кадр       Сплин

Остаемся зимовать       25-й кадр       Сплин

[/php]

А теперь пример объединения трех и более SQL таблиц для LEFT OUTER JOIN в базе данных SQLite3:

[php]

SELECT tracks.title, albums.title, artist.name  FROM tracks LEFT JOIN

Albums ON tracks.album_id = albums.id

LEFT JOIN artist ON albums.artist_id = artist.id;

title   title   name

Девушка по городу       Биографика      Вячеслав Бутусов

Песня идущего домой     Биографика      Вячеслав Бутусов

Полковнику никто не пишет       Би-2    Би-2

Мой друг        Би-2    Би-2

Моё сердце      25-й кадр       Сплин

Линия жизни     25-й кадр       Сплин

Остаемся зимовать       25-й кадр       Сплин

Мертвый город

Звезда по имени Солнце

[/php]

Для CROSS JOIN мы пример писать не будем, так как в результате появится таблица с очень большим количеством строк, которую мы всё равно здесь не покажем. Добавим, что можно комбинировать вместе INNER JOIN, LEFTT JOIN и CROSS JOIN это обусловлено тем, что все действия по объединению нескольких таблиц делаются библиотекой SQLite3 по порядку (можно представить, что JOIN – это операторы сложения).

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

Подведем общий итог: мы разобрались, как составлять SQL запросы SELECT, которые позволяют нам объединять две и более таблицы в одну. В языке SQL это делается при помощи ключевого слова JOIN, в том числе и в СУБД SQLite. SQLite дает возможность объединять таблицы тремя разными способами: INNER JOIN – внутреннее объединение таблиц, CROSS JOIN – перекрестное объединение таблиц и LEFT OUTER JOIN – внешнее объединение таблиц.