Querying

آموزش 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 IDNameID
3Ali1
4Omid2
5Reza3
جدول Employee
NameID
Tehran3
Shiraz4
Tabriz5
جدول City

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

City NameCity IDNameID
Tehran3Ali1
Shiraz4Omid2
Tabriz5Reza3
نتیجه مورد انتظار

عملگر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 مشترک بوده و وظیفه آن تکرار تمامی اعضای (رکورد) مجموعه داده دوم به ازای هر یک از رکورد های مجموعه داده اول می باشد.

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

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

SELECT 
	*
FROM
	Employee
CROSS JOIN
	City

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

IDIDCity IDNameID
Tehran33Ali1
Shiraz43Ali1
Tabriz53Ali1
Tehran34Omid2
Shiraz44Omid2
Tabriz54Omid2
Tehran35Reza3
Shiraz45Reza3
Tabriz55Reza3

نتیجه حاصل از ضرب دکارتی دو جدول Employee و City

اگر کمی به رکورد های منتخب نگاه کنید، متوجه می شوید که همه آن ها دارای یک ویژگی مشترک هستند. شناسه (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 IDNameID
3Ali1
4Omid2
جدول Employee
NameID
Shiraz4
Tabriz5
جدول City

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

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

City NameCity IDNameID
3Ali1
Shiraz4Omid2
نتیجه مورد انتظار

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

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

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

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

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

آیا سطر خارجی است؟ City IDNameID
بله3Ali1
خیر4Omid2
سطرهای خارجی Employee
آیا سطر خارجی است؟NameID
خیرShiraz4
بلهTabriz5
سطرهای خارجی City

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

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

Employee.CityID = City.IDNameIDCityIDNameID
FALSEShiraz43Ali1
FALSETabriz53Ali1
TRUEShiraz44Omid2
FALSETabriz54Omid2
ضرب دکارتی و بررسی شرط انتخاب

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

NameIDCity IDNameID
Shiraz44Omid2
نتیجه حاصل از گام اول و دوم

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

توضیحاتNameIDCity IDNameID
خروجی پس از گام دومShiraz44Omid2
سطر خارجی Employee، اضافه شده در گام سوم.3Ali1
سطر خارجی City، اضافه شده در گام سوم.Tabriz5
نتیجه حاصل از گام اول، دوم و سوم

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

نوشته های مشابه

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

همچنین ببینید

بستن
دکمه بازگشت به بالا
بستن
بستن