Sql ms join: Joins (SQL Server) — SQL Server

Функция Join в SQL: описание и примеры

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

Примеры использования SQL Server JOIN

Данный материал является переводом оригинальной статьи «MSSQLTips : Jeremy Kadlec : SQL Server Join Example».

Вы новичок в Microsoft SQL Server и хотите узнать о параметрах JOIN для реляционной базы данных? Каковы все параметры JOIN в SQL Server и в чем значение каждого из них? Вы хотите получить несколько примеров и объяснений? Ознакомьтесь с этой небольшой статьёй, чтобы получить ответы на некоторые вопросы и узнать о синтаксисе SQL Server JOIN.

Объединение таблиц для получения необходимых данных для запроса, скрипта или хранимых процедур является ключевым понятием в разработке на базе SQL Server. Кратко обозначим, что соединения JOIN обычно выполняются в предложении FROM таблицы или представления для выражений SELECT, INSERT…SELECT, SELECT…INTO, UPDATE и DELETE . В предыдущих версиях SQL Server, логику соединения также можно было включить в выражение WHERE с синтаксисом = (INNER JOIN), = (LEFT OUTER JOIN), = (RIGHT OUTER JOIN), и т.д., но поддержка была сокращена, и лучшая практика в SQL Server — использовать синтаксис, описанный в примерах ниже.

Прежде чем мы перейдем к коду, давайте предоставим некоторую базовую информацию о типах JOIN в SQL Server:

  • INNER JOIN — сопоставление строки между двумя таблицами, указанными в операторе INNER JOIN, на основе одного или нескольких столбцов, имеющих совпадающие данные — Equi Join. Предпочтительно соединение основано на ссылочной целостности, обеспечивающей взаимосвязь между таблицами для обеспечения целостности данных.
    • В целом параметр INNER JOIN считается наиболее распространенным соединением, необходимым в приложениях и/или запросах. Хотя это так в некоторых средах, это действительно зависит от дизайна базы данных, ссылочной целостности и данных, необходимых для приложения. Таким образом, найдите время, чтобы понять запрашиваемые данные, а затем выберите правильный вариант соединения.
    • Хотя большая часть логики соединения основана на сопоставлении значений между двумя указанными столбцами, можно также включить логику с использованием больше, меньше, не равно и т. д.
  • LEFT OUTER JOIN – на основе двух таблиц, указанных в предложении соединения, все данные возвращаются из левой таблицы. В правой таблице соответствующие данные возвращаются в дополнение к значениям NULL, если запись существует в левой таблице, но не в правой таблице.
    • Еще один момент, о котором следует помнить, это то, что логика LEFT и RIGHT OUTER JOIN противоположна друг другу. Таким образом, вы можете изменить либо порядок таблиц в конкретном операторе соединения, либо изменить JOIN слева направо или наоборот и получить те же результаты.
  • RIGHT OUTER JOIN — на основе двух таблиц, указанных в предложении соединения, все данные возвращаются из правой таблицы. В левой таблице соответствующие данные возвращаются в дополнение к значениям NULL, если запись существует в правой таблице, но не в левой таблице.
  • SELF JOIN — в этом случае одна и та же таблица указывается дважды с двумя разными псевдонимами для сопоставления данных в одной и той же таблице.
  • CROSS JOIN — на основе двух таблиц, указанных в предложении соединения, создается декартово произведение, если предложение WHERE фильтрует строки. Размер декартова произведения основан на умножении количества строк из левой таблицы на количество строк в правой таблице. Будьте осторожны при использовании CROSS JOIN.
  • FULL JOIN — на основе двух таблиц, указанных в предложении соединения, все данные возвращаются из обеих таблиц независимо от совпадающих данных.

Давайте рассмотрим примеры из демонстрационной базы данных AdventureWorks, доступной для SQL Server, чтобы предоставить примеры операторов SQL для каждого типа JOIN, а затем дать некоторое представление об использовании и примерах наборов результатов.

 

Пример SQL Server INNER JOIN

