آموزش JOIN در SQL

اگر فکر می کنید که می دانید JOIN در SQL به چه طریقی انجام می شود این مقاله را بخوانید. اگر فکر نمی کنید که می دانید JOIN در SQL به چه طریقی انجام می شود این مقاله را حتما بخوانید.

همان طور که در مقاله پیشین گفتیم، طبق فرایند اجرای منطقی کویری، اولین گام پردازش دستور FROM است. FROM در ابتدایی ترین حالت، اشاره کننده به یک جدول است.

بیایید با هم مراحل اجرای کویری زیر را طبق Logical Query Processing بررسی کنیم:

SELECT 
	*
FROM
	Person.Person

1. دستور FROM اجرا می شود: تمامی رکورد ها و فیلد های جدول Person.Person، خوانده شده و به مرحله بعد منتقل می شود.
2. سپس Select List بررسی شده و فیلد های مشخص شده پردازش و نمایش داده می شود.
   1. توضیح: علامت * (Asterisk) که معمولا به صورت استار (Star) تلفظ می شود، به معنای تمامی فیلد هایی است که در مرحله SELECT به آن دسترسی داشته، می باشد.
   2. از این علامت برای جلوگیری از نوشتن نام تمامی فیلد ها استفاده می کنیم.

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

برای مثال در پایگاه داده Adventure Works، اطلاعات اشخاص در جدول Person.Person نگهداری می شود. اما اطلاعات تکمیلی کارمندان که زیرمجموعه ای از اشخاص می باشد در یک جدول دیگر به نام HumanResources.Employee ذخیره شده است.

اجازه دهید که با یک مثال ساده کنیم:

 فرض کنید که پایگاه داده ما دارای دو جدول زیر باشد: اطلاعات کارمندان و شهر محل خدمت آن ها.

اگر بخواهیم اطلاعات کارمند را که قسمتی از آن در جدول Employee و قسمت دیگر در جدول City پخش شده است را در کنار هم داشته باشیم، راهکار چیست؟

عملگرJOIN:

عملگرهای JOIN، یکی از عملگرهایی است که در FROM می توان از آن استفاده کرد و وظیفه اصلی آن در کنار هم قرار دادن رکورد های دو جدول (مجموعه داده) است. اما JOIN چگونه این کار را می کند؟

عملگر JOIN، انواع مختلفی داشته و در کامل ترین حالت دارای 3 گام درونی است:

گام اول: ضرب دکارتی (Cartesian Product)

جداول Employee و City را به صورت دو مجموعه زیر در نظر بگیرید:

Employee = { (1, Ali, 3), (2, Omid, 4), (3, Reza, 5) }

City = { (3, Tehran), (4, Shiraz), (5, Tabriz) }

اجرای ضرب دکارتی در میان تمامی انواع JOIN مشترک بوده و وظیفه آن تکرار تمامی اعضای (رکورد) مجموعه داده دوم به ازای هر یک از رکورد های مجموعه داده اول می باشد.

• اگر مجموعه اول M رکورد و مجموعه دوم N رکورد داشته باشد، مجموعه حاصل از ضرب دکارتی M * N رکورد خواهد داشت.
• اگر مجموعه اول M ستون و مجموعه دوم N ستون داشته باشد، مجموعه حاصل از ضرب دکارتی M + N ستون خواهد داشت.

در SQL برای اجرای ضرب دکارتی از CROSS JOIN استفاده می کنیم:

SELECT 
	*
FROM
	Employee
CROSS JOIN
	City

این کویری 9 رکورد بر می گرداند. اما تمامی این رکورد ها، برای ما مطلوب نیست:

اگر کمی به رکورد های منتخب نگاه کنید، متوجه می شوید که همه آن ها دارای یک ویژگی مشترک هستند. شناسه (ID) های مرتبط با شهر از هر دو جدول در این رکورد ها برابر هستند:

Employee.CityID = City.ID

برای فیلتر کردن این رکوردها، نیاز به گام دوم داریم.

گام دوم: ارزیابی شرط انتخاب

در این گام تمامی رکورد های حاصل از ضرب دکارتی، بر اساس یک شرط ارزیابی شده و رکورد هایی که موافق با آن هستند، منتخب شده و به گام بعدی منتقل می شوند.

در SQL برای اجرای این فرایند از عملگر INNER JOIN استفاده می کنیم:

SELECT 
	*
FROM
	Employee
INNER JOIN
	City
ON
	Employee.CityID = City.ID

عملگر INNER JOIN هر دو گام ضرب دکارتی و ارزیابی شرط انتخاب را انجام می دهد. ON و شرط مقابل آن اجباری بوده و قابل حذف کردن نیست.

حالا که با CROSS JOIN و INNER JOIN آشنا شده ایم، اجازه دهید مساله را کمی پیچیده تر کنیم:

فرض کنید که رکورد های دو جدول Employee و City به شکل زیر تغییر کند:

همان طور که مشاهده می کنید اطلاعات کامل نبوده و اطلاعات شهر 3 (شهری که Ali در آن خدمت می کند) موجود نیست.

اگر نتیجه مطلوب ما به شکل زیر باشد:

چگونه می توانیم کویری ای بنویسیم که به نتیجه دلخواه برسیم؟

برای توضیح گام سوم ابتدا نیاز داریم که به تعریف سطر خارجی (Outer Row) بپردازیم.

سطر خارجی (Outer Row):

سطر خارجی، سطری از یک جدول (مجموعه داده) است که در ارزیابی گام دوم همواره FALSE شده است.

بنابراین سطر های خارجی دو جدول به شرح زیر خواهد بود:

گام سوم: اضافه کردن سطرهای خارجی

اگر بین دو جدول بالا، گام های اول و دوم را اجرا کنیم؛ نتیجه زیر حاصل خواهد شد:

پس خروجی ما تنها یک رکورد خواهد داشت و اطلاعات مربوط به Ali در گام دوم حذف خواهد شد. زیرا پس از ضرب دکارتی هیچ یک از رکوردهای مرتبط با Ali شرط Employee.CityID = City.ID را دارا نبوده و در گام دوم حذف می شوند.

حال گام سوم، یعنی اضافه کردن رکورد های خارجی را اجرا می کنیم:

همان طور که مشاهده می کنید، سطرهای خارجی هر جدول مستقل از دیگری به مجموعه داده پس از گام دوم اضافه شده است. هنگام اضافه کردن سطر های خارجی یک جدول، مقایر مرتبط با جدول دوم خالی (NULL) در نظر گرفته می شود.

در SQL عملگر های OUTER JOIN هر سه گام ضرب دکارتی، ارزیابی شرط انتخاب و اضافه کردن سطر های خارجی را انجام می دهند.

• اگر بخواهیم سطرهای خارجی جدول سمت چپ اضافه شود از LEFT OUTER JOIN استفاده می کنیم.
• اگر بخواهیم سطرهای خارجی جدول سمت راست اضافه شود از RIGHT OUTER JOIN استفاده می کنیم.
• اگر بخواهیم سطرهای خارجی هر دو جدول اضافه شود از FULL OUTER JOIN استفاده می کنیم.

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

SELECT 
	*
FROM
	Employee
LEFT OUTER JOIN
	City
ON
	Employee.CityID = City.ID

اگر بخواهیم ارتباط انواع JOIN و گام های سه گانه را به طور خلاصه در کنار هم داشته باشیم، می توانیم جدول زیر را تشکیل دهیم: