معرفی انواع JOIN در زبان SQL

یکی از مباحثی که در زبان SQL برای کاربران تازه کار مشکل ایجاد می کند، بحث JOIN کردن داده ها و انواع JOIN در SQL است. ما می خواهیم در این مقاله به بررسی JOIN ها پرداخته و آن ها را توضیح بدهیم.

این مقاله برای افرادی در نظر گرفته شده است که با پایگاه های داده رابطه ای مانند MySQL کار می کنند.

پایگاه داده ای ساده

تصور کنید یک فروشگاه آنلاین دارید و می خواهید اطلاعات مشتری ها (customers) و سفارشات آن ها (orders) را در پایگاه داده خود ثبت کنید. اگر از پایگاه های داده رابطه ای مانند MySQL استفاده می کنید، می دانید که برای طراحی چنین سیستمی باید دو جدول به نام های customers و orders ایجاد کنیم. فرض کنید جدول customers شما در حال حاضر چنین داده هایی را دارد:

customer_id         first_name             last_name              email      address  city          state       zip

1              George   Washington           gwashington@usa.gov        3200 Mt Vernon Hwy          Mount Vernon     VA           22121

2              John        Adams    jadams@usa.gov  1250 Hancock St  Quincy   MA          02169

3              Thomas  Jefferson               tjefferson@usa.gov             931 Thomas Jefferson Pkwy              Charlottesville      VA           22902

4              James     Madison jmadison@usa.gov               11350 Constitution Hwy     Orange   VA           22960

5              James     Monroe jmonroe@usa.gov               2050 James Monroe Pkwy Charlottesville      VA           22902

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

order_id order_date           amount  customer_id

1              07/04/1776          $234.56 1

2              03/14/1760          $78.50   3

3              05/23/1784          $124.00 2

4              09/03/1790          $65.50   3

5              07/21/1795          $25.50   10

6              11/27/1787          $14.40   9

ستون order_id در جدول بالا یک foreign key است که به آیدی مشتری صاحب سفارش اشاره می کند. همچنین در نظر داشته باشید که اولا اینطور نیست که تمام کاربران ما سفارشی ثبت کرده باشند؛ برخی از کاربران هیچ سفارشی ندارند. دوما برخی از سفارشات هیچ صاحبی در جدول customers ندارند (مثلا customer_id که برابر ۱۰ باشد نداریم).

با تمام این اوصاف مدل پایگاه داده ما در این حالت به شکل زیر است:

حالا اگر بخواهیم تمام سفارشات ثبت شده توسط یک مشتری خاص را پیدا کنیم باید چه کار کنیم؟ در اینجا به هر دو جدول نیاز داریم؛ ابتدا باید اطلاعات مشتری را از جدول مشتریان داشته باشیم و سپس اطلاعات سفارشات ثبت شده توسط او را به دست بیاوریم.

انواع JOIN در SQL از نظر تئوری

ما به زودی به جاول customers و orders برمی گردیم و مثال های مختلفی را روی آن ها پیاده می کنیم اما فعلا به جای این دو جدول، دو جدول مثالی دیگر به نام های table A و table B را در نظر بگیرید. کلمه JOIN به معنی «پیوند دادن» یا «ادغام» است بنابراین زمانی از آن استفاده می کنیم که هدفمان ادغام کردن دو جدول  table A و table B باشد. اینکه این جداول تا چه حدی در هم ادغام می شوند بسته به مقدار داده ها و نوع JOIN مورد نظر خواهد داشت. بیایید در ابتدا بدون اینکه کدنویسی کنیم از نظر تئوری انواع JOIN ها را بررسی نماییم.

اولین نوع JOIN به inner join یا پیوند داخلی معروف است. در این حالت تمام داده های جدول A و جدول B که با شرطِ join همخوانی داشته باشند، ترکیب خواهند شد. به زبان ساده تر مقادیری که در هر دو جدول اشتراک داشته باشند برگردانده خواهند شد:

نوع بعدی join به نام left join یا پیوند چپ شناخته می شود. در این حالت تمام داده های جدول سمت چپ (A) با داده هایی از جدول سمت راست (B) که شرطِ Join را رعایت کنند برگردانده می شوند:

