SQLite INNER JOIN: объединение данных из нескольких таблиц

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

Как таблицы связываются друг с другом

В реляционных базах данных таблица связывается с другой таблицей с помощью внешних ключей (foreign keys). Например, таблица треков может ссылаться на таблицу альбомов через столбец AlbumId — это и есть внешний ключ

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

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

Синтаксис предложения INNER JOIN

Предположим, у вас есть две таблицы: A и B

Таблица A содержит столбцы a1, a2 и f. Таблица B содержит столбцы b1, b2 и f. Таблица A связана с таблицей B через столбец внешнего ключа с именем f

Синтаксис предложения INNER JOIN выглядит следующим образом:

SELECT a1, a2, b1, b2
FROM A
INNER JOIN B on B.f = A.f;

Для каждой строки в таблице A предложение INNER JOIN сравнивает значение столбца f со значением столбца f в таблице B. Если значения совпадают, данные из столбцов a1, a2, b1, b2 объединяются и эта строка включается в результирующий набор

Таким образом, INNER JOIN возвращает только те строки таблицы A, для которых найдена соответствующая строка в таблице B, в отличие от LEFT JOIN, который возвращает все строки из таблицы A

Эта логика применяется и при объединении более двух таблиц

Рассмотрим конкретный пример. Только строки таблицы A: (a1,1), (a3,3), имеющие соответствующие строки в таблице B: (b1,1), (b2,3), включаются в результирующий набор. Строки без пары отбрасываются

Пример INNER JOIN для двух таблиц

Рассмотрим таблицы tracks и albums в учебной базе данных SQLite. Таблица tracks связана с таблицей albums через столбец AlbumId

В таблице tracks столбец AlbumId является внешним ключом. В таблице albums столбец AlbumId является первичным ключом

Чтобы запросить данные из обеих таблиц, используется следующий оператор:

SELECT trackid, name, title
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid;

Для каждой строки в таблице tracks SQLite сравнивает значение столбца albumid со значением столбца albumid в таблице albums. При совпадении данные из обеих таблиц объединяются

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

SELECT trackid, name, tracks.albumid AS album_id_tracks,
       albums.albumid AS album_id_albums, title
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid;

Здесь я использую псевдонимы (aliases) album_id_tracks и album_id_albums, чтобы различать одноимённые столбцы из разных таблиц — без псевдонимов SQLite вернул бы неоднозначный результат

Пример INNER JOIN для трёх таблиц

Рассмотрим следующие таблицы: tracks, albums и artists

Один трек принадлежит одному альбому, и один альбом содержит множество треков. Таблица tracks связана с таблицей albums через столбец albumid

Один альбом принадлежит одному исполнителю, и один исполнитель может иметь один или несколько альбомов. Таблица albums связана с таблицей artists через столбец artistid

Для запроса данных из всех трёх таблиц необходимо использовать два предложения INNER JOIN в операторе SELECT:

SELECT trackid, tracks.name AS track, albums.title AS album,
       artists.name AS artist
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid;

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

Фильтрация результатов с помощью WHERE

Предложение WHERE позволяет сузить результат до нужного подмножества строк. Например, чтобы получить треки и альбомы исполнителя с artistid = 10, используется следующий оператор:

SELECT trackid, tracks.name AS Track, albums.title AS Album,
       artists.name AS Artist
FROM tracks
INNER JOIN albums ON albums.albumid = tracks.albumid
INNER JOIN artists ON artists.artistid = albums.artistid
WHERE artists.artistid = 10;

WHERE применяется уже после того, как INNER JOIN сформировал объединённый набор строк — это важно понимать при составлении сложных запросов. На практике я часто использую именно такую комбинацию INNER JOIN + WHERE, когда нужно получить данные по конкретному исполнителю, жанру или любому другому критерию

Типичные ошибки при работе с INNER JOIN

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

Неоднозначные имена столбцов. Если в двух таблицах есть столбец с одинаковым именем (например, albumid), SQLite не сможет определить, из какой таблицы его брать. Всегда указывайте имя таблицы или псевдоним: tracks.albumid, а не просто albumid

Отсутствие условия ON. Если забыть указать условие ON, SQLite выполнит декартово произведение (cross join) — каждая строка первой таблицы будет объединена с каждой строкой второй. Для таблиц с тысячами строк это приведёт к миллионам строк в результате

Ожидание строк без совпадений. INNER JOIN возвращает только строки с совпадениями в обеих таблицах. Если трек не привязан ни к одному альбому, он не попадёт в результат. Для таких случаев используется LEFT JOIN

Неправильный порядок таблиц в условии ON. Условия ON albums.albumid = tracks.albumid и ON tracks.albumid = albums.albumid дают одинаковый результат, но для читаемости кода лучше придерживаться единого стиля: сначала таблица из JOIN, затем таблица из FROM

Часто задаваемые вопросы об INNER JOIN в SQLite

В чём разница между INNER JOIN и LEFT JOIN в SQLite? INNER JOIN возвращает только строки, для которых есть совпадение в обеих таблицах. LEFT JOIN возвращает все строки из левой таблицы, а для строк без совпадения в правой таблице подставляет NULL

Можно ли использовать INNER JOIN более чем с двумя таблицами? Да. Каждое дополнительное предложение INNER JOIN добавляет ещё одну таблицу в запрос. SQLite последовательно обрабатывает каждое условие ON и формирует единый результирующий набор

Обязательно ли указывать имя таблицы перед именем столбца в условии ON? Если имена столбцов уникальны среди всех участвующих таблиц, указывать имя таблицы необязательно. Однако при наличии одноимённых столбцов (например, albumid в tracks и albums) это обязательно — иначе SQLite вернёт ошибку неоднозначности

Влияет ли порядок таблиц в INNER JOIN на результат? Нет. INNER JOIN симметричен: FROM A INNER JOIN B и FROM B INNER JOIN A дают одинаковый результат. Порядок может влиять на производительность в зависимости от оптимизатора запросов, но не на состав возвращаемых строк

Как использовать псевдонимы таблиц, чтобы сократить запрос? Псевдоним (alias) задаётся сразу после имени таблицы: можно использовать короткие обозначения t и al вместо полных имён таблиц во всём запросе