В следующем запросе у нас есть предложение INNER JOIN между таблицами Sales.SalesOrderDetail и Production.Product. Таблицы имеют следующие псевдонимы: SOD для Sales.SalesOrderDetail и P для Production.Product. Условие JOIN основано на совпадении строк в столбцах SOD.ProductID и P.ProductID. Записи фильтруются, возвращая только записи с SOD.UnitPrice (имя столбца) больше 1000. Наконец, набор результатов возвращается в порядке, начиная с самого дорогого на основе предложения ORDER BY и только самых высоких 100 продуктов на основе предложения TOP.

USE MSSQLTips; 
GO 
SELECT TOP 100 P.ProductID, 
 P.Name, 
 P.ListPrice, 
 P.Size, 
 P.ModifiedDate, 
 SOD.UnitPrice, 
 SOD.UnitPriceDiscount,
 SOD.OrderQty,
 SOD.LineTotal 
FROM Sales.SalesOrderDetail SOD 
INNER JOIN Production.Product P 
 ON SOD.ProductID = P.ProductID 
WHERE SOD.UnitPrice > 1000 
ORDER BY SOD.UnitPrice DESC
GO

 

Пример SQL Server LEFT OUTER JOIN

В следующем запросе мы покажем то, что в одном операторе SELECT можно объединить более двух таблиц и можно использовать более одного типа JOIN. В приведенном ниже примере кода мы получаем совпадающие строки между таблицами Person.Contact и Sales.SalesPerson вместе со всеми данными из таблицы Sales.SalesPerson и совпадающими строками в таблице Sales.SalesTerritory. Для записей, которые существуют в таблице Sales.SalesPerson, а не в таблице Sales.SalesTerritory, для столбцов в Sales.SalesTerritory возвращаются значения NULL. Кроме того, этот код использует два столбца для упорядочивания данных, то есть ST. TerritoryID и C.LastName.

USE MSSQLTips;
GO
SELECT  C.ContactID,
 C.FirstName,
 C.LastName,
 SP.SalesPersonID,
 SP.CommissionPct,
 SP.SalesYTD,
 SP.SalesLastYear,
 SP.Bonus,
 ST.TerritoryID,
 ST.Name,
 ST.[Group],
 ST.SalesYTD
FROM Person.Contact C
INNER JOIN Sales.SalesPerson SP
 ON C.ContactID = SP.SalesPersonID
LEFT OUTER JOIN Sales.SalesTerritory ST 
 ON ST.TerritoryID = SP.TerritoryID
ORDER BY ST.TerritoryID, C.LastName
GO

 

Пример SQL Server RIGHT OUTER JOIN

Чтобы показать то, как RIGHT OUTER JOIN и LEFT OUTER JOIN логически исключают друг друга, следующий запрос является переписанной версией LEFT OUTER JOIN, описанного выше. Как видите, порядок JOIN и таблицы различаются, но конечный набор результатов соответствует логике LEFT OUTER JOIN. В приведенном ниже примере кода мы получаем совпадающие строки между таблицами Person.Contact и Sales.SalesPerson вместе со всеми данными из таблицы Sales.SalesPerson и совпадающими строками в таблице Sales.SalesTerritory. Для записей, которые существуют в таблице Sales.SalesPerson, а не в таблице Sales.SalesTerritory, для столбцов в Sales.SalesTerritory возвращаются значения NULL.

USE MSSQLTips;
GO 
SELECT  C.ContactID, 
 C.FirstName, 
 C.LastName, 
 SP.SalesPersonID,
 SP.CommissionPct,
 SP.SalesYTD,
 SP.SalesLastYear,
 SP.Bonus,
 ST.TerritoryID,
 ST.Name, ST.[Group],
 ST.SalesYTD 
FROM Sales.SalesTerritory ST 
RIGHT OUTER JOIN Sales.SalesPerson SP 
 ON ST.TerritoryID = SP.TerritoryID 
INNER JOIN Person.Contact C 
 ON C.ContactID = SP.SalesPersonID 
ORDER BY ST.TerritoryID, C.LastName
GO

 

Пример SQL Server SELF JOIN

В этом примере мы фактически присоединяемся к таблице HumanResources.Employee. Мы делаем это, чтобы получить информацию об отношениях Employee и Manager в таблице HumanResources.Employee. В сочетании с этой логикой JOIN мы также дважды присоединяемся к Person.Contact, чтобы получить данные об имени и заголовке на основе исходных отношений Employee и Manager. Кроме того, еще одна новая концепция, представленная в этом запросе, — это псевдоним каждого из имен столбцов. Хотя мы могли сделать это в предыдущих примерах, мы сделали это в этом запросе, чтобы различать данные, связанные с сотрудником и менеджером.

