آموزش 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 ذخیره شده است.

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

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

City ID	Name	ID
3	Ali	1
4	Omid	2
5	Reza	3

Name	ID
Tehran	3
Shiraz	4
Tabriz	5

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

City Name	City ID	Name	ID
Tehran	3	Ali	1
Shiraz	4	Omid	2
Tabriz	5	Reza	3

عملگر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	ID	City ID	Name	ID
Tehran	3	3	Ali	1
Shiraz	4	3	Ali	1
Tabriz	5	3	Ali	1
Tehran	3	4	Omid	2
Shiraz	4	4	Omid	2
Tabriz	5	4	Omid	2
Tehran	3	5	Reza	3
Shiraz	4	5	Reza	3
Tabriz	5	5	Reza	3

اگر کمی به رکورد های منتخب نگاه کنید، متوجه می شوید که همه آن ها دارای یک ویژگی مشترک هستند. شناسه (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 به شکل زیر تغییر کند:

City ID	Name	ID
3	Ali	1
4	Omid	2

Name	ID
Shiraz	4
Tabriz	5

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

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

City Name	City ID	Name	ID
	3	Ali	1
Shiraz	4	Omid	2

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

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

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

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

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

آیا سطر خارجی است؟	City ID	Name	ID
بله	3	Ali	1
خیر	4	Omid	2

آیا سطر خارجی است؟	Name	ID
خیر	Shiraz	4
بله	Tabriz	5

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

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

Employee.CityID = City.ID	Name	ID	CityID	Name	ID
FALSE	Shiraz	4	3	Ali	1
FALSE	Tabriz	5	3	Ali	1
TRUE	Shiraz	4	4	Omid	2
FALSE	Tabriz	5	4	Omid	2

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

Name	ID	City ID	Name	ID
Shiraz	4	4	Omid	2

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

توضیحات	Name	ID	City ID	Name	ID
خروجی پس از گام دوم	Shiraz	4	4	Omid	2
سطر خارجی Employee، اضافه شده در گام سوم.	–	–	3	Ali	1
سطر خارجی City، اضافه شده در گام سوم.	Tabriz	5	–	–	–

همان طور که مشاهده می کنید، سطرهای خارجی هر جدول مستقل از دیگری به مجموعه داده پس از گام دوم اضافه شده است. هنگام اضافه کردن سطر های خارجی یک جدول، مقایر مرتبط با جدول دوم خالی (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 و گام های سه گانه را به طور خلاصه در کنار هم داشته باشیم، می توانیم جدول زیر را تشکیل دهیم:

نوع JOIN	ضرب دکارتی	ارزیابی شرط انتخاب	اضافه کردن سطر خارجی
CROSS JOIN	OK
INNER JOIN	OK	OK
OUTER JOIN	OK	OK	OK