Оператор SQL INNER JOIN
Оператор SQL INNER JOIN формирует таблицу из записей двух или нескольких таблиц. Каждая строка из первой (левой) таблицы, сопоставляется с каждой строкой из второй (правой) таблицы, после чего происходит проверка условия. Если условие истинно, то строки попадают в результирующую таблицу. В результирующей таблице строки формируются конкатенацией строк первой и второй таблиц.
Оператор SQL INNER JOIN имеет следующий синтаксис:
SELECT column_names [. n] FROM Table_1 INNER JOIN Table_2 ON condition
Условие для сравнения задается в операторе ON.
Примеры оператора SQL INNER JOIN. Имеются две таблицы:
Authors — содержит в себе информацию об авторах книг:
| AuthorID | AuthorName |
| 1 | Bruce Eckel |
| 2 | Robert Lafore |
| 3 | Andrew Tanenbaum |
Books — содержит в себе информацию о названии книг:
| BookID | BookName |
| 3 | Modern Operating System |
| 1 | Thinking in Java |
| 3 | Computer Architecture |
| 4 | Programming in Scala |
В таблице Books поле BookID являются внешним ключом и ссылаются на таблицу Authors.
Пример 1. Используя оператор SQL INNER JOIN вывести на экран, какими авторами были написаны какие из книг:
SELECT * FROM Authors INNER JOIN Books ON Authors.AuthorID = Books.BookID
В данном запросе оператора SQL INNER JOIN условие сравнения — это равенство полей AuthorID и BookID. В результирующую таблицу не попадет книга под названием Programming in Scala, так как значение ее BookID не найдет равенства ни с одной строкой AuthorID.
Результирующая таблица будет выглядеть следующим образом:
| Authors.AuthorID | Authors.AuthorName | Books.BookID | Books.BookName |
| 3 | Andrew Tanenbaum | 3 | Modern Operating System |
| 1 | Bruce Eckel | 1 | Thinking in Java |
| 3 | Andrew Tanenbaum | 3 | Computer Architecture |
Как работает SQL Join: описание, методы, примеры
Поговорим о том, как работает Join в SQL-базах данных. Для чего нужна эта директива, какие возможности она открывает и как правильно ее использовать.
Что такое SQL Join?
SQL Join – одна из наиболее часто используемых команд в SQL-синтаксисе. Она используется для поиска информации в базах данных по заранее определенным критериям. В частности, Join отвечает за объединение нескольких групп данных в единый поток информации.
И это действительно необходимо, потому что в 100% случаев контент в реляционных базах данных с поддержкой SQL-синтаксиса делится на множество таблиц, фильтровать данные в которых можно с помощью специальных команд и запросом информации из общего пула таблиц.
SQL Join помогает настроить фильтр поиска в базе данных, опираясь на взаимосвязи между различными элементами БД и их отличительные черты (теги, ID, наименования и т.п.).
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
SQL Inner Join
Этот режим объединения результатов поиска в базах данных SQL включается автоматически. Если вы не укажете намеренно тип Join, то сработает именно Inner Join. С помощью него можно указать сразу два критерия (две таблицы) и по ним отсеять контент.
Достаточно прописать SQL-запрос в духе:
SELECT * FROM table-1 JOIN table-2 ON table-1.parameter=table-2.parameter WHERE table-1.parameter IS ‘myData’
Фактически мы пытаемся выудить данные из первой таблицы и объединить их с данными из второй таблицы, при этом фильтруя только те записи, в которых совпадает значение параметра. В первой таблице оно приравнивается к myData.
На практике это может использоваться на сайте с музыкальными инструментами, например. Можно запрашивать гитары конкретного бренда, при этом еще и выбирая дополнительное условие в духе количества струн.
SELECT * FROM SevenStringGuitars JOIN Ibanez ON SevenStringGuitar.brandId=Ibanez.brandId
Таким SQL-запросом мы можем отфильтровать все инструменты бренда Ibanez в категории «Гитары» с 7 струнами.
SQL Self Join
Запросы Self Join полезны в тех случаях, когда необходимо выполнить фильтрацию контента внутри одной таблицы. Например, у вас есть список товаров в базе данных. У каждого из них указан свой бренд, но есть и те, что поставляются одним производителем. Self Join можно использовать для объединения двух стеков информации из одной таблицы.
Например, можно запросить информацию о наименовании товара и параллельно обратиться к базе с названием бренда. Результатом работы функции станет появление нового списка товаров, соответствующего критериям.
SQL-команда в этом случае может выглядеть следующим образом:
SELECT * FROM products JOIN products ON table.product=table.brand
Такой сценарий полезен практически в любом виде баз данных, так как в одной таблице нередко может храниться информация о товарах или контенте, имеющим большое количество общих параметров.
SQL Cross Join
Самый специфичный вариант фильтрации данных. Он подразумевает сбор сразу всех комбинаций элементов из нескольких таблиц, без обращения к какой-либо дополнительной информации (не требуется указывать id или любую другую строку в таблице).
Стандартный SQL-запрос с Cross Join может выглядеть следующим образом:
SELECT * FROM table-1 CROSS JOIN table-2
Этого достаточно, чтобы создать новый список элементов, в котором будут собраны все строки из базы данных, отфильтрованные только по выбранным таблицам.
Полученный набор данных называют декартовым произведением. Схематично его часто изображают как большое количество перекрестий между двумя группами элементов.
Такой вид JOIN применяется в онлайн-магазинах для вывода всех возможных пар по выбранным характеристикам одежды (цвету и размеру или другим параметрам).
SQL Outer Join
Outer Join – это своего рода противоположность Inner Join. Как понятно из названия, Outer Join предоставляет информацию не только из внутренней части поиска, но и из внешней. То есть программа ищет не только точечные совпадения по выбранным ранее критериям, а позволяет немного ослабить «хватку» и предоставить более «свободные» результаты поиска, включающие в себя элементы из таблиц, которые хоть и совпадают с критериями в SQL-запросе, но не полностью.
Когда такой подход может понадобиться? Например, для скрупулезной фильтрации товаров. Если вы готовы покупать продукцию компании «Шестерочка» и не против, если среди нее окажется молоко, но при этом вы точно не хотите покупать молоко других производителей, то вам подойдет подобный фильтр. Он позволяет дать одному из критериев поиска что-то в духе привилегий.
Разновидности Outer Join
Внешние Join-запросы существуют не в единственном виде, а сразу в трех вариациях. Каждый вариант по-своему обрабатывает информацию и в итоге выдает разные результаты.
Left
Левое объединение подразумевает как раз выше описанный сценарий. Когда мы берем одну таблицу, подключаем вторую и при этом показываем не только точные совпадения, но еще и весь список строк, полученных из левой таблицы, для которых не нашлось пары в правой таблице.
На практике это может выглядеть так:
SELECT * FROM table1 LEFT JOIN table2 ON table1.parameter=table2.parameter
Теперь мы объединяем первую и вторую таблицу, доставая информацию как о совпадениях по заданным параметрам, так и по контенту без пары в левой таблице.
При желании, надстраивая подобный фильтр, можно вовсе исключить целую категорию строк:
SELECT * FROM table1 LEFT JOIN table2 ON table1.parameter=table2.parameter WHERE table2.parameter IS NULL
На живом примере фильтрация такого рода может выглядеть так:
SELECT * FROM Russian LEFT JOIN Rap ON Russian.genreId=Rap.genreId
Представим, что мы запустили продвинутый поиск на сайте с музыкальными альбомами. Мы хотим послушать что-то на русском языке. Причем готовы даже оценить качество отечественного рэпа. При этом в целом мы рэп не любим и не хотим, чтобы он попадался на каких-то других языках.
Right
Понятно, что правое объединение будет работать в обратную сторону и покажет элементы из правой таблицы, для которых не нашлось пары в левой.
Получится следующий SQL-запрос:
SELECT * FROM table1 RIGHT JOIN table2 ON table1.parameter=table2.parameter
Если взять пример из предыдущей главы, то в реальности можно обернуть ситуацию в противоположную сторону. Искать только рэп-музыку, исключив все русское, кроме хип-хопа. Получится что-то в духе:
SELECT * FROM Russian RIGHT JOIN Rap ON Russian.genreId=Rap.genreId
Full
Это вариант для тех, кто хочет использовать сразу два разных критерия для поиска какого-либо контента. Снова вернемся к примеру с музыкальным приложением. Join Full может пригодиться, если вы хотите послушать либо что-то на русском, либо любой рэп. Вам не важны какие-либо другие параметры. Вас волнуют исключительно две характеристики. При этом вам не так важно, будут ли они пересекаться. То есть вам все равно, будет рэп на русском или же на русском будет какой-то агрессивный металл.
SQL-запрос с таким Join мог бы выглядеть следующим образом:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.parameter=table2.parameter
Можно исключить из результатов фильтрации все пары. То есть можно выбрать только рэп, но ни в коем случае не русский, и русскую музыку, но ни в коем случае не рэп (вполне могу понять такой выбор, кстати говоря).
Чтобы это сделать, необходимо написать следующий SQL-запрос.
SELECT * FROM Russian FULL OUTER JOIN Rap ON Russian.genreId=Rap.genreId WHERE Russian.genreId IS NULL OR Rap.genreId IS NULL
Теперь вы увидите в результатах поиска только непарные строки.
Вместо заключения
SQL Join – мощнейший инструмент для фильтрации строк в базах данных. Благодаря ему можно легко находить именно ту информацию, что нужна, а не возиться с недоделанными фильтрами, которые обычно предоставляют разработчики сайтов и приложений. Жаль, что такие мощные механизмы поиска доступны далеко не везде. Но, создавая собственные продукты, вы можете их реализовать. Power-пользователи точно останутся довольны.
Операция INNER JOIN (Microsoft Access SQL)
Объединяет записи из двух таблиц, если в связующих полях этих таблиц содержатся одинаковые значения.
Синтаксис
FROM table1 INNER JOIN table2 ON table1. field1compopr table2. field2
Операция INNER JOIN состоит из следующих элементов:
Имена таблиц, содержащих объединяемые записи.
Имена объединяемых полей. Поля, не являющиеся числовыми, должны относиться к одному типу данных и содержать данные одного вида. Однако имена этих полей могут быть разными.
Любой оператор реляционного сравнения: «=», «,» «=» или «<>«.
Примечания
Операцию INNER JOIN можно использовать в любом предложении FROM. Это самый распространенный тип объединения. С его помощью происходит объединение записей из двух таблиц по связующему полю, если оно содержит одинаковые значения в обеих таблицах.
При работе с таблицами «Отделы» и «Сотрудники» операцией INNER JOIN можно воспользоваться для выбора всех сотрудников в каждом отделе. Если же требуется выбрать все отделы (включая те из них, в которых нет сотрудников) или всех сотрудников (в том числе и не закрепленных за отделом), можно при помощи операции LEFT JOIN или RIGHT JOIN создать внешнее соединение.
При попытке связи полей, содержащих данные типа Memo или объекты OLE, возникнет ошибка.
Можно связать любые два числовых поля аналогичных типов. Например, можно связать поля AutoNumber и Long, так как эти типы аналогичны, однако не поля Single и Double.
В следующем примере показано, как можно объединить таблицы Categories и Products по полю CategoryID.
SELECT CategoryName, ProductName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID; В предыдущем примере CategoryID является объединенным полем, но оно не включается в результаты запроса, поскольку не указано в инструкции SELECT. Чтобы включить объединенное поле в результаты запроса, добавьте его имя в инструкцию SELECT. В данном случае это Categories.CategoryID.
В инструкции JOIN можно также связать несколько предложений ON, используя следующий синтаксис:
Ниже приведен пример синтаксиса, с помощью которого можно составлять вложенные инструкции JOIN.
SELECT поля FROM table1 INNER JOIN (table2 INNER JOIN [( ]table3 [INNER JOIN [( ]tablex [INNER JOIN . )] ON table3. field3compoprtablex. fieldx)] ON table2. field2compoprtable3. field3) ON table1. field1compoprtable2. field2;
Операции LEFT JOIN и RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.
Пример
В этом примере создается два уравнивающих соединения: одно между таблицами Order Details (Сведения о заказах) и Orders (Заказы), а другое между таблицами Orders (Заказы) и Employees (Сотрудники). Это необходимо, так как таблица Employees (Сотрудники) не содержит данные о продажах, а таблица Order Details (Сведения о заказах) не содержит данные сотрудников. Результат запроса представляет собой список сотрудников и их общие объемы продаж.
В этом примере вызывается процедура EnumFields, которую можно найти в примере инструкции SELECT.
Sub InnerJoinX() Dim dbs As Database, rst As Recordset ' Modify this line to include the path to Northwind ' on your computer. Set dbs = OpenDatabase("Northwind.mdb") ' Create a join between the Order Details and ' Orders tables and another between the Orders and ' Employees tables. Get a list of employees and ' their total sales. Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " _ & "Sum(UnitPrice * Quantity) AS Sales, " _ & "(FirstName & Chr(32) & LastName) AS Name " _ & "FROM Employees INNER JOIN(Orders " _ & "INNER JOIN [Order Details] " _ & "ON [Order Details].OrderID = " _ & "Orders.OrderID ) " _ & "ON Orders.EmployeeID = " _ & "Employees.EmployeeID " _ & "GROUP BY (FirstName & Chr(32) & LastName);") ' Populate the Recordset. rst.MoveLast ' Call EnumFields to print the contents of the ' Recordset. Pass the Recordset object and desired ' field width. EnumFields rst, 20 dbs.Close End Sub
Операция INNER JOIN
Объединяет записи из двух таблиц, если в связующих полях этих таблиц содержатся одинаковые значения.
Синтаксис
FROM таблица1 INNER JOIN таблица2 ON таблица1.поле1оператор_сравнения таблица2.поле2
Операция INNER JOIN состоит из следующих элементов:
таблица1, таблица2
Имена таблиц, содержащих объединяемые записи.
поле1, поле2
Имена полей, которые соединены. Если они не являются числами, поля должны быть одного тип данных и содержать данные одного типа, но не должны иметь одинаковых имен.
Любой оператор сравнения: (=, , = или <>)
Замечания
Операцию INNER JOIN можно использовать в любом предложении FROM. Это самый распространенный тип объединения. С его помощью происходит объединение записей из двух таблиц по связующему полю, если оно содержит одинаковые значения в обеих таблицах.
При работе с таблицами «Отделы» и «Сотрудники» операцией INNER JOIN можно воспользоваться для выбора всех сотрудников в каждом отделе. Если же требуется выбрать все отделы (включая те из них, в которых нет сотрудников) или всех сотрудников (в том числе и не закрепленных за отделом), можно при помощи операции LEFT JOIN или RIGHT JOIN создать внешнее соединение.
При попытке связи полей, содержащих данные типа Memo или объекты OLE, возникнет ошибка.
Вы можете объединить любые два числовых поля похожих типов. Например, можно объединить поля с типом данных «Автонобере» и «Длинный». Однако нельзя соединить поля Single и Double.
В следующем примере показано, как можно объединить таблицы Categories и Products по полю CategoryID.
SELECT CategoryName, ProductName
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID;
В предыдущем примере CategoryID — это соединенное поле, но оно не включается в результаты запроса, так как оно не включено в утверждение SELECT. Чтобы включить в него соединенное поле, включив его имя в утверждение SELECT— в данном случае это Categories.CategoryID .
В инструкции JOIN можно также связать несколько предложений ON, используя следующий синтаксис:
SELECT поля
FROM таблица1 INNER JOIN таблица2
ON таблица1.таблица1оператор_сравнениятаблица2.поле1 AND
ON таблица1.поле2оператор_сравнениятаблица2.поле2) OR
ON таблица1.поле3оператор_сравнениятаблица2.поле3)];
Ниже приведен пример синтаксиса, с помощью которого можно составлять вложенные инструкции JOIN.
SELECT поля
FROM таблица1 INNER JOIN
(таблица2 INNER JOIN [( ]таблица3
[INNER JOIN [( ]таблицаx [INNER JOIN . )]
ON таблица3.поле3оператор_сравнениятаблицаx.полеx)]
ON таблица2.поле2оператор_сравнениятаблица3.поле3)
ON таблица1.поле1оператор_сравнениятаблица2.поле2;
Операции LEFT JOIN и RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.