USE MSSQLTips;
GO
SELECT  M.ManagerID AS 'ManagerID',
 M1.ContactID AS 'ManagerContactID',
 M1.FirstName AS 'ManagerFirstName',
 M1.LastName AS 'ManagerLastName',
 M.Title AS 'ManagerTitle',
 E.EmployeeID AS 'EmployeeID',
 E1.ContactID AS 'EmployeeContactID',
 E1.FirstName AS 'EmployeeFirstName',
 E1.LastName AS 'EmployeeLastName',
 E.Title AS 'EmployeeTitle'
FROM HumanResources.Employee E 
INNER JOIN HumanResources.Employee M 
 ON E.ManagerID = M.EmployeeID 
INNER JOIN Person.Contact E1 
 ON E1.ContactID = E.ContactID 
INNER JOIN Person.Contact M1 
 ON M1.ContactID = M.ContactID
ORDER BY M1.LastName
GO

 

Пример SQL Server CROSS JOIN

Как сказано ранее, следует соблюдать осторожность при выполнении или изменении этого запроса в любой среде базы данных SQL Server. Набор результатов намеренно ограничен предложением TOP 100 и предложением WHERE, чтобы предотвратить декартово произведение, которое является результатом умножения каждой из строк из левой таблицы на количество строк в правой таблице.

USE MSSQLTips; 
GO 
SELECT  TOP 100 P.ProductID, 
 P.Name, 
 P.ListPrice, 
 P.Size, 
 P.ModifiedDate, 
 SOD.UnitPrice, 
 SOD.UnitPriceDiscount,
 SOD.OrderQty,
 SOD.LineTotal 
FROM Sales.SalesOrderDetail SOD 
CROSS JOIN Production.Product P 
WHERE SOD.UnitPrice > 3500 
ORDER BY SOD.UnitPrice DESC
GO

 

Пример SQL Server FULL OUTER JOIN

В нашем последнем примере мы изменили логику из приведенного выше примера LEFT OUTER JOIN и преобразовали синтаксис в FULL OUTER JOIN. В этом случае набор результатов такой же, как LEFT OUTER JOIN, где мы возвращаем все данные между обеими таблицами, а данные, недоступные в Sales.SalesTerritory, возвращаются как NULL.

USE MSSQLTips;
GO
SELECT  C.ContactID,
 C.FirstName,
 C.LastName,
 SP.SalesPersonID,
 SP.CommissionPct,
 SP.SalesYTD,
 SP.SalesLastYear,
 SP.Bonus,
 ST.TerritoryID,
 ST.Name,
 ST.[Group],
 ST.SalesYTD
FROM Person.Contact C
INNER JOIN Sales.SalesPerson SP
 ON C.ContactID = SP.SalesPersonID
FULL OUTER JOIN Sales. SalesTerritory ST 
 ON ST.TerritoryID = SP.TerritoryID
ORDER BY ST.TerritoryID, C.LastName
GO

 

Следующие шаги

Когда вы начнете писать код на SQL Server, убедитесь, что у вас есть четкое представление о доступных параметрах JOIN, а также о связанных с ними данных, которые извлекаются. Обязательно выберите правильную логику JOIN на основе данных, которые необходимо получить.

После того, как вы твердо усвоите логику JOIN с помощью операторов SELECT, переходите к использованию логики с выражениями INSERT…SELECT, SELECT…INTO, UPDATE и DELETE.

В процессе обучения обязательно ознакомьтесь с некоторыми альтернативами JOIN, например:

  • Joining data and differences of using UNION and UNION ALL in SQL Server
  • Comparing Multiple SQL Server Datasets with the INTERSECT and EXCEPT operators

Полезными могут оказаться следующие заметки MSSQLTips:

  • SQL Server SELECT Tutorial
  • SQL Server Stored Procedure
  • SQL Server Cursor Examples
  • Getting started with SQL Server stored procedures
  • SQL Server SELECT Examples
  • SQL SELECT DISTINCT Examples
  • Using MERGE in SQL Server to insert, update and delete at the same time
  • SQL INSERT INTO SELECT Examples
  • SQL UPDATE Statement
  • SQL Update Statement with Join in SQL Server vs Oracle vs PostgreSQL
  • The T-SQL DELETE statement
  • Delete SQL Statement in SQL Server, Oracle and PostgreSQL

