Как сделать inner join из нескольких таблиц
В прошлой теме было рассмотрено неявное соединение таблиц с помощью простой выборки путем сведения данных. Однако более распространенным подходом свдения данных из разных таблиц является применение оператора JOIN . SQLite поддерживает несколько вариантов оператора JOIN . В этой статье рассмотрим INNER JOIN . Общий формальный синтаксис этой версии оператора :
SELECT столбцы FROM таблица1 [INNER] JOIN таблица2 ON условие1 [[INNER] JOIN таблица3 ON условие2]
После оператора JOIN идет название второй таблицы, из которой надо добавить данные в выборку. Перед JOIN может использоваться необязательное ключевое слово INNER . Его наличие или отсутствие ни на что не влияет. Затем после ключевого слова ON указывается условие соединения. Это условие устанавливает, как две таблицы будут сравниваться. В большинстве случаев для соединения применяется первичный ключ главной таблицы и внешний ключ зависимой таблицы.
Возьмем таблицы с данными из прошлой темы:
DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS customers; CREATE TABLE products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, company TEXT NOT NULL, items_count INTEGER DEFAULT 0, price INTEGER ); CREATE TABLE customers ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, product_id INTEGER NOT NULL, customer_id INTEGER NOT NULL, created_at TEXT NOT NULL, items_count INTEGER DEFAULT 1, price INTEGER NOT NULL, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE );
Пусть эти таблицы будут содержать следующие данные:
INSERT INTO products (name, company, items_count, price) VALUES ('iPhone 13', 'Apple', 3, 76000), ('iPhone 12', 'Apple', 2, 51000), ('Galaxy S21', 'Samsung', 2, 56000), ('Galaxy S20', 'Samsung', 1, 41000), ('P40 Pro', 'Huawei', 5, 36000); INSERT INTO customers(name) VALUES ('Tom'), ('Bob'),('Sam'); INSERT INTO orders (product_id, customer_id, created_at, items_count, price) VALUES ( (SELECT id FROM products WHERE name='Galaxy S21'), (SELECT id FROM customers WHERE name='Tom'), '2021-11-30', 2, (SELECT price FROM products WHERE name='Galaxy S21') ), ( (SELECT id FROM products WHERE name='iPhone 12'), (SELECT id FROM customers WHERE name='Tom'), '2021-11-29', 1, (SELECT price FROM products WHERE name='iPhone 12') ), ( (SELECT id FROM products WHERE name='iPhone 12'), (SELECT id FROM customers WHERE name='Bob'), '2021-11-29', 1, (SELECT price FROM products WHERE name='iPhone 12') );
Используя JOIN, выберем все заказы и добавим к ним информацию о товарах:
SELECT orders.created_at, orders.items_count, products.name FROM orders JOIN products ON products.id = orders.product_id;
Поскольку таблицы могут содержать столбцы с одинаковыми названиями, то при указании столбцов для выборки указывается их полное имя вместе с именем таблицы, например, «orders.items_count».
Используя псевдонимы для таблиц, можно сократить код:
SELECT O.created_at, O.items_count, P.name FROM orders AS O JOIN products AS P ON P.id = O.product_id;
Также можно присоединять данные сразу из нескольких таблиц. Например, добавим к заказу информацию о покупателе из таблицы customers:
SELECT orders.created_at, customers.name, products.name FROM orders JOIN products ON products.id = orders.product_id JOIN customers ON customers.id=orders.customer_id;
Благодаря соединению таблиц мы можем использовать их столбцы для фильтрации выборки или ее сортировки:
SELECT orders.created_at, customers.name, products.name FROM orders JOIN products ON products.id = orders.product_id JOIN customers ON customers.id=orders.customer_id WHERE products.price > 45000 ORDER BY customers.name;
Условия после ключевого слова ON могут быть более сложными по составу:
SELECT orders.created_at, customers.name, products.name FROM orders JOIN products ON products.id = orders.product_id AND products.company='Apple' JOIN customers ON customers.id=orders.customer_id ORDER BY customers.name;
В данном случае выбираем все заказы на товары, производителем которых является Apple.
Выборка из нескольких таблиц inner join
Имеется структура БД следующего вида: Мне необходимо сделать выборку из таблицы Drivers(date, pack, directory) и Usable(system). При написании запроса у меня(скорее всего) где-то декартово произведение, ибо выдаётся большое количество строк — дубликатов. Использование distinct проблему решает, удаляя дубликаты, и я получаю нужный результат, однако я и сам понимаю, что это жутки костыль и так делать не нужно. Ниже приведён текст моего запроса, посмотрите, может быть я делаю очевидную ошибку:
select Drivers.pack Drivers.directory, Drivers.[date], Usable.[system] from Sections inner join Drivers on ( Drivers.id in ( select driverId from Sections where Sections.id in ( select sectionId from Usable where deviceId in ( select id from Devices where deviceId like "%VEN_14F1%" and deviceId like "%DEV_8880%" ) ) ) and Drivers.id = Sections.driverId ) inner join Usable on ( Usable.[system] = "6.1x64" and Usable.sectionId = Sections.id ) order by(Drivers.[date]) desc; Подскажите решение. P.S.: код отформатировал, надеюсь, так будет удобнее читать. UPD:
SELECT Drivers.date, Drivers.pack, Drivers.directory, Usable.system FROM Drivers INNER JOIN Sections ON Drivers.id = Sections.driverId INNER JOIN Usable ON Sections.id = Usable.sectionId inner join Devices on Usable.deviceId = Devices.id WHERE Devices.deviceId LIKE "%VEN_14F1%" AND Devices.deviceId LIKE "%DEV_8880%" AND Usable.system = '6.1x64' order by(Drivers.[date]) desc; Join — инструмент объединения данных из нескольких связанных таблиц
Реляционная модель данных подразумевает отдельное хранение и возможность независимой обработки данных для каждой сущности.
Вместе с тем, часто возникает потребность собрать данные из нескольких связанных таблиц.
Как правило, сущности (таблицы) связаны друг с другом внешними связями по принципу (primary key — foreign key).
Связи могут быть типа «1 к 1» или «1 ко многим» (с вариантами «1 к 0 или 1», «1 к 0 или более», «1 к 2» и пр).
Связь «многие-ко-многим» в реляционной модели обеспечивается с помощью дополнительной таблицы связей (другие названия: Link-таблица, Bridge-таблица, xref-таблица).
В зависимости от характера связей между таблицами, операция соединения может быть:
- внутренним соединением (INNER JOIN). При этом:
- если для описания связи между наборами данных используются корреляционные подзапросы, то такой INNER JOIN называют CROSS APPLY
- если условие соединения отсутствует, то такой INNER JOIN называют “декартовым произведением” (CROSS JOIN, CARTESIAN PRODUCT)
- если для описания связи между наборами данных используются корреляционные подзапросы, то такой OUTER JOIN называют OUTER APPLY
Новичку порой трудно разобраться, когда и какой тип джоинов нужно использовать и чем одни из них лучше других.
В этой статье мы попробуем решить разными способами несколько практических задач, отмечая плюсы и минусы каждого решения.
Пример логической модели данных:
Обратите внимание на характер данных в наших таблицах.
У некоторых спортсменов нет категории, клуба или тренера (возможно, они неизвестны).
Встречаются спортсмены, которых ведёт несколько тренеров.Про одни сущности есть чуть больше информации чем про другие.
Знакомство с данными.
use tempdb go --содержимое таблиц: select * from dbo.SwimmingClub select * from dbo.Swimmer select * from dbo.Category (3 rows affected) (7 rows affected) (3 rows affected)
Пример #1. Найти всех спортсменов из клуба Янтарь, имеющих II спортивный разряд.
Используя старую нотацию:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc, dbo.Swimmer s, dbo.Category c where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID and s.CategoryID = c.CategoryID and c.[Name] = N'II' (1 row affected)
В этой форме записи WHERE-часть перегружена условиями. Часть из них относятся к логической модели данных (у. 2 и 3), часть – к логике текущей задачи (у. 1 и 4).
В сложных запросах WHERE-часть может стать просто огромной!
Используя новую нотацию:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID inner join dbo.Category c on s.CategoryID = c.CategoryID where sc.[Name] like N'%Янтарь%' and c.[Name] = N'II' (1 row affected)
Преимуществом этой формы записи является “разгрузка” WHERE-части от условий, определенных логической моделью данных, при этом четко видно какое из условий используется для реализации связи между какими таблицами.
В сложных запросах новая форма записи позволяет добиться существенно лучшей читаемости кода (а, как увидим позже, еще и лучшей готовности кода к рефакторингу).
Используя CROSS JOIN:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc cross join dbo.Swimmer s cross join dbo.Category c where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID and s.CategoryID = c.CategoryID and c.[Name] = N'II' (1 row affected)
Обратите внимание на то, как запрос с CROSS JOIN похож на вариант решения в старой нотации!
На самом деле, ничего удивительного, т. к. логически они выражают один и тот же подход к решению задачи: “все источники данных — в FROM, все условия — в WHERE. При отсутствии условий каждая таблица декартово перемножается с каждой”.Вообще, вариантов решения задачи много. Но, как можно догадаться, не все они оптимальны;).
Например, порой начинающие программисты для решения первой задачи создают что-то вроде этого:
use tempdb go declare @ClubId int, @ClubCity nvarchar(30), @ClubName nvarchar(100), @CategoryId int set @ClubId = ( select SwimmingClubId from dbo.SwimmingClub where [Name] like N'%Янтарь%' ) set @ClubCity = ( select City from dbo.SwimmingClub where [Name] like N'%Янтарь%' ) set @ClubName = ( select [Name] from dbo.SwimmingClub where [Name] like N'%Янтарь%' ) set @CategoryId = ( select CategoryId from dbo.Category where [Name] = N'II' ) select SwimmerID, FirstName, LastName, YearOfBirth, Gender, @ClubName Club, @ClubCity City, N'II' Category from dbo.Swimmer where SwimmingClubID = @ClubId and CategoryId = @CategoryId (1 row affected)
Идея за этим кодом такова.
Чтобы найти всех спортсменов из клуба Янтарь, имеющих II спортивный разряд, нужно:
найти код клуба Янтарь (значение внешнего ключа SwimmingClubId)
2. dbo.Category
найти код категории, указывающей на II разряд (значение внешнего ключа CategoryId)
3. dbo.Swimmer
зная эти два значения, найти соответствующих пловцов
Приведенный выше алгоритм “напрашивается” сам собой. Именно поэтому разработчики, далекие от SQL, пишут такие решения.
Недостатком этого подхода являются:
- длинный код (объявление переменных + 5 инструкций SELECT)
- худшая производительность (пять селектов явно медленнее одного)
- неизолированность от конкурирующих транзакций (пока мы рассчитываем значения переменных, данные в любой из таблиц могут быть изменены кем-то другим)
- если есть несколько клубов со словом “Янтарь” в названии, любой из трех первых селектов упадет.
Пример #2. Вывести спортсменов из клуба Янтарь с теми же атрибутами что и выше, но без требования иметь II спортивный разряд.
Используя старую нотацию:
use tempdb go --это код с багом! в случае если у спортсмена нет разряда, запись о нем не выводится select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc, dbo.Swimmer s, dbo.Category c where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID and s.CategoryID = c.CategoryID --подправленный код (один из вариантов) select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, (select c.[Name] from dbo.Category c where c.CategoryID = s.CategoryID) Category from dbo.SwimmingClub sc, dbo.Swimmer s where sc.[Name] like N'%Янтарь%' and sc.SwimmingClubID = s.SwimmingClubID (2 rows affected) (3 rows affected)
Интуитивно напрашивающееся решение адаптировать старый код под новые требования удалением соответствующего условия не работает!
В старой нотации нужно существенно менять сам код при незначительных изменениях в требованиях.
Используя новую нотацию:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID left join dbo.Category c on s.CategoryID = c.CategoryID where sc.[Name] like N'%Янтарь%' (3 rows affected)
В отличии от старой, после удаления уже ненужного требования, в новой нотации нужно лишь поменять слово inner на left.
Вариант решения задачи с outer apply:
use tempdb go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, c.[Name] Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID outer apply (select [Name] from dbo.Category c where c.CategoryID = s.CategoryId) c where sc.[Name] like N'%Янтарь%' (3 rows affected)
Несмотря на запись запроса, похожую на вариант с LEFT JOIN, этот способ не оптимален из-за имеющегося корреляционного подзапроса.
Последний приводит к тому, что выполнение идет по принципу row—by—row вместо set—based. Логически, мы выполняем корреляционный подзапрос с внешним параметром s.CategoryId столько раз, сколько строчек в таблице dbo.Swimmer.Корреляционные запросы существенно ухудшают производительность!
Вариант решения задачи с пользовательской скалярной функцией:
use tempdb go create or alter function dbo.fn_GetCategoryName(@CategoryID int) returns nvarchar as begin return (select [Name] from dbo.Category where CategoryId = @CategoryID) end go select s.SwimmerID, s.FirstName, s.LastName, s.YearOfBirth, s.Gender, sc.[Name] Club, sc.City, dbo.fn_GetCategoryName(s.CategoryId) Category from dbo.SwimmingClub sc inner join dbo.Swimmer s on s.SwimmingClubID = sc.SwimmingClubID where sc.[Name] like N'%Янтарь%' (3 rows affected)
Использование пользовательской функции, на первый взгляд, помогает упростить решение задачи и, опять же, масса разработчиков идет этим путем, следуя привычке разбивать сложный функционал на отдельные блоки.
Вместе с тем, это решение в условиях баз данных, скорее всего, еще хуже предыдущего:
- корреляционный подзапрос никуда не делся, он лишь мигрировал в тело функции
- в ходе выполнения запроса функция будет вызвана множество раз, что негативно влияет на производительность (из-за особенностей работы СУБД и интерпретатора языка SQL)
- для решения одной конкретной задачи понадобилось создать дополнительный постоянный объект БД – пользовательскую скалярную функцию.
Если пользоваться таким подходом постоянно, то скоро БД будет завалена множеством непонятных программных объектов!
Вывод.
В рассмотренных примерах победителем стал запрос, созданный в новой нотации без использования корреляционных подзапросов. Он максимально читабельный, легко меняется в случае изменения требований, производительный и не требует создания дополнительных объектов в БД.
Автор материала – Тимофей Гавриленко, преподаватель Тренинг центра ISsoft.
Образование: окончил с отличием математический факультет Гомельского Государственного Университета им. Франциска Скорины.
Microsoft Certified Professional (70-464, 70-465).
Работа: c 2011 года работает в компании ISsoft (ETL/BI Developer, Release Manager, Data Analyst/Architect, SQL Training Manager), на протяжении 10 лет до этого выступал как Sysadmin, DBA, Software Engineer.
В свободное время ведет бесплатные образовательные курсы для детей и взрослых, желающих повысить свой уровень компьютерной грамотности и переквалифицироваться в IT-специалиста.
Как сделать inner join из нескольких таблиц
В предыдущих статьях описывалась работа только с одной таблицей базы данных. В реальности же очень часто приходится делать выборку из нескольких таблиц, каким-то образом объединяя их. В данной статье вы узнаете основные способы соединения таблиц.
Например, если мы хотим получить информацию о тратах на покупки, мы можем её получить следующим образом:
MySQLSELECT family_member, amount * unit_price AS price FROM Paymentsfamily_member price 1 2000 2 2100 3 100 4 350 4 300 5 100 2 120 2 5500 5 230 3 2200 2 66000 1 40 3 100 3 1200 В поле family_member полученной выборки отображаются идентификаторы записей из таблицы Payments , но для нас они мало что значат.
Вместо этих идентификаторов было бы гораздо нагляднее выводить имена тех, кто покупал (поле member_name из таблицы FamilyMember ). Ровно для этого и существует объединение таблиц и оператор JOIN .
MySQLSELECT поля_таблиц FROM таблица_1 [INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_2 ON условие_соединения [INNER] | [[LEFT | RIGHT | FULL][OUTER]] JOIN таблица_n ON условие_соединения]Как можно увидеть по структуре, соединение бывает:
- внутренним INNER (по умолчанию)
- внешним OUTER , при этом внешнее соединение делится на левое LEFT , правое RIGHT и полное FULL
С более подробными деталями, чем отличается внутреннее соединение от внешнего и как они работают, мы познакомимся в следующих статьях.
Пока нам достаточно лишь знать, что для вышеописанного примера с запросом на покупки нам понадобится именно запрос с внутренним соединением, который будет выглядеть следующим образом:
MySQLSELECT family_member, member_name, amount * unit_price AS price FROM Payments INNER JOIN FamilyMembers ON Payments.family_member = FamilyMembers.member_idfamily_member member_name price 1 Headley Quincey 2000 2 Flavia Quincey 2100 3 Andie Quincey 100 4 Lela Quincey 350 4 Lela Quincey 300 5 Annie Quincey 100 2 Flavia Quincey 120 2 Flavia Quincey 5500 5 Annie Quincey 230 3 Andie Quincey 2200 2 Flavia Quincey 66000 1 Headley Quincey 40 3 Andie Quincey 100 3 Andie Quincey 1200 В данном запросе мы сопоставляем записи из таблицы Payments и записи из таблицы FamilyMembers .
Чтобы сопоставление работало, мы указываем как именно записи из двух разных таблиц должны находить друг друга. Это условие указывается после ON :
MySQLON Payments.family_member = FamilyMembers.member_idВ нашем случае поле family_member указывает на идентификатор в таблице FamilyMembers и таким образом помогает однозначному сопоставлению.
В большинстве случаев условием соединения является равенство столбцов таблиц (таблица_1.поле = таблица_2.поле), однако точно так же можно использовать и другие операторы сравнения.