مفهوم JOIN در SQL (Inner, Left, Right و Full Join)

مفهوم JOIN در SQL

در این مطلب قصد داریم شما را با دستور join در SQL Server آشنا کنیم. در پایگاه داده‌ های رابطه ‌ای (Relational DataBase) داده ‌ها در چندین جدول به صورت منطقی توزیع و نگهداری می ‌شوند. برای بدست آوردن یک مجموعه کامل از داده باید از دستور Join استفاده کنید تا یک مجموعه اطلاعات معنی ‌دار از چندین جدول داده‌ ها را جستجو کنید. در واقع دستور Join برای ترکیب کردن ستون‌ های یک یا چند جدول مورد استفاده قرار می‌ گیرد. تصور کنید که می‌ خواهید اطلاعات مشتریانی را استخراج کنید که تعداد سفارش مشتری و چه اقلامی در سفارش درخواست کرده را بدست بیارید، و هر کدام از این اطلاعات در یک جدول جداگانه ذخیره شده‌ اند، در نتیجه ما برای واکشی این اطلاعات نیاز داریم اطلاعات را از سه جدول واکشی کنیم و این موضوع باعث می ‌شود که از دستورات join استفاده کنیم انواع مختلفی از دستور‌های join را داریم که لیست آن‌ ها در ادامه آمده است. و برای درک هر چه بهتر و بیش ‌تر مطلب دستور Join سعی شده برای هر کدام یک مثال ساده ارائه شود

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

همان ‌طور که پیش ‌از این گفته شد انواع گوناگونی از دستور join در SQL وجود دارد که کاربر با توجه‌به نوع اطلاعاتی که می ‌خواهد از جداول مختلف واکشی کند می ‌تواند از آن‌ ها استفاده کند. دستورات join یکسری تفاوت ریز با یکدیگر دارند و شناخت درست از هرکدام از این دستورات در نوع استفاده و کاربرد‌های آن‌ ها از یکدیگر متمایز کرده است. در ادامه به انواع دستورات join پرداخته و با ارائه مثال مورد بررسی قرار می‌ گیرد.

•  Inner join
•  Left outer join
•  Right outer join
• Full outer join
•  Cross join

برای اینکه از دستورات join استفاده کنیم یک دیتابیس و دو جدول ایجاد کردیم که انواع دستورات join را مورد بررسی قرار بدهیم. برای همین موضوع از کوئری زیر استفاده می‌کنیم.

CREATE DATABASE SQLHintsJoinDemo
GO
USE SQLHintsJoinDemo
GO
--ایجاد جدول مشتری و درج چند رکورد تستی
CREATE TABLE Customers
( CustomerId INT, Name VARCHAR(50) )
GO
INSERT INTO Customers(CustomerId, Name) VALUES(1, ' Shree')
INSERT INTO Customers(CustomerId, Name) VALUES(2, 'Kalpana')
INSERT INTO Customers(CustomerId, Name) VALUES(3,' Basavaraj')
GO
-- ایجاد جدول سفارشات و درج چند رکورد تستی
CREATE TABLE Orders
(OrderId INT, CustomerId INT, OrderDate DateTime)
GO
INSERT INTO Orders(OrderId, CustomerId, OrderDate)
VALUES(100,1,Getdate()-1)
INSERT INTO Orders VALUES(200,4,Getdate())
INSERT INTO Orders VALUES(300,3,Getdate()+1)
GO

دستور inner join در SQL Server

زمانی از دستور join استفاده می ‌شود که بخواهید بین جدوال رکورد‌های مشترک را استخراج کنید. منظور از رکورد‌های مشترک این نیست که پیش خودتان تصور کنید برای مثال یک جدول Customer دارید و یک جدول Orders که در جدول مشتری یک (Primary Key) دارید و همین فیلد را هم در جدول Orders به عنوان کلید خارجی (Foreign key) تعریف کردید، نه منظور این است در فیلد‌های مشترک که بین جداول است را استخراج کنیم. اگر به تصویر زیر نگاه کنید این موضوع را بهتر‌ درک می‌ کنید.
مثال: با استفاده از کوئری زیر لیست مشتریانی که ثبت سفارش داشتند نمایش می ‌دهد

SELECT *
FROM  Customers C
        INNER JOIN Orders O
            ON O.CustomerId = C.CustomerId
GO

توضیحات کوئری بالا: همانظور که در تصویر مشاهده می ‌کنید خروجی کوئری بالا به این صورت است که در جدول Customers یکسری رکورد داریم، آیا مشتری شماره یک در جدول Orders ثبت سفارش داشته اگر به جدول Orders نگاه کنید یک فیلد بنام CustomerID دارید که شماره مشتری یک وجود دارد پس فیلد مشترک بین دو جدول CustomerID است و در خروجی اطلاعات مشتری شماره یک نمایش داده می ‌شود. و برای سایر رکورد‌ها هم این اتفاق می ‌افتد.

دستور Left outer join در SQL Server