SQL Joins

❮ Предыдущий
Далее ❯

SQL JOIN

Предложение JOIN используется для объединения строк из двух или более таблиц на основе
связанный столбец между ними.

Посмотрим на выборку из таблицы «Заказы»:

OrderID идентификатор клиента Дата заказа
10308 2 18 сентября 1996 г.
10309 37 1996-09-19
10310 77 1996-09-20

Затем посмотрите на выбор из таблицы «Клиенты»:

CustomerID ИмяКлиента Имя контакта Страна
1 Альфред Футтеркисте Мария Андерс Германия
2 Ана Трухильо Emparedados y helados Ана Трухильо Мексика
3 Антонио Морено Такерия Антонио Морено Мексика

Обратите внимание, что столбец «CustomerID» в таблице «Заказы» относится к
«CustomerID» в таблице «Клиенты». Отношения между двумя таблицами выше
является столбец «CustomerID».

Затем мы можем создать следующую инструкцию SQL (содержащую
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ),
который выбирает записи, имеющие совпадающие значения в обеих таблицах:

Пример

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Попробуйте сами »

и он выдаст что-то вроде этого:

OrderID ИмяКлиента Дата заказа
10308 Ана Трухильо Emparedados y helados 18.09.1996
10365 Антонио Морено Такерия 27.11.1996
10383 Вокруг Рога 16.12.1996
10355 Вокруг Рога 15.11.1996
10278 Berglunds snabköp 12. 08.1996

Научитесь фильтровать данные в SQL как аналитик данных

Попробуйте практические занятия с пошаговыми инструкциями от эксперта. Попробуйте управляемый проект, созданный в сотрудничестве с Coursera, прямо сейчас!

Начало работы

Различные типы SQL JOIN

Вот различные типы JOIN в SQL:

  • (INNER) JOIN : Возвращает записи, которые имеют совпадающие значения в обеих таблицах
  • ЛЕВОЕ (НАРУЖНОЕ) СОЕДИНЕНИЕ : Возвращает все записи из левой таблицы и соответствующие записи из правой таблицы
  • ПРАВОЕ (ВНЕШНЕЕ) СОЕДИНЕНИЕ : Возвращает все записи из правой таблицы и соответствующие
    записей из левой таблицы
  • ПОЛНОЕ (ВНЕШНЕЕ) СОЕДИНЕНИЕ : Возвращает все записи, если есть совпадение в любом левом
    или правый стол

Проверьте себя с помощью упражнений

Упражнение:

Вставьте недостающие части в Предложение JOIN для объединения двух таблиц Orders и Customers ,
используя поле CustomerID в обеих таблицах в качестве отношения между двумя таблицами.

ВЫБИРАТЬ *
ОТ заказов
ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к клиентам
= 
 ;

Начать упражнение

❮ Предыдущий
Следующий ❯

ВЫБОР ЦВЕТА

Лучшие учебники

Учебное пособие по HTML
Учебное пособие по CSS
Учебное пособие по JavaScript
Учебное пособие
Учебник по SQL
Учебник по Python
Учебник по W3.CSS
Учебник по Bootstrap
Учебник по PHP
Учебник по Java
Учебник по C++
Учебник по jQuery

Лучшие ссылки

Справочник по HTML
Справочник по CSS
Справочник по JavaScript
Справочник по SQL
Справочник по Python
Справочник по W3.CSS
Справочник по Bootstrap
Справочник по PHP
Цвета HTML
Справочник по Java
Справочник по Angular
Справочник по jQuery

Лучшие примеры

0219
Примеры HTML
Примеры CSS
Примеры JavaScript
Примеры инструкций
Примеры SQL
Примеры Python
Примеры W3. CSS
Примеры Bootstrap
Примеры PHP
Примеры Java
Примеры XML
Примеры jQuery

FORUM |
О

W3Schools оптимизирован для обучения и обучения. Примеры могут быть упрощены для улучшения чтения и обучения.
Учебники, ссылки и примеры постоянно пересматриваются, чтобы избежать ошибок, но мы не можем гарантировать полную правильность всего содержания.
Используя W3Schools, вы соглашаетесь прочитать и принять наши условия использования,
куки-файлы и политика конфиденциальности.

