درس دهم – بررسی JOIN در SQL

یکی از مهم‌ترین دستوراتی که در SQL می‌توان نام برد، دستور JOIN است که این دستور اساساً برای ترکیب و بازیابی داده‌ها از دو یا چند جدول مورد استفاده قرار می‌گیرد. در یک پایگاه‌داده، رابطه‌ای در دنیای واقعی، داده‌ها در تعداد زیادی جداول ساختار می‌یابند و به این دلیل، دائماً باید به این جداول متصل شد. چهار نوع اساسی عضویت در SQL Server وجود دارد: Inner، Outer، Self و Cross join. برای اینکه یک مرور سریع در مورد همه­ی این پیوستن‌ها داشته باشید در زیر آنها را بررسی می‌کنیم:

در شکل ۶۵-۳ یک شمای کلی از نحوه­ی ارتباط دستورات JOIN را مشاهده می‌کنید که هر کدام توضیح مخصوص به خود را دارند.

شکل ۶۵-۳ بررسی دستور JOIN

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

در ادامه نوع‌های مختلف SQL JOIN را که در شکل ۴۳-۳ مشخص شده است را با هم بررسی می‌کنیم:

۱- SQL inner join

این دستور یکی از ساده‌ترین دستورات JOIN است که نتیجه‌ی خروجی آن، سطری از هر دو جدول است که در آن شرایط پیوستگی یکی باشد؛ در شکل ۶۶-۳ شمای کلی این دستور را مشاهده می‌کنید.

شکل ۶۶-۳SQL inner join

دستورات کلی  Inner Joinبه‌صورت زیر است:

SELECT ColumnList from LeftTable L
INNER join  RightTable R
ON L.Column=R.Column

۲- SQL self join

این دستور باعث می‌شود، یک جدول با خودش Join شود که در ادامه، نحوه­ی کار آن را بررسی خواهیم کرد.

شکل ۶۷-۳ نمای کلی دستور به‌صورت زیر است:

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition

شکل ۶۷-۳  SQL self join

۳- SQL cross join

این نوع JOIN حاصل‌ضرب دکارتی سطرهای تعیین­شده در دستورjoin  را در خروجی نمایش می‌دهد. این دستور، سطرهایی را تولید می‌کند که ترکیبی از سطر جدول اول و دوم است که نمای کلی آن را در شکل ۶۸-۳ مشاهده می‌کنید.

شکل ۶۸-۳ SQL cross Join

• SQL outer join

این دستور شامل سه دستور LEFT OUTER, RIGHT OUTER, FULL است که هرکدام برای کار خاص طراحی شده‌‎اند.

دستور LEFT OUTER JOIN

شکل کلی این دستور به‌صورت زیر است:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

این دستور، تمام اطلاعات را از جدول سمت چپ، یعنی جدول اول و اطلاعات منطبق را از جدول دوم در خروجی چاپ می‌کند و اگر هم عدم تطابق ایجاد شود، نتیجه‎ی صفر را از جدول سمت راست برمی‌گرداند؛ در شکل ۶۹-۳ این موضوع مشخص شده است.

شکل ۶۹ -۳ SQL Full outer

مثال دستور INNER JOIN

برای اینکه بیشتر با این دستورات آشنا شویم یک مثال را با هم بررسی می‌کنیم؛ برای شروع نیاز به دو جدول داریم تا بتوانیم عملیات JOIN را بر روی آنها انجام دهیم.

جدول ۹-۳  Customers

جدول ۱۰- ۳ Order

دو جدول ۹-۳ و ۱۰-۳ را مشاهده می‌کنید که باید در SQL آنها را ایجاد کنیم تا بتوانیم عملیات Join را بر روی آنها انجام دهیم.

همان ‌طور که در شکل ۷۰-۳ مشاهده می‌کنید، دو جدول مورد نظر در SQL ایجاد شده‌اند.

شکل ۷۰-۳  ایجاد جدول

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

SELECT Customers.[Customer Name],Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.[Customer Name];

در خط اول با دستور SELECT، ستون‌های خروجی را که قرار است چاپ شوند را مشخص می‌کنیم که ستون Customer Name از جدول Customers و ستون OrderID از جدول Orders چاپ خواهد شد.

در خط دوم جدول Customers انتخاب می‌شود و در خط سوم مشخص می‌کنیم که باید با جدول Orders ارتباط INNER JOIN برقرار کند؛ در خط چهارم باید شرط برقراری ارتباط را مشخص کنیم که با دستور ON این کار را انجام می‌دهیم و بعد از آن، CustomerID از جدول Customers را با ستون CustomerID از جدول Orders را مساوی قرار می‌دهیم؛ این بدان معنا است که اگر در این دو ستون، عبارت مساوی هم قرار داشته باشد، آن سطر در خروجی چاپ خواهد شد؛ در خط آخر نیز با دستور ORDER BY، ستون Customer Name را منظم می‌کنیم تا در خروج، نمای بهتری داشته باشد.