دستور LEFT JOIN زمانی استفاده می ‌شود که در شرط join ما صدق نمی‌ کند اما می‌ خواهید حتی مشتریانی که ثبت سفارش نداشته ‌اند را در خروجی نمایش بدید. در کوئری مربوط به Join دیدید که مشتری شماره ۲ در خروجی نمایش داده نشد، اما در کوئری زیر ما برای اینکه لیست تمام مشتریان استخراج کنیم و مشتری شماره دو هم در خروجی نمایش داده شود از دستور Left Join استفاده می ‌کنیم.در واقع دستور left join تمامی رکورد‌های جدول سمت چپ را حفظ می ‌کند.
مثال: با استفاده از کوئری زیر لیست تمای مشتریان را نمایش می‌دهد حتی مشتری که ثبت سفارش نداشته است

SELECT *
FROM  Customers C
        LEFT OUTER JOIN Orders O
            ON O.CustomerId = C.CustomerId
GO

توضیحات کوئری بالا: همانظور که در تصویر بالا مشاهده می ‌کـنید خروجی کوئری دسـتور Left join به این صورت است که در تمامی رکورد جدول Customers را در خروجی نمایش داده است. درسته مشتری شماره ۲ که در جدول Customers داریم ثبت سفارش نداشته و مقادیرش در جدول Orders به صورت Null نمایش داده شده، اما در خروجی کوئری ما به کمک دستور Left join نمایش داده می ‌شود.

دستور Right Outer Join در SQL Server

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

SELECT *
FROM  Customers C
        RIGHT OUTER JOIN Orders O
            ON O.CustomerId = C.CustomerId
GO

توضیحات کوئری مروبط به دستور Right join: همانظور که در تصویر بالا مشاهده می ‌کـنید خروجی کوئری دسـتور Right join به این صورت است که در تمامی رکورد جدول Orders را در خروجی نمایش داده است. طبق داده های جداول نمایشی ما ، سفارش ثبت شده با Orderid 200 داریم، اما اطلاعات مشتری با CustomerId شماره ۴ در جدول Customers نداریم. و اطلاعات دو مشتری در جدول Customers موجود است. که CustomerId این دو مشتری شماره ۱ و۳ می ‌باشد. و برای سفارش مشتری با CustomerId شماره ۴ دارای مقادیر Null در خروجی خواهد بود.

دستور Full Outer Join در SQL Server

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

SELECT *
    FROM Customers C
            FULL OUTER JOIN Orders O
                ON O.CustomerId = C.CustomerId
GO

دستور Cross Join

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

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

دستور Self join در SQL Server

این دستور موجب می ‌شود که یک جدول با خودش join شود. برای اینکه مفهوم Self join را درک کنیم در ادامه یک جدول کارمند ایجاد می‌ کنیم و برای هر کارمند یک مدیر تعیین می‌ کنیم و سپس با استفاده از دستور Self join مثالی در ادامه ارائه می‌ کنیم که مدیر هر کارمند چه شخصی است. فقط به این نکته توجه داشته باشید در SQL Server ما دستوری بنام Self join نداریم صرفاً یک نوع قراردادی است که در بعضی از پروژه ‌ها می ‌تواند مورد استفاده قرار بگیرد.
مثال: با استفاده از کوئری زیر یک جدول کارمند ایجاد می‌کنیم و برای هر کارمند یک مدیر هم مشخص می‌کنیم با استفاده از دستور Self join  جدول کارمند را با خودش join می‌زنیم

CREATE TABLE Employee
(EmployeeId INT, Name NVARCHAR(50), ManagerId INT)
GO
INSERT INTO Employee VALUES(1,'Shree',1)
INSERT INTO Employee VALUES(2,'Kalpana',1)
INSERT INTO Employee VALUES(3,'Basavaraj',2)
INSERT INTO Employee VALUES(4,'Monty',2)
GO

با استفاده از دستور زیر جدول کارمند(Employee) را با خودش join می‌زنیم که اصطلاحاً به این روش Selfjoin گفته می‌شود.

SELECT E.EmployeeId,
       E.Name 'Employee Name',
 M.Name 'Manager Name'
FROM dbo.Employee E
        INNER JOIN Employee M
            ON M.EmployeeId = E.ManagerId
GO

دستورComposite join

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

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

مثال: با استفاده از کوئری زیر بین جدول Composite1 و جدول Composite2 دستور Join نوشته‌ایم که در بخش on بیش از یک شرط گذاشتیم که در صورتی کوئری نوشته خروجی خواهد داد که مقادیر ستون ID1 در جدول Composite1 برابر باشد با ID1 جدول Comsoite2 و همچنین مقادیر ستون ID2 و مقادیر ستون ID2 اگر برابر باشد کوئری نوشته خروجی خواهد داد

SELECT
  C1.Family, C2.Serial
FROM dbo.Composite1 AS C1
JOIN dbo.Composite2 AS C2
  ON  C1.ID1 = C2.ID1
  AND C1.ID2 = C2.ID2;
GO

نتیجه‌گیری

در این مطلب سعی کردیم شما را با مفهوم انواع join در SQL Server آشنا کنیم اگر می ‌خــواهید با مباحث تکـمیلی و مثال متنوع از مباحث join را بیامـوزید می ‌تـوانیـد «دوره کوئری نویسی مقدماتی» که در مجموعه نیک ‌آموز تهیه شده است استفاده کنید.