نوع بعدی join به نام right join یا پیوند راست شناخته می شود که دقیقا برعکس left join است. در این حالت تمام داده های جدول سمت راست (B) با داده هایی از جدول سمت چپ (A) که شرطِ Join را رعایت کنند برگردانده می شوند:

نوع آخر join به نام full join یا پیوند کامل شناخته می شود. در این حالت تمام داده هایی که با شرط کوئری منطبق باشند از هر دو جدول برگردانده می شوند (هم راست و هم چپ) و دیگر شرطِ join اهمیتی ندارد:

در ضمن باید بدانید که right join و left join و full join در گروهی بزرگتر به نام outer join ها قرار دارند بنابراین اگر آن ها را با نام right outer join یا left outer join یا full outer join دیدید، تعجب نکنید.

کدنویسی JOIN در SQL با مثال های عملی

اولین مثال ما Inner Join است. فرض کنید بخواهیم لیستی از مشتریانی داشته باشیم که سفارشی را ثبت کرده اند به طوری که جزئیات سفارشاتشان را نیز داشته باشیم. از آنجایی که inner join اشتراک دو جدول را برمی گرداند، این مثال بهترین مثال برای آن است. ساختار کلی دستور inner join در زبان SQL به شکل زیر است:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

با این حساب اگر بخواهیم از این ساختار استفاده کنیم، چنین کد SQL ای را خواهیم داشت:

select first_name, last_name, order_date, order_amount

from customers c

inner join orders o

on c.customer_id = o.customer_id

بسته به اینکه از چه پایگاه داده ای استفاده می کنید (MySQL یا Postgresql و امثال آن ها) ممکن است ساختار کد ها کمی متفاوت باشد. من در انتهای این مقاله چند مثال مستقیم از MySQL را به شما نشان می دهم اما فعلا هدف شما باید درک ساختار کلی کد ها باشد. با اجرای این کد نتیجه زیر برایمان برگردانده می شود:

first_name             last_name              order_date           order_amount

George   Washington           07/4/1776            $234.56

John                        Adams                    05/23/1784          $124.00

Thomas  Jefferson               03/14/1760          $78.50

Thomas  Jefferson               09/03/1790          $65.50

همانطور که می بینید مشتری Thomas Jefferson دو سفارش داشته است و هر دو سفارش نیز در نتیجه بالا ذکر شده اند.

مثال بعدی ما یک Left Join است. اگر بخواهیم داده های سفارشات را به جدول مشتریان اضافه کنیم (فارغ از اینکه یک مشتری سفارشی ثبت کرده است یا خیر) از Left Join استفاده می کنیم. همانطور که گفتم left join تمام داده های جدول سمت چپ را به همراه داده های منطبق از آن در جدول سمت راست برمی گرداند. ساختار کلی استفاده از Left Join بدین شکل است:

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

بنابراین می توان با SQL گفت:

select first_name, last_name, order_date, order_amount

from customers c

left join orders o

on c.customer_id = o.customer_id

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

first_name             last_name              order_date           order_amount

George   Washington           07/04/1776          $234.56

John                        Adams                    05/23/1784          $124.00

Thomas  Jefferson               03/14/1760          $78.50

Thomas  Jefferson               09/03/1790          $65.50

James                     Madison NULL                       NULL

James                     Monroe NULL                       NULL

در جدول بالا می بینید که James Madison و James Monroe هیچ سفارشی ثبت نکرده بودند. اگر به جداول اصلی که در ابتدای مقاله به شما دادم نگاهی بیندازید متوجه می شوید که customer_id این دو فرد در جدول customers به ترتیب 4 و 5 بوده است اما در ستون customer_id از جدول orders آیدی های 4 و 5 را نداریم. این مسئله باعث شده است که مقادیر آن ها در ستون تاریخ خرید و مقدار خرید برابر با NULL باشد.

شاید بپرسید چنین join ای چه کاربردی دارد؟ کاربرد این join ها بسته به ساختار پایگاه داده شما و نیاز خاص شما در آن لحظه دارد. به طور مثال ما می توانیم با همین left join و اضافه کردن یک شرط ساده به آن، تمام مشتریانی که سفارشی را ثبت نکرده اند، پیدا کنیم:

select first_name, last_name, order_date, order_amount

from customers c

left join orders o

on c.customer_id = o.customer_id

where order_date is NULL

در اینجا همان left join را انجام داده ایم با این تفاوت که این بار تنها James Madison و James Monroe برایمان برگردانده می شوند.

مثال سوم یک Right Join است و همانطور که می دانید دقیقا نسخه برعکس Left Join است بنابراین با استفاده از آن می توانیم لیست کاملی از تمام سفارشات را به همراه اطلاعات تمام مشتریان دریافت کنیم! ساختار کلی استفاده از Right Join بدین شکل است:

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

با این حساب می توان گفت:

select first_name, last_name, order_date, order_amount

from customers c

right join orders o

on c.customer_id = o.customer_id

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

first_name             last_name              order_date           order_amount

George   Washington           07/04/1776          $234.56

Thomas  Jefferson               03/14/1760          $78.50

John                        Adams                    05/23/1784          $124.00

Thomas  Jefferson               09/03/1790          $65.50

NULL                       NULL                       07/21/1795          $25.50

NULL                       NULL                       11/27/1787          $14.40

دو سفارش آخر هیچ مشتری نداشتند که در پایگاه داده ما وجود داشته باشد بنابراین نام و نام خانوادگی آن ها null می باشد.

احتمالا شما نیز متوجه شده باشید که در right join و left join ترتیب جدول ها (اینکه کدام جدول را جدول سمت راست و کدام جدول را جدول سمت چپ در نظر بگیریم) اهمیت بسیار زیادی دارد. ما در مثال بالا جدول orders را روی جدول customers پیوند راست یا right join کرده ایم. اگر به جای این کار جدول customers را روی جدول orders پیوند راست یا right join می کردیم، نتیجه دقیقا مانند نتیجه left join در مثال قبلی می شد!

مثل همیشه می توانیم با اضافه کردن یک شرط where می توانیم سفارشاتی را پیدا کنیم که در پایگاه داده ما هیچ صاحبی ندارند:

select first_name, last_name, order_date, order_amount

from customers c

right join orders o

on c.customer_id = o.customer_id

where first_name is NULL

مثال آخر ما نیز یک Full Join است که تمام داده های جدول راست و چپ را با هم ادغام می کند. ساختار کلی استفاده از full join ها به شکل زیر است:

SELECT column_name(s)

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name

WHERE condition;

با این حساب می توانیم تمام داده های جدول customers و orders را با هم ترکیب کنیم:

select first_name, last_name, order_date, order_amount

from customers c

full join orders o

on c.customer_id = o.customer_id

با اجرای این کد نتیجه زیر را دریافت می کنیم:

first_name             last_name              order_date           order_amount

George   Washington           07/04/1776          $234.56

Thomas  Jefferson               03/14/1760          $78.50

John                        Adams                    05/23/1784          $124.00

Thomas  Jefferson               09/03/1790          $65.50

NULL                       NULL                       07/21/1795          $25.50

NULL                       NULL                       11/27/1787          $14.40

James                     Madison NULL       NULL

James                     Monroe NULL       NULL

این تمام داده های جدول customers و orders می باشد.

تفاوت SQL در پایگاه های داده مختلف

در نهایت باید در نظر داشته باشید که هر نوع پایگاه داده ای که از SQL استفاده می کند ممکن است نسخه ای متفاوت از آن را داشته باشد. مثلا MySQL از Full Join پشتیبانی نمی کند و نمی توانید چنین کاری را در آن انجام بدهید. برای آشنایی با پایگاه داده مورد نظر خودتان باید به وب سایت آن مراجعه کنید. مثلا این صفحه از documentation رسمی MySQL یک مثال ساده برای inner join دارد:

SELECT t1.name, t2.salary

  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;




SELECT t1.name, t2.salary

  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

t1 به معنی table 1 و t2 به معنی table 2 است. می بینید که ساختار اصلی یکسان است اما ممکن است تفاوت هایی جزئی مانند استفاده از as (به عنوان alias) نیز وجود داشته باشد.

منبع: وب سایت sql-join