آشنایی با انواع دستور Join در SQL server

دستور join در SQL server از موهبت‌ هایی است که می‌توان در Relational Data Base به کار گرفت. تقریباً بعید است که جدول‌های دیتابیس ما همیشه آنقدر کامل باشند که نیازی به واکشی از چند جدول دیگر وجود نداشته باشد. دستور Join به سادگی راه ترکیب‌ ستون‌ها و ردیف‌های این جدول‌ها را برایمان هموار می‌کند. پس اگر می‌خواهید با انواع دستور Join آشنا شوید، مثال‌های کاربردی این دستور را ببینید و به‌کارگیری آن را بیاموزید؛ تا انتهای مطلب همراه ما باشید. 

دستور join در SQL server چیست؟

دستور جوین در پایگاه‌داده‌های Relational به ما امکان می‌دهد ستون‌ها و ردیف‌ها را به یکدیگر متصل و ترکیب کنیم. این ویژگی در مواردی که داده‌های مربوط به یک پروژه در جدول‌های متعددی ذخیره شده‌اند، بسیار کاربردی است. فرض کنید در یک پروژه لازم است داده‌های مربوط به تعداد سفارش، موقعیت مکانی و اقلام درخواستی مشتریان را در اختیار تیم مارکتینگ قرار دهید. در صورتی که هر یک از این داده‌ها در یک جدول مجزا ذخیره شده‌ باشند، شما باید داده‌ها را از سه جدول واکشی کنید. به لطف دستور join در SQL server دیگر نیازی به این کار نیست. کافی است بدانید باید چه نوعی از جوین را به کار بگیرید. 

انواع دستور join در SQL server

انواع مختلف دستور join در SQL server بر اساس موقعیت جدول‌ها نسبت به یکدیگر، از هم متمایز می‌شوند. به‌طور کلی جدول‌ها در ۴ موقعیت اصلی می‌توانند با یکدیگر ترکیب شوند:

• Self join: جدول با خودش ترکیب می‌شود! (نگران نباشید، در ادامه خواهیم گفت چطور این اتفاق می‌افتد.)
• Inner Join: در این نوع اتصال داده‌های مشترک بین دو جدول ترکیب می‌شوند.
• Outer Join: در نوع بیرونی جوین با دو حالت Left و Right داده‌های از جدول کناری سمت راست یا چپ (به انتخاب ما) به جدول پیوست می‌شوند. 
• Cross Join: این دستور، هر سطر از جدول اول را با هر سطر از جدول دوم ترکیب می‌کند.

نحوهٔ استفاده از دستور Join در SQL server

برای استفاده از دستور join در SQL server ما با دستور اصلی select سروکار خواهیم داشت. در کوئری Select در بخش From مفهوم Primary Key و در بخش On مفهوم Foreign Key را تعریف می‌کنیم. 

برای مثال، فرض کنید دو جدول به نام‌های “Employees” و “Departments” داریم. جدول “Employees” شامل اطلاعات کارمندان (شامل شماره کارمندی، نام، نام خانوادگی و شماره بخش) و جدول “Departments” شامل اطلاعات بخش‌ها (شامل شماره و نام بخش) است. حالا می‌خواهیم نام کارمندان و نام بخش‌های مرتبط با آن‌ها را با هم ترکیب کنیم. این کار را می‌توان با استفاده از دستور  Select و JOIN انجام داد:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
GO

 اجرای تمام انواع دستور join در SQL server به همین سادگی انجام می‌شود. بسیار خب، حالا وقت آن است که در هر یک از انواع دستور join در SQL server عمیق‌تر شویم. 

۱. دستور Inner join در SQL server

اگر بخواهیم داده‌های مشترک میان دو جدول را ترکیب کنیم، از دستور inner join  استفاده می‌کنیم. این دستور join در SQL server به صورت Select > From> Inner join > on > go اجرا می‌شود. 

با این دستور، می‌توانیم اطلاعات مرتبط از دو جدول را با یکدیگر ترکیب کنیم. برای مثال فرض کنید دو جدول به‌نام‌های  “Professors” و “Courses” داشته‌ باشیم که جدول “Professors” شامل اطلاعات استادان (شامل شناسه استاد، نام استاد و حقوق) و جدول “Courses” شامل اطلاعات دروس (شامل شناسه درس، شناسه استاد و نام درس) است. اگر بخواهیم نام استادان و دروسی که توسط آن‌ها تدریس می‌شود را با هم ترکیب کنیم، INNER JOIN به کارمان می‌آید.  

مثالی از دستور Inner join در SQL server

در بخش پیش، مثال بسیار ساده‌ای برای این دستور join در SQL server را بررسی کردیم. اما بیایید کمی به دنیای واقعی نزدیک‌تر شویم و شرایطی را در نظر بگیریم که جدول‌های Sales.SalesOrderDetail و Production را داریم. حالا می‌خواهیم اطلاعات ۱۰۰ محصول پرفروش را از میان این دو جدول پیدا کنیم. برای این کار:

