روش های مختلف JOIN در SQL

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

در این متن روش های مختلف JOIN که با استاندارد SQL:1999 سازگار می باشند توضیح داده می شوند.

این روش ها را در سینتکس زیر مشاهده می کنید.

 

NATURAL JOIN

از این روش برای نمایش اطلاعات از دو جدول متفاوت استفاده می شود. در  NATURAL JOIN فقط سطرهایی از دو جدول نمایش می یابند که ستون های مشترک آنها دارای مقدار یکسان باشند.

منظور از ستون های مشترک بین دو جدول ستون هایی می باشند که نام و نوع داده یکسان دارند. اگر نام این ستون ها یکسان باشد ولی نوع داده آنها متفاوت باشد در زمان اجرای دستور خطایی را از اوراکل دریافت می کنیم.

مثال: دو جدول EMPLOYEES و JOBS با هم NATURAL JOIN شده اند. در هر دو جدول ستون JOB_ID دارای نام و نوع داده یکسان می باشد. بنابراین سطرهایی از دو جدول که مقدار این ستون برای آنها یکسان است در کنار هم نمایش می یابند.

نکته: در روش NATURAL JOIN می توان از عبارت WHERE به منظور محدودسازی نمایش اطلاعات استفاده نمود.

مثال:

روش JOIN با استفاده از عبارت USING

برای اجرای NATURAL JOIN باید تمام ستون های مشترک دارای نام و نوع داده یکسان باشند. اما در روش JOIN با استفاده از عبارت USING یک یا چند ستون از دو جدول به عنوان شرط عملیات JOIN انتخاب می شوند که نام یکسان دارند ولی می توانند نوع داده متفاوت داشته باشند.

نکته: از این روش معمولا برای دو جدول که رابطه از نوع Primary Key و Foreign Key دارند استفاده می شود.

مثال 1: نام دپارتمان مربوط به تمام کارمندان شرکت را نمایش دهید.

در این مثال نام دپارتمان کارمندان در جدول DEPARTMENTS ذخیره شده است و سایر اطلاعات کارمندان در جدول EMPLOYEES می باشد. بنابراین با انجام عملیات JOIN این دو جدول بر اساس ستون DEPARTMENT_ID اطلاعات مورد نیاز نمایش می یابد.

SELECT EMPLOYEE_ID, DEPARTMENT_NAME FROM employees JOIN Departments USING (DEPARTMENT_ID);

مثال 2: در این مثال LOCATION_ID هر کارمند در جدول DEPARTMENTS قرار دارد.

+اگر نام جدول را به همراه یک نقطه قبل از نام ستون قرار دهیم سرعت عملیات PARSING در دیتابیس اوراکل بیشتر می شود.

+زمانی که در یک دستور دارای JOIN نام یک ستون در چند جدول یکسان می باشد (بجز نام ستون بکار رفته در عبارت USING یا در روش NATURAL JOIN) ذکر نام جدول(یا alias) قبل از نام ستون اجباری می باشد تا اوراکل متوجه شود این ستون مربوط به کدام جدول است.

مثال 3: در این دستور هر دو جدول دارای ستون MANAGER_ID می باشند بنابراین باید قبل از نام این ستون از پیشوند نام جدول مورد نظر استفاده شود.

نکته: به منظور افزایش خوانایی و افزایش سرعت نوشتن کد می توان از نام مستعار برای نام جداول استفاده نمود. این نام مستعار فقط در داخل همان دستور SQL معتبر خواهد بود. در ضمن اگر در قسمت FROM دستور SELECT از نام مستعار برای یک جدول استفاده شود این نام باید در تمامی بخش های دیگر آن دستور استفاده شود. البته برای نام ستونی که در عبارت USING  یا در روش NATURAL JOIN مشخص شده است نمی توان از از نام مستعار یا پیشوند نام جدول استفاده نمود.

مثال 4:

مثال: ستون location_id در عبارت USING استفاده شده است. بنابراین زمانی که برای این ستون از پیشوند نام جدول استفاده می شود اوراکل خطای 25154 برمی گرداند.

 

روش JOIN با استفاده از عبارت ON

روش های NATURAL و USING به صورت ضمنی شرط مساوی بودن مقدار ستون های دو جدول که نام یکسان دارند را در نظر می گیرند. ولی در این روش می توان شرط عمل JOIN را به صورت صریح بیان نمود و از ستون های با نام و نوع داده متفاوت استفاده کرد. بنابراین پیشنهاد می گردد به جای روش های USING و NATURAL از این روش استفاده شود.

مثال 1: فقط سطرهایی نمایش یابند که مقدار DEPARTMENT_ID آنها در دو جدول برابر است.

+ از آنجایی که در شرط ON نام ستون ها می توانند متفاوت باشند از نام مستعار و پیشوند نام جدول قبل از نام ستون استفاده می شود. البته استفاده از علامت پرانتز برای عبارت ON اختیاری می باشد.

مثال 2: JOIN سه جدول با استفاده از روش ON.

کد مثال بالا با استفاده از روش USING به صورت زیر می باشد.

نکته:  در روش JOIN با عبارت ON می توان از عبارت WHERE و یا AND به منظور تعیین شرطهای دیگر استفاده نمود.

مثال 3: این کدها نتیجه یکسان برمی گردانند.

 

روش SELF JOIN