اگر به شکل ۷۱-۳ توجه کنید، مشاهده خواهید کرد سه سطر در خروجی اپ شده است و آن هم به‌ دلیل برابر بودن اعداد در ستون‌های Customer ID در هر دو جدول است.

شکل ۷۱-۳ بررسی دستور JOIN

نکته: دستور JOIN و INNER JOIN یک کار را انجام می‌دهند، اما دستور INNER JOIN دارای عملکرد بهتری نسبت به دستور JOIN است و کار را در سریع‌ترین زمان ممکن انجام می‌دهد.

مثال دستور SELF JOIN

همان ‌طور که گفتیم این دستور با خودش ارتباط برقرار می‌کند؛ برای تست این موضوع از جدول Customers استفاده می‎کنیم و دستور زیر را اجرا می‌کنیم:

SELECT A.[Customer Name] AS CustomerName1, B.[Customer Name] AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

در دستور بالا و در خط اول، سه ستون در خروجی چاپ خواهد شد که دستور AS، یک ستون جدید در خروجی نمایش می‎دهد؛ در قسمت FROM، یک جدول به دو قسمت تبدیل شده است که یکی Customers A و دیگری Customers B است؛ در قسمت WHERE شرطی قرار دادیم و گفتیم که در صورتی‌ که A.CustomerID برابر نباشد با B.CustomerID و همچنین شهر جدول A با شهر جدول B یکی باشد شرط درست است و باید در خروجی چاپ شود که در خط آخر نیز طبق ستون A.City خروجی منظم خواهد شد.

در شکل ۷۲-۳ خروجی دستورات بالا را مشاهده می‌کنید که سه ستون ایجاد و خروجی طبق شرطی که قرار دادیم، نمایش‌داده شده است.

شکل ۷۲-۳  بررسی دستور SELF JOIN

مثال دستور CROSS JOIN

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

SELECT *
FROM Customers
CROSS JOIN Orders;

در دستور بالا با دستور SELECT * باید همه­ ی ستون‎‌های جدول Customers در خروجی نمایش داده شوند، اما در خط آخر با دستور CROSS JOIN، تمام سطر‌های جدول اول را ضرب در جدول Orders می‌کنیم که خروجی مانند شکل ۷۳-۳ خواهد شد.

همان ‌طور که در شکل ۷۳-۳ مشاهده می‌کنید، هر ‎کدام از سطر‌های جدول Customers ضرب در هر ‌کدام از سطر‌های جدول Orders خواهد شد که نتیجه‌ی آن یک جدول بزرگ از اطلاعات است.

شکل۷۳-۳ خروجی دستور CROSS JOIN

مثال دستور OUTER JOIN

این دستور همان ‌طور که گفتیم از چندین قسمت تشکیل شده:LEFT OUTER, RIGHT OUTER, FULL:OUTER که آنها را با هم بررسی می‌کنیم.

مثال دستور LEFT JOIN

دستور زیر را اجرا کنید:

SELECT Customers.[Customer Name], Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.[Customer Name];

در دستور بالا، در خط اول دستور SELECT ستون Customer Name را از جدول Customers و ستون OrderID را از جدول Orders در خروجی چاپ خواهد کرد؛ در خط دوم جدول Customers انتخاب و در خط سوم با استفاده از دستور LEFT JOIN می‌گوییم که ستون Customers.CustomerID برابر با Orders.CustomerID باشد؛ این دستور تمام اطلاعات جدول Customers را در صورت تطبیق در خروجی چاپ خواهد کرد و اگر در جدول دوم، یعنی Orders مقداری وجود نداشته باشد، کلمه‌ی null را برمی‌گرداند.

همان ‌طور که در شکل ۷۴-۳ مشاهده می‌کنید، خروجی به‌ درستی نمایش داده شده است.

شکل ۷۴-۳ بررسی دستور OUTER JOIN

مثال دستور RIGHT JOIN

برای انجام این دستور، دو جدول ۱۱-۳ و ۱۲-۳ را در SQL ایجاد کنید:

جدول ۱۱-۳ Orders

جدول ۱۲-۳ Employees

بعد از ایجاد جدول باید دستورات زیر را اجرا کنید:

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

در خط اول، سه ستون از دو جدول مشخص شده است و در ادامه به جدول Orders و جدول Employess متصل می‌شود که شرط RIGHT JOIN در جدول Employess پیاده‌سازی شده است، با این شرایط که Orders.EmployeeID = Employees.EmployeeID باشد؛ در شکل ۷۵-۳ این موضوع را مشاهده می‌کنید.

شکل ۷۵-۳ بررسی دستور RIGHT JOIN

مثال دستور Full JOIN

این دستور به طور کامل، خروجی هر دو جدول را طبق شرطی که در دستور زیر مشاهده می‌کنید، در خروجی شکل ۷۶-۳ نمایش می‌دهد.

SELECT Employees.FirstName, Orders.OrderID
FROM Employees
FULL OUTER JOIN Orders ON Employees.EmployeeID=Orders.CustomerID
ORDER BY Orders.EmployeeID;

شکل ۷۶-۳ بررسی دستور Full JOIN