Copyright 1999-2023 Refsnes Data. Все права защищены.
W3Schools работает на основе W3.CSS.

SQL Server: Соединения

В этом руководстве по SQL Server объясняется, как использовать СОЕДИНЕНИЯ , ВНУТРЕННЕЕ и ВНЕШНЕЕ СОЕДИНЕНИЯ, в SQL Server (Transact-SQL) с синтаксисом, наглядными иллюстрациями и примерами.

Описание

SQL Server (Transact-SQL) JOINS используются для извлечения данных из нескольких таблиц. SQL Server JOIN выполняется всякий раз, когда две или более таблиц объединяются в операторе SQL.

Существует 4 различных типа соединений SQL Server:

  • ВНУТРЕННЕЕ СОЕДИНЕНИЕ SQL Server (иногда называемое простым соединением)
  • SQL Server LEFT OUTER JOIN (или иногда называемый LEFT JOIN)
  • SQL Server ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (или иногда называемое ПРАВЫМ СОЕДИНЕНИЕМ)
  • SQL Server ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ (или иногда называемое ПОЛНОЕ СОЕДИНЕНИЕ)

Итак, давайте обсудим синтаксис SQL Server JOIN, посмотрим на визуальные иллюстрации SQL Server JOIN и рассмотрим примеры SQL Server JOIN.

INNER JOIN (простое соединение)

Скорее всего, вы уже написали оператор, использующий SQL Server INNER JOIN. Это наиболее распространенный тип соединения. ВНУТРЕННИЕ СОЕДИНЕНИЯ SQL Server возвращают все строки из нескольких таблиц, в которых выполняется условие соединения.

Синтаксис

Синтаксис для INNER JOIN в SQL Server (Transact-SQL):

 столбцы SELECT
ИЗ таблицы1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ таблица2
ON таблица1. столбец = таблица2.столбец;

Визуальная иллюстрация

На этой визуальной диаграмме ВНУТРЕННЕЕ СОЕДИНЕНИЕ SQL Server возвращает заштрихованную область:

ВНУТРЕННЕЕ СОЕДИНЕНИЕ SQL Server вернет записи, где таблица1 и таблица2 пересекаются.

Пример

Вот пример ВНУТРЕННЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБОР
ОТ поставщиков
ВНУТРЕННЕЕ СОЕДИНЕНИЕ заказы
ПО Suppliers.supplier_id = orders.supplier_id;

В этом примере SQL Server INNER JOIN будут возвращены все строки из таблиц поставщиков и заказов, для которых в обеих таблицах поставщиков и заказов имеется совпадающее значение supplier_id.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают ВНУТРЕННИЕ СОЕДИНЕНИЯ:

У нас есть таблица с именем Suppliers с двумя полями (supplier_id и supplier_name). Он содержит следующие данные:

supplier_id имя_поставщика
10000 IBM
10001 Хьюлетт Паккард
10002 Майкрософт
10003 NVIDIA

У нас есть другая таблица с именем заказы с тремя полями (order_id, supplier_id и order_date). Он содержит следующие данные:

order_id идентификатор_поставщика дата_заказа
500125 10000 12.05.2003
500126 10001 13.05.2003
500127 10004 14.05.2003

Если мы запустим инструкцию SQL Server SELECT (содержащую ВНУТРЕННЕЕ СОЕДИНЕНИЕ) ниже:

 SELECT Suppliers.supplier_id, Suppliers.supplier_name, orders.order_date
ОТ поставщиков
ВНУТРЕННЕЕ СОЕДИНЕНИЕ заказы
ПО Suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть так:

идентификатор_поставщика название дата_заказа
10000 IBM 12.05. 2003
10001 Хьюлетт Паккард 13.05.2003

Строки для Microsoft и NVIDIA из таблицы поставщиков будут опущены, поскольку идентификаторы Supplier_id 10002 и 10003 не существуют в обеих таблицах. Строка для 500127 (order_id) из таблицы заказов будет опущена, так как supplier_id 10004 не существует в таблице поставщиков.

Старый синтаксис