گاهی اوقات به منظور بدست آوردن برخی نتایج باید یک جدول با خودش JOIN شود. به عنوان مثال می خواهیم نام مسئول مستقیم یکی از کارمندان شرکت را پیدا کنیم.

نام و اطلاعات فردی تمامی پرسنل در جدول EMPLOYEES قرار دارد. بنابراین با استفاده از عمل JOIN SELF در جدول EMPLYEES در مرحله اول از ستون های  LAST_NAME و MANAGER_ID نام کارمند و شماره پرسنلی مسئول مستقیم آن شخص را بدست می آوریم و در مرحله دوم با استفاده از مقدار MANAGER_ID، نام آن مسئول که یکی از کارمندان می باشد را از ستون LAST_NAME نمایش می دهیم.

مثال: نام خانوادگی مسئول مستقیم تمام کارمندان شرکت نمایش یابد. بنابراین جدول employees با خودش JOIN می شود.

 

NONEQUIJOIN یا JOIN نامساوی

در این روش برخلاف روش های قبلی از شرط مساوی در JOIN استفاده نمی شود و می توان از عملگرهای BETWEEN، منطقی، ریاضی و … استفاده نمود. بنابراین روش های قبلی از نوع EQUIJOIN بودند و این روش از نوع NONEQUIJOIN است.

مثال: در جدول JOB_GRADE به ازای میزان حقوق دریافتی، GRADE_LEVEL های مختلف تعیین شده است. می خواهیم GRADE_LEVEL مورد نظر برای تمام کارمندان بر اساس میزان حقوق دریافتی آنها را نمایش دهیم. بنابراین در شرط JOIN از عملگر BETWEEN استفاده می شود.

 

نکته: عملگر BETWEEN همان عملگر AND است.

 

روش JOIN با استفاده از عمگر OUTER

براساس استاندارد SQL:1999 تمامی روش های قبلی JOIN از نوع INNER JOIN می باشند به این معنی که  در این روش ها فقط سطرهایی از جداول که شرط JOIN در مورد آنها برقرار بود نمایش می یابند. ولی در روش OUTER سطرهای دیگر جدول که مطابق با شرط JOIN نیستند نیز نمایش می یابند. بنابراین برای جداول از نام مستعار و برای ستون ها از پیشوند نام جدول استفاده می شود.

روش OUTER JOIN می تواند به صورت LEFT، RIGHT یا FULL انجام شود.

نکته: در روش LEFT OUTER JOIN علاوه بر حالت عادی، سطرهایی از جدول سمت چپ که شرط JOIN در مورد آنها صدق نمی کند نیز نمایش می یابند. منظور از جدول سمت چپ جدولی می باشد که نام آن در سمت چپ عبارت JOIN ذکر شده است.

مثال: نام، شماره دپارتمان و نام دپارتمان تمام کارمندان نمایش یابد. نام تمام کارمندان از جدول سمت چپ نمایش یابد حتی اگر مقدار شماره دپارتمان یک کارمند از جدول EMPLOYEES با مقدار شماره دپارتمان در جدول DEPARTMENTS برابر نیست.

در روش RIGHT OUTER JOIN علاوه بر حالت عادی، سطرهایی از جدول سمت راست که شرط JOIN در مورد آنها صادق نمی باشد نیز نمایش می یابند. منظور از جدول سمت راست جدولی می باشد که نام آن در سمت راست عبارت JOIN ذکر شده است.

مثال: نام، شماره دپارتمان و نام دپارتمان تمام کارمندان نمایش یابد. نام دپارتمان و شماره دپارتمان تمام کارمندان از جدول سمت راست نمایش یابد حتی اگر مقدار شماره دپارتمان یک کارمند از جدول EMPLOYEES با مقدار شماره دپارتمان در جدول DEPARTMENTS برابر نیست

 در روش FULL OUTER JOIN علاوه بر حالت عادی، سطرهایی از جدول سمت راست و سمت چپ که شرط JOIN در مورد آنها صدق نمی کند نیز نمایش می یابند.

مثال: نام، شماره دپارتمان و نام دپارتمان تمام کارمندان نمایش یابد. اگر مقدار شماره دپارتمان یک کارمند از جدل EMPLOYEES با مقدار شماره دپارتمان در جدول DEPARTMENTS برابر نیست سطرهای متناظر از هر دو جدول سمت چپ و راست نمایش یابند.

 

روش CROSS JOIN

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

مثال: جدول EMPLOYEES شامل 20 سطر می باشد و جدول DEPARTMENTS 8 سطر دارد. بنابراین در CROSS JOIN این دو جدول 160 سطر نمایش می یابند.

SELECT EMPLOYEE_ID, DEPARTMENT_ID, LOCATION_ID FROM EMPLOYEES CROSS JOIN DEPARTMENTS;

 

روش JOIN غیراستاندارد

در این متن انواع روش های JOIN استاندارد را ملاحظه کردید. می توان دستور تمام این روش ها را بر اساس روش غیراستاندارد نوشت و نتیجه یکسان دریافت نمود.

مثال از روش USING:

SELECT EMPLOYEE_ID, DEPARTMENT_NAME FROM employees JOIN Departments USING (DEPARTMENT_ID);

همین دستوررا با روش غیر استاندارد می نویسیم:

SELECT a.EMPLOYEE_ID, b.DEPARTMENT_NAME FROM employees a , Departments b WHERE  a.DEPARTMENT_ID=b.DEPARTMENT_ID;

Comment (1)

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

نشانی ایمیل شما منتشر نخواهد شد.