• ابتدا نام مستعار جدول اول را به SOD و جدول دوم را P تعریف می‌کنیم. 
• سپس شرط‌های Join را در دو ستون اجرا می‌کنیم. 
• رکوردهای برگشتی با 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

در SQL Server، دستور  LEFT OUTER JOIN برای ایجاد ارتباط بین دو جدول استفاده می‌شود که در آن جدول سمت چپ به‌عنوان پایه در نظر گرفته می‌شود. به این معنا که این خروجی این دستور، تمام رکوردهای جدول اول را نمایش می‌دهد، حتی اگر مطابقتی در جدول دوم وجود نداشته باشد. اگر مطابقتی وجود داشته باشد، اطلاعات مربوط به هر دو جدول نمایش داده می‌شود. ساختار دستور LEFT OUTER  JOIN در SQL Server به صورت زیر است:

SELECT *
FROM Table1
LEFT JOIN Table2 ON Table1.Column = Table2.Column;
GO

برای درک بهتر این توضیح به تصویر زیر توجه کنید:

می‌بینید که جدول سمت به صورت کامل وارد شده و از جدول سمت راست، تنها ردیف‌هایی که مطابقت داشته‌اند به خروجی اضافه شده‌اند. 

مثالی از دستور left  outer join در SQL server

برای مثال، فرض کنید دو جدول به نام‌های “Persons” و “Orders” داریم. جدول “Persons” شامل اطلاعات افراد (شامل شناسه فرد، نام و نام خانوادگی) و جدول “Orders” شامل اطلاعات سفارش‌ها (شامل شناسه سفارش و شماره سفارش) است. حالا می‌خواهیم نام و نام خانوادگی افراد و شماره سفارش‌های مرتبط با آن‌ها را با هم ترکیب کنیم. این کار را می‌توان با استفاده از دستور LEFT OUTER JOIN  انجام داد:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT OUTER JOIN Orders ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName;
Go

۳. دستور right outer join در SQL server

این دستور join در SQL server درست شبیه به دستور left Outer  عمل می‌کند، با این تفاوت که این بار جدول سمت راست به‌عنوان مبدا در نظر گرفته می‌شود. 

همانطور که در تصویر می‌بینید، دستور Right join تمامی رکورد‌های که در سمت راست وجود دارد را حفظ می ‌کند و در خروجی نمایش می ‌دهد. در واقع همه مقادیر جدول سمت راست را در خروجی نمایش داده می ‌شود، حتی اگر شرط join هیچ سطر مطابقی در جدول سمت چپ پیدا نکند.

مثالی از دستور Right Outer Join در SQL server

در کد نمونه زیر، ردیف‌های منطبق بین جداول 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

۴. دستور Full Outer Join در SQL server

دستور Full join تمام سطرهای هر دو جدول را بر می‌گرداند ، اگر رکوردی سمت راست و یا چپ جدول بر اثر دستور join ریزش پیدا کند، به این معنی است که با شرط join شما تطبیق نداشته باشد. اما شما می‌ توانید با دستور Full join تمامی رکورد‌های هر دو جدول را در خروجی نمایش دهید.

۵. دستور Equi join در SQL server

اتصال equi رایج ترین شکل اتصال inner join است که در عمل استفاده می‌شود. اگر اتصال شامل یک عملگر برابری باشد، به عنوان مثال. =، سپس یک equi-join است. مثال زیر همه نام‌های ایالتی و StateProvinceIDهای منطبق را برمی‌گرداند.

SELECT DISTINCT A.StateProvinceID,S.Name
FROM Person.Address A
inner join Person.StateProvince S
On A.StateProvinceID=S.StateProvinceID

برای این مثال خروجی کوئری شبیه به جدول زیر خواهد بود:

۶. دستور Self Join در SQL server

بگذارید حقیقتی را به شما بگوییم، در لیست دستور join در SQL server در واقع دستوری به‌نام Self join وجود ندارد. این دستور join در SQL server صرفاً نوعی قرارداد است که در بعضی از پروژه ‌ها می‌تواند مورد استفاده قرار بگیرد. در Self join هر جدول با خودش ترکیب می‌شود. 

مثالی از دستور Self Join در SQL server

در این مثال، ما جدولی به نام HumanResources.Employee را داریم و از تابع Self Join استفاده می‌کنیم. بنابراین می‌توانیم اینطور بگوییم که این جدول را به خودش متصل می‌کنیم. یعنی قصد داریم با استفاده از اطلاعات موجود در همین جدول (شامل شناسه‌ها و نام‌های مدیر و کارمند و عناوین شغلی آن‌ها) ارتباط بین کارمند و مدیر را تعیین کنیم. بنابراین براساس منطق join، دو بار با ستون Person.Contact انطباق را انجام می‌دهیم تا اطلاعات نام و عنوان را بر اساس روابط اصلی کارمند و مدیر جمع‌آوری کنیم. به بیان دیگر بر اساس E.ManagerID = M.EmployeeID شناسه مدیر را با شناسه کارمند مطابقت می‌دهیم. در این کوئری برای تمایز بین داده‌های مربوط به کارمند و مدیر از نام مستعار استفاده کرده‌ایم.

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

به‌ طور خلاصه در این کد اقدامات زیر صورت گرفته است:

1. SELECT: انتخاب ستون‌هایی که می‌خواهیم در خروجی نمایش داده شوند، شامل شناسه‌ها و نام‌های مدیر و کارمند و عناوین شغلی آن‌ها.
2. FROM HumanResources.Employee E: استفاده از جدول Employee با نام مستعار E برای کارمندان.
3. INNER JOIN HumanResources.Employee M: اتصال جدول Employee با خودش با نام مستعار M برای مدیران. این اتصال بر اساس E.ManagerID = M.EmployeeID انجام می‌شود که شناسه مدیر را با شناسه کارمند مطابقت می‌دهد.
4. INNER JOIN Person.Contact E1: اتصال به جدول Contact با نام مستعار E1 برای کسب اطلاعات تماس کارمندان. این اتصال بر اساس E1.ContactID = E.ContactID انجام می‌شود.
5. INNER JOIN Person.Contact M1: اتصال به جدول Contact با نام مستعار M1 برای کسب اطلاعات تماس مدیران. این اتصال بر اساس M1.ContactID = M.ContactID انجام می‌شود.
6. ORDER BY M1.LastName: مرتب‌سازی نتایج بر اساس نام خانوادگی مدیر.

این کوئری اطلاعات مدیر و کارمند را در قالب یک خروجی واحد برمی‌گرداند و به ما اجازه می‌دهد که ساختار سلسله‌مراتبی و روابط بین کارمندان و مدیران را مشاهده کنیم.

۷. دستور Cross Join در SQL server

دستور Cross join حاصل ضرب دکارتی (Cartesian Product) است به این صورت که تصور کنید که شما دوتا جدول دارید بنام A و B و داخل هر کدام از این جداول ‌ها چندین رکورد وجود دارد و دستور Cross join به این صورت عمل می ‌کند n*m می ‌شود و ترکیب مختلف این حاصل ضرب دکارتی می ‌شود cross join نتایج Cross join را می‌ توان با استفاده از دستور WHERE فیلتر کرد که در این صورت، خروجی آن مشابه با خروجی inner join خواهد بود. برای مثال به دستور join در SQL server در کوئری کراس زیر توجه کنید:

SELECT *
FROM Customers AS C
        CROSS JOIN Orders AS O
GO

۸. دستور Composite Join در SQL server

در دستور join در SQL server زمانی از Composite استفاده می‌کنیم که جدول ما بیش از یک کلید اصلی ( اصطلاحاً کلید ترکیبی) داشته باشد. برای درک بهتر کوئری که در ادامه آمده است توجه کنید دو جدول ایجاد شده و تعدادی مقادیر تستی درج شده است در ادامه دستور Composite join استفاده شده است و در بخش ON بیش از دو شرط تعیین شده است. 

DROP TABLE IF EXISTS Composite1, Composite2;
GO
CREATE TABLE Composite1
(
  ID1 INT,
  ID2 INT,
  Family NVARCHAR(50)
);
GO
CREATE TABLE Composite2
(
  ID1 INT,
  ID2 INT,
  Serial INT IDENTITY,
  CheckedDate CHAR(10) DEFAULT GETDATE()
);
GO
INSERT INTO dbo.Composite1
VALUES
  (۱,۱۰,N'احمدی'),(۱,۲۰,N'سعادت'),
  (۲,۱۰,N'پایدار'),(۲,۲۰,N'رضایی');
GO
INSERT INTO dbo.Composite2(ID1, ID2)
VALUES
  (۱,۱۰),(۱,۱۰),(۱,۲۰),(۱,۲۰),
  (۲,۱۰),(۱,۱۰),(۱,۱۰),(۲,۱۰);
GO

بعد از ساخت جدول، با استفاده از کوئری زیر اطلاعات دو جدول را واکشی کرده و نمایش می ‌دهیم. اگر به مقادیر ستون ID1 و ID2 دقت کنید، درک مفاهیم Composite join بهتر خواهد بود.

SELECT * FROM dbo.Composite1;
SELECT * FROM dbo.Composite2;
GO

خروجی این کوئری به شکل زیر خواهد بود:

جمع‌ بندی

در این مقاله انواع مختلف دستور join در SQL server از جمله Self، Inner، Outer و Cross را شناختیم و با مثال‌های کاربردی هریک آشنا شدیم. حالا شما به‌خوبی آگاه هستید که چطور باید از این دستور در حل مسئله‌‌ی خود استفاده کنید. با این دانش به سادگی می‌توانید جدول‌های مختلف دیتابیس‌ را به یکدیگر متصل کنید. اگر حوزهٔ کاری شما به SQL server مرتبط است؛ پیشنهاد می‌کنیم برای شناخت عمیق‌تر کوئری‌ها در دستور جوین، از دوره‌های کوئری‌نویسی کمک بگیرید. اما اگر بعد از خواندن این مقاله هنوز  نقاطی از ابهام در ذهن شما باقی مانده است، برای پاسخ گفتن به سوالات و ابهامات ذهن شما دربارهٔ Join آماده‌ایم، از بخش نظرات با ما در ارتباط باشید.