انواع join در SQL

بررسی انواع join در زبان SQL

اشتراک گذاری

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

فرض ما بر این است که شما از قبل می دانید که به طور کلی عملیات join وسیله ای برای ترکیب ستون های رکوردهای ذخیره شده اطلاعات یک (self-join) یا چند جدول با همدیگر است. و اما تفاوت در انواع join در ترتیب و محدودیت های عملکرد آن هاست، تا حالت کلی ترکیب (ضرب) تمام ردیف های دو جدول در همدیگر را طوری کاهش داد که بتوان با اعمال پرس و جو روی ترکیب به دست آمده، نتیجه مطلوب را کسب کرد.

در ابتدای کار باید شمایی که در طراحی مثال ها استفاده شده، ارائه شود. جداول توسط کوئری های زیر ایجاد شده، که بهتر است شمای جداول تولید شده را در نظر داشته باشید.

قبل از بررسی انواع join ها، بیایید درباره کلمات “inner” و “outer” صحبت کنیم. استفاده از این کلمات در ایجاد یک عملیات join اختیاری است ( INNER JOIN برابر JOIN است و LEFT OUTER JOIN مشابه LEFT JOIN است). این کلمات کلیدی برای ایجاد شفافیت اضافه می شوند؛ چرا که موجب می شوند مفهوم پیاده سازی شده را بهتر متوجه شویم. برخی از توسعه دهندگان نرم افزار استفاده از آن ها را ترجیح نمی دهند و استدلال می کنند که دلیلی ندارد تا از کلماتی اضافی و غیر ضروری در کوئری های پایگاه داده ها استفاده کنند. اما به طور کلی برای ایجاد خوانایی بیشتر و قابلیت درک، توصیه می شود تا با امکانات زبان SQL سازگار باشیم و از این کلمات کلیدی استفاده کنیم.

Inner Join

inner join تنها ردیف هایی را به دست می دهد که در همه جداول join شده، مقادیر داده ای مرتبطی دارند. به طور مثال، اگر ما جدول مشتری ها و جدول سفارشات ثبت شده را با هم inner join کنیم، ردیف های نهایی خروجی ما مشتری ها و سفارشات مربوط به هم خواهند بود. چرا که ما برای هر مشتری سفارشی ثبت کرده ایم و برای هر سفارش یک مشتری به عنوان خریدار وجود دارد. پس اطلاعات این دو جدول به طور ذاتی به همدیگر مربوط هستند و نتیجه اجرای inner join ردیف هایی است که هر دو ردیف مربوط به دو جدول مذکور را در کنار یکدیگر قرار داده است. ارتباطی که از آن تا به کنون صحبت کرده ایم، به صورت مفهومی با استفاده از ستون های (ویژگی های) برابر ایجاد می شود (برابری به معنای هم نام و هم جنس و هم دامنه بودن است، مثلا دو ستون شناسه یکتای مشتری).

مثال زیر را برای روشن شدن توضیحات ببینید.

اگر ما بخواهیم طعم کیک فروخته شده در سفارش مشتری را هم از نتیجه کوئری به دست بیاوریم، از ارتباط دیگری ( cake_id) برای یک inner join اضافه (با جدول cakes) استفاده می کنیم؛ که به صورت زیر کوئری آن نوشته می شود:

Left outer join

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

در زیر یک کوئری روی جداول نمونه این مطلب ارائه شده است که البته در آن جدول سفارشات (به عنوان جدول چپ که تمام ردیف های آن در خروجی هستند) را با جدول مشتری ها (به عنوان جدول سمت راست) left outer join شده است و مقداری از خروجی هم به نمایش درآمده است.

Right outer join

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

در کوئری زیر مشتری ها (به عنوان جدول اول و چپ) با جدول سفارشات (به عنوان جدول سمت راست) ، RIGHT OUTER JOIN شده است. اما قبل از بررسی آن کوئری و دیدن بخشی از ابتدای نتیجه آن، به نگته زیر دقت کنید.

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

 

گویی right outer join همان left outer join است تنها با تفاوت در ترتیب اعمال روی پایگاه داده هایی join. به کد زیر دقت کنید.

Full outer join

Full outer join همه رکوردهای جدوال موجود در join را در خروجی خواهد آورد؛ بدین شکل که ردیف هایی با مقادیر مرتبط به هم در کنار همدیگر قرار خواهند گرفت و ردیف های دیگر هم به همان شکل دارای ویژگی های NULL در جدول به دست آمده، وجود خواهند داشت و هیچ ردیفی کنار گذاشته نخواهد شد. به طور مثال برای Full outer join میان جدول مشتری ها و سفارشات، جدول join شده خروجی، شامل: الف) ترکیب همه مشتری های دارای سفارش ثبت شده و سفارشات مربوطه ب) تمام مشتری های بدون سفارش ج) تمام سفارش های ثبت شده فاقد شناسه مشتری، خواهد بود.

شیوه نوشتاری آن در نسخه استاندارد SQL به صورت زیر است.

اما MySQL از full outer join پشتیبانی نمی کند، که می توانیم نتیجه مشابه با آن را از طریق اعمال UNION روی outer join های چپ و راست، به دست آوریم که به شکل زیر می تواند پیاده شود.

Cross join

این نوع از join گاهی به نام Cartesian join شناخته می شود؛ چرا که خروجی آن ضرب کارتزین میان ردیف های جداول است. در واقع جدول به دست آمده، ترکیب دو به دوی تمام ردیف هاست؛ هر ردیف از یک جدول با تمام ردیف های جداول دیگر ترکیب می شود و یک ردیف را در جدول خروجی تشکیل می دهد.

معمولاً این نوع از join اغلب استفاده نمی شود؛ چرا که خروجی آن بسیار بزرگ و شلوغ است! مثلا اگر دو جدول را که هر کدام ۱۰,۰۰۰ ردیف دارند، در همدیگر Cross join کنیم، جدول خروجی ۱۰۰,۰۰۰,۰۰۰ ردیف دارد. اما به هر حال گاهی ترکیب تمام داده ها در کاربردی خاص مورد نیاز است.

 

نظرتان را برای ما بنویسید

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