مثال JOIN در SQL Server

نوشته شده توسط: تیم فنی نیک آموز

۰۳ اسفند ۱۴۰۰

زمان مطالعه: 9 دقیقه

مسئله

من در Microsoft SQL Server تازه کار هستم و می‌خواهم در مورد گزینه‌های join برای پایگاه داده رابطه‌ای بیاموزم. تمام گزینه‌های مربوط به join در SQL Server چیست؟ اهمیت هر یک از گزینه‌ها چیست؟ در مورد تفاوت‌ها و سینتکس کمی گیج شده‌ام، می‌توانید چند مثال و توضیح بدهید؟ آیا join ها فقط برای عبارات SELECT هستند؟

برای آشنایی با سینتکس join در SQL Server این مقاله را مطالعه کنید.

راه‌حل

join میان جداول برای به دست آوردن داده‌های مورد نیاز برای یک کوئری، اسکریپت یا stored procedure یک مفهوم کلیدی است که در مورد توسعه SQL Server یاد می‌گیرید. به طور خلاصه، join ها معمولاً در عبارت FROM یک جدول یا view برای دستورات SELECT، INSERT…SELECT، SELECT…INTO، UPDATE و DELETE انجام می‌شود. در نسخه‌های قبلی SQL Server، منطق join می‌توانست در عبارت WHERE با عملگر (=) برای INNER JOIN، با عملگر (*=) برای LEFT OUTER JOIN، با عملگر (=*) برای RIGHT OUTER JOIN، به کار رود اما در نسخه‌های جدید پشتیبانی از این سینتکس ها کاهش یافته است و بهترین روش در SQL Server برای join استفاده از سینتکس‌های ذکر شده در مثال‌های این مقاله است.

قبل از اینکه وارد کد شویم، اجازه دهید برخی اطلاعات پایه در مورد انواع JOIN در SQL Server را شرح دهیم:

SQL INNER JOIN: ردیف‌های بین دو جدول مشخص شده در عبارت INNER JOIN را بر اساس یک یا چند ستون دارای کلیدهای مرتبط بین دو جدول مطابقت دهید. ترجیحاً اتصال مبتنی بر یکپارچگی ارجاعی است که رابطه بین جداول را برای اطمینان از یکپارچگی داده‌ها اعمال می‌کند.
برای افزودن کمی توضیح بیشتر به تعاریف اصلی بالا، به طور کلی گزینه INNER JOIN رایج ترین join مورد استفاده در برنامه‌ها و یا کوئری‌ها در نظر گرفته می‌شود. اگر چه در بیشتر محیط‌ها این مورد صادق است، اما واقعاً به طراحی پایگاه داده، یکپارچگی ارجاعی و داده‌های مورد نیاز برای برنامه بستگی دارد. به این ترتیب، برای درک داده‌های کوئری وقت بگذارید و سپس گزینه join مناسب را انتخاب کنید.
اگر چه بیشتر منطق join بر اساس مقادیر تطبیق بین دو ستون مشخص شده است، می‌توان منطق را با استفاده از بزرگتر، کمتر، نامساوی و غیره نیز به کار برد.
SQL LEFT OUTER JOIN: بر اساس دو جدول مشخص شده در عبارت join، تمام داده‌ها از جدول سمت چپ برگردانده می‌شوند. در جدول سمت راست، داده‌های منطبق برگردانده می‌شود و به جای ردیف‌هایی از جدول سمت چپ که ردیفی منطبق در جدول سمت راست ندارند، مقادیر NULL برگشت داده می‌شود.
مورد دیگری که باید در نظر داشت این است که منطق OUTER JOIN LEFT و RIGHT OUTER JOIN عکس یکدیگر هستند. بنابراین می‌توانید ترتیب جداول را در دستور join تغییر دهید و این دو منطق را با هم جا به جا کنید و همان نتایج را بگیرید.
SQL RIGHT OUTER JOIN: بر اساس دو جدول مشخص شده در عبارت join، تمام داده‌ها از جدول سمت راست بازگردانده می‌شوند. در جدول سمت چپ، داده‌های منطبق برگردانده می‌شود و به جای ردیف‌هایی از جدول سمت راست که ردیفی منطبق در جدول سمت چپ ندارند، مقادیر NULL برگشت داده می‌شود.
SQL Self Join: در این شرایط، یک جدول با خودش join می‌شود. جدول دوم با نام مستعار مشخص می‌شود.
SQL FULL JOIN: بر اساس دو جدول مشخص شده در join، تمام داده‌ها بدون توجه به داده‌های مطابقت از هر دو جدول برگردانده می‌شوند. ردیف‌هایی که در جدول دیگر ردیف منطبق نداشته باشند، مقادیر NULL به جای ردیف معادل برگشت داده می‌شود.

