Примеры использования 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