В заключение стоит упомянуть, что приведенный выше пример SQL Server INNER JOIN можно переписать с использованием старого неявного синтаксиса следующим образом (но мы по-прежнему рекомендуем использовать синтаксис ключевого слова INNER JOIN):

 ВЫБЕРИТЕ поставщиков .supplier_id, Suppliers.supplier_name, orders.order_date
ОТ поставщиков, заказы
ГДЕ Suppliers.supplier_id = orders.supplier_id;

ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

Другой тип соединения называется ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ SQL Server. Этот тип объединения возвращает все строки из ЛЕВОЙ таблицы, указанной в условии ON и 9. 0283 только те строки из другой таблицы, где соединяемые поля равны (соблюдено условие соединения).

Синтаксис

Синтаксис для LEFT OUTER JOIN в SQL Server (Transact-SQL):

 столбцы SELECT
ИЗ таблицы1
ЛЕВЫЙ [ВНЕШНИЙ] ПРИСОЕДИНЯЙТЕСЬ к таблице2
ON таблица1.столбец = таблица2.столбец;

В некоторых базах данных ключевые слова LEFT OUTER JOIN заменены на LEFT JOIN.

Визуальная иллюстрация

На этой визуальной диаграмме SQL Server LEFT OUTER JOIN возвращает заштрихованную область:

SQL Server LEFT OUTER JOIN вернет все записи из table1 и только те записи из table2 , которые пересекаются с table1 .

Пример

Вот пример ЛЕВОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБОР
ОТ поставщиков
LEFT OUTER JOIN заказы
ПО Suppliers.supplier_id = orders.supplier_id;

Этот пример LEFT OUTER JOIN вернет все строки из таблицы поставщиков и только те строки из таблицы заказов, в которых объединенные поля равны.

Если значение supplier_id в таблице поставщиков не существует в таблице заказов, все поля в таблице заказов будут отображаться как > в наборе результатов.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают ЛЕВЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ:

У нас есть таблица с именем Suppliers с двумя полями (supplier_id и supplier_name). Он содержит следующие данные:

supplier_id имя_поставщика
10000 IBM
10001 Хьюлетт Паккард
10002 Майкрософт
10003 NVIDIA

У нас есть вторая таблица с именем заказы с тремя полями (order_id, supplier_id и order_date). Он содержит следующие данные:

order_id идентификатор_поставщика дата_заказа
500125 10000 12. 05.2003
500126 10001 13.05.2003

Если мы запустим оператор SELECT (который содержит ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ) ниже:
ОТ поставщиков
LEFT OUTER JOIN заказы
ПО Suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть следующим образом:

supplier_id имя_поставщика дата_заказа
10000 IBM 12.05.2003
10001 Хьюлетт Паккард 13.05.2003
10002 Майкрософт <ноль>
10003 NVIDIA <ноль>

Строки для Microsoft и NVIDIA будут включены, поскольку использовалось LEFT OUTER JOIN. Однако вы заметите, что поле order_date для этих записей содержит значение .

RIGHT OUTER JOIN

Другой тип соединения называется SQL Server RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из ПРАВОЙ таблицы, указанной в условии ON и 9.0283 только те строки из другой таблицы, где соединяемые поля равны (соблюдено условие соединения).

Синтаксис

Синтаксис для RIGHT OUTER JOIN в SQL Server (Transact-SQL):

 SELECT columns
ИЗ таблицы1
ПРАВОЕ [ВНЕШНЕЕ] ПРИСОЕДИНЯЙТЕСЬ к таблице2
ON таблица1.столбец = таблица2.столбец;

В некоторых базах данных ключевые слова RIGHT OUTER JOIN заменены на RIGHT JOIN.

Визуальная иллюстрация

На этой визуальной диаграмме SQL Server RIGHT OUTER JOIN возвращает заштрихованную область:

SQL Server RIGHT OUTER JOIN вернет все записи из table2 и только те записи из table1 , которые пересекаются с table2 .

Пример

Вот пример ПРАВОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 SELECT orders. order_id, orders.order_date, Suppliers.supplier_name
ОТ поставщиков
Заказы RIGHT OUTER JOIN
ПО Suppliers.supplier_id = orders.supplier_id;

Этот пример RIGHT OUTER JOIN вернет все строки из таблицы заказов и только те строки из таблицы поставщиков, в которых объединенные поля равны.

Если значение supplier_id в таблице заказов не существует в таблице поставщиков, все поля в таблице поставщиков будут отображаться как > в наборе результатов.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают RIGHT OUTER JOINS:

У нас есть таблица с именем Suppliers с двумя полями (supplier_id и supplier_name). Он содержит следующие данные:

supplier_id имя_поставщика
10000 Яблоко
10001 Гугл

У нас есть вторая таблица с именем заказы с тремя полями (order_id, supplier_id и order_date). Он содержит следующие данные:

order_id идентификатор_поставщика дата_заказа
500125 10000 12.08.2013
500126 10001 13.08.2013
500127 10002 14.08.2013

Если мы запустим инструкцию SELECT (содержащую ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ) ниже:
ОТ поставщиков
Заказы RIGHT OUTER JOIN
ПО Suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть следующим образом:

order_id дата_заказа имя_поставщика
500125 12.08.2013 Яблоко
500126 13.08.2013 Гугл
500127 14. 08.2013 <ноль>

Строка для 500127 (order_id) будет включена, поскольку использовалось ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Однако вы заметите, что поле supplier_name для этой записи содержит значение .

FULL OUTER JOIN

Другой тип соединения называется FULL OUTER JOIN SQL Server. Этот тип соединения возвращает все строки из таблицы LEFT-hand и RIGHT-hand таблицы с нулевыми значениями в местах, где условие соединения не выполняется.

Синтаксис

Синтаксис FULL OUTER JOIN в SQL Server (Transact-SQL):

 ВЫБЕРИТЕ столбцы
ИЗ таблицы1
ПОЛНОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ таблица2
ON таблица1.столбец = таблица2.столбец;

В некоторых базах данных ключевые слова FULL OUTER JOIN заменены на FULL JOIN.

Визуальная иллюстрация

На этой визуальной диаграмме SQL Server FULL OUTER JOIN возвращает заштрихованную область:

SQL Server FULL OUTER JOIN возвращает все записи из table1 и table2 .

Пример

Вот пример ПОЛНОГО ВНЕШНЕГО СОЕДИНЕНИЯ в SQL Server (Transact-SQL):

 ВЫБЕРИТЕ Suppliers.supplier_id, Suppliers.supplier_name, orders.order_date
ОТ поставщиков
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
ПО Suppliers.supplier_id = orders.supplier_id;

В этом примере FULL OUTER JOIN будут возвращены все строки из таблицы поставщиков и все строки из таблицы заказов, и всякий раз, когда условие соединения не выполняется, будут расширены на эти поля в результирующем наборе.

Если значение supplier_id в таблице поставщиков не существует в таблице заказов, все поля в таблице заказов будут отображаться как > в наборе результатов. Если значение supplier_id в таблице заказов не существует в таблице поставщиков, все поля в таблице поставщиков будут отображаться как в результирующем наборе.

Давайте посмотрим на некоторые данные, чтобы объяснить, как работают ПОЛНЫЕ ВНЕШНИЕ СОЕДИНЕНИЯ:

У нас есть таблица с именем Suppliers с двумя полями (supplier_id и supplier_name). Он содержит следующие данные:

supplier_id имя_поставщика
10000 IBM
10001 Хьюлетт Паккард
10002 Майкрософт
10003 NVIDIA

У нас есть вторая таблица с именем заказы с тремя полями (order_id, supplier_id и order_date). Он содержит следующие данные:

идентификатор_заказа идентификатор_поставщика дата_заказа
500125 10000 12.08.2013
500126 10001 13.08.2013
500127 10004 14.08.2013

Если мы запустим оператор SELECT (который содержит ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ) ниже:

 ВЫБЕРИТЕ Suppliers. supplier_id, Suppliers.supplier_name, orders.order_date
ОТ поставщиков
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
ПО Suppliers.supplier_id = orders.supplier_id;

Наш результирующий набор будет выглядеть следующим образом:

supplier_id имя_поставщика дата_заказа
10000 IBM 12.08.2013
10001 Хьюлетт Паккард 13.08.2013
10002 Майкрософт <ноль>
10003 NVIDIA <ноль>
<ноль> <ноль> 14.08.2013

Строки для Microsoft и NVIDIA будут включены, поскольку использовалось ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ. Однако вы заметите, что поле order_date для этих записей содержит значение .