در ادامه نمونه‌هایی از پایگاه داده نمونه AdventureWorks را که برای SQL Server در دسترس است مرور می‌کنیم تا عبارات SQL را برای هر نوع join ارائه کنیم.

مثالی از INNER JOIN در SQL Server

در کوئری زیر، یک SQL INNER JOIN بین جداول Sales.SalesOrderDetail و Production.Product داریم. نام مستعار جداول عبارتند از: SOD برای Sales.SalesOrderDetail و P برای Production.Product شرط Join بر اساس ردیف‌های منطبق در ستون‌های SOD.ProductID و P.ProductID است. رکوردهای برگشتی با SOD.UnitPrice بیشتر از ۱۰۰۰ فیلتر می‌شوند. در نهایت، مجموعه نتایج به ترتیب نزولی با دستور ORDER BY و فقط ۱۰۰ محصول اول با دستور 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

مثالی از LEFT OUTER JOIN در SQL Server

در کوئری زیر دو مفهوم را با هم ترکیب می‌کنیم تا نشان دهیم که می‌توان بیش از دو جدول را در یک دستور SELECT با هم join کرد و همچنین بیش از یک نوع join را می‌توان در یک دستور SELECT استفاده کرد. در کد نمونه زیر، ردیف‌های منطبق بین Person.Contact و Sales را بازیابی می‌کنیم.

جداول SalesPerson به همراه تمام ردیف‌های داده منطبق در جدول Sales.SalesPerson و جدول Sales.SalesTerritory برگشت داده می‌شود. برای رکوردهایی که در جدول Sales.SalesPerson وجود دارند و در جدول Sales.SalesTerritory نیستند، مقادیر NULL برای ستون‌های Sales.SalesTerritory برگردانده می‌شوند. علاوه بر این، این کد از دو ستون برای مرتب‌سازی داده‌ها استفاده می‌کند، ستون‌های ST.TerritoryID و C.LastName در دستور order by استفاده می‌شوند.

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

مثالی از RIGHT OUTER JOIN در SQL Server

این مثال نشان می‌دهد که چگونه 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 نیستند، مقادیر NULL برای ستون‌های Sales.SalesTerritory برگردانده می‌شوند.

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

مثالی از Self JOIN در SQL Server

در این مثال، ما در واقع در حال join جدول HumanResources.Employee با خودش هستیم. ما این کار را برای به دست آوردن اطلاعات مربوط به رابطه کارمند و مدیر در جدول HumanResources.Employee انجام می‌دهیم. در ارتباط با منطق join، دو بار با ستون Person.Contact انطباق را انجام می‌دهیم تا اطلاعات نام و عنوان را بر اساس روابط اصلی کارمند و مدیر جمع‌آوری کنیم. علاوه بر این، مفهوم جدید دیگری که در این کوئری معرفی شده است که نام مستعار برای هر یک از ستون‌ها است. اگر چه می‌توانستیم این کار را در مثال‌های قبلی نیز انجام دهیم، اما در این کوئری برای تمایز بین داده‌های مربوط به کارمند و مدیر از نام مستعار استفاده نمودیم.

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

مثالی از CROSS JOIN در SQL Server

لطفاً هنگام اجرای یا اصلاح این کوئری در هر محیط پایگاه داده 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

مثالی از FULL OUTER JOIN در SQL Server

در این مثال ، منطق مثال LEFT OUTER JOIN در بالا را تغییر دادیم و سینتکس LEFT OUTER JOIN را به FULL OUTER JOIN تبدیل کردیم. در این شرایط، مجموعه نتایج مانند LEFT OUTER JOIN است که در آن ما همه داده‌ها را بین هر دو جدول برمی‌گردانیم.

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