join چیست - مقدمه 

امروز آموزشی دیگر از مجموعه چیستان SQL با عنوان  join چیست رو با هم خواهیم دید. join یکی از مهمترین عملیاتی است که در هر دیتابیس رابطه ای انجام میشه و در واقع یک (RDBMS(relational database management system از جوین استفاده می کنه تا رکوردهای یک جدول رو با جدول/جداول دیگه تطبیق بده و خروجی مورد نظر ما را تولید کنه. مثلا پیدا کردن نویسندگان کتاب از دو جدول مؤلف و کتاب و یا استخراج دروس اخذ شده دانشجو از جداول درس و دانشجو، بوسیله Join انجام می شود.

در دنیای برنامه نویسی کمتر برنامه و یا نرم افزاری رو مشاهده خواهید کرد که در انجام درخواست های کاربر و یا گزارشات و .... از عملیات join استفاده نکرده باشد. نکته ای که عملیات جوین در sql server رو جذاب تر (شایدم سخت تر) می کنه پیچیده شدن این عملیات با افزایش تعداد جداول شرکت کننده در جوین می باشد. پیچیدگی عملیات جوین نه تنها برای ما، بلکه برای خود sql server هم وجود دارد! به نحوی که در آموزش های بهینه سازی کوئری خواهیم دید که پیدا کردن ترتیب بهینه جداول در عملیات join یکی از سخت ترین مراحل بهینه سازی در sql server محسوب می شود.

در آموزش join چیست ابتدا با این عملیات به صورت مفهومی و با مرور مثالی در دنیای واقعی آشنا خواهیم شد و در ادامه خواهیم دید که شباهت زیادی بین نحوه انجام این عملیات در sql server با آنچه که ما بصورت روزمره انجام می دهیم وجود دارد. در قسمت پایانی آموزش join چیست به معرفی عملگرهای جوین در sql server می پردازیم و بطور کامل نحوه عملکرد آن ها را بررسی می کنیم. از آنجایی که در رفع مشکلات کارایی و افزایش سرعت اجرای کوئری هایی که با استخراج اطلاعات از چند جدول سرو کار دارند، همواره آشنایی با join و مفهوم آن بسیار مؤثر خواهد بود، مطالعه این آموزش به شما پیشنهاد می شود.

---

join چیست - مفهوم 

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

 با توجه به لیست های بالا و خروجی خواسته شده (نام و فامیل به همراه محل اشتغال) می بینیم که نیاز است که اطلاعات رو از دو لیست مستقل تهیه کنیم. هر فرد ممکنه از روش متفاوتی برای تهیه لیست نهایی استفاده کنه اما یکی از سریع ترین و رایج ترین روش ها که ما استفاده می کنیم به این صورت هست که در ابتدا یک کدملی رو از لیست اطلاعات افراد انتخاب می کنیم، نام و فامیل اون فرد رو در در لیست نهایی می نویسیم سپس در لیست دوم ( اطلاعات محل اشتغال) کد ملی این فرد رو جستجو می کنیم و با پیدا کردن آن، در واقع محل کار فرد مورد نظر رو استخراج کرده ایم(یک علامت هم، کنار سطر مربوطه قرار می دهیم تا در جستجوهای بعدی سریعتر عمل کنیم) و نهایتاً محل اشتغال رو هم به لیست نهایی اضافه می کنیم. شکل زیر تصویری انتزاعی از این عملیات را نمایش می دهد:

خوب ما در عملیات بالا، از یک ارتباط یا فیلد مشترک بین دو لیست استفاده کردیم و از طریق این فیلد مشترک، از لیست اول نام و فامیل رو استخراج کردیم و از لیست دوم هم محل اشتغال فرد رو و در نهایت این اطلاعات رو به یکدیگر وصل کردیم (با توجه به یکسان بودن کد ملی). پس ما تا اینجا به سادگی یک عملیات جوین رو بصورت دستی انجام داده ایم.به طور خلاصه در انجام عملیات جوین انجام شده توسط ما مراحل بالا انجام شد:

 1- وجود دو لیست کاغدی حاوی اطلاعات

 2- یک فیلد مشترک برای تطبیق دادن اطلاعات در لیست دوم (در مثال ما کد ملی)

 3- روش و یا الگوریتمی برای وصل کردن اطلاعات به یکدیگر (یافتن در لیست اول، تطبیق در لیست دوم و کنار هم قرار دادن اطلاعات مورد نیاز)

بریم و ببینیم در sql server این عملیات به چه شکل انجام می شود، فرض کنید معادل با لیست کاغذی حاوی اطلاعات پرسنل شاغل، جدول Employees ومعادل با لیست کاغذی اطلاعات محل کار  جدول Department را ایجاد کرده ایم و اطلاعات لیست ها را در این جداول وارد می کنیم. برای تولید خروجی مورد نظر در sql server کوئری زیر را می نویسیم:

Select E.Name,E.Famil,D.EmpNationalCode 
   From Employees As E Inner Join Department As D
                        ON E.NatinalCode=D.NationalCode

جالب است بدانید که یکی از روش های (الگوریتم های) مورد استفاده توسط sql server جهت انجام عملیات جوین، شبیه روشی است که ما در لیست های کاغذی بالا، انجام دادیم. به این صورت که در ابتدا یک رکورد (فرد) را از جدول اول (ورودی - لیست) انتخاب می کند و سپس در جدول دوم  به دنبال رکورد (فرد) دارای مقدار مساوی در فیلد مشترک (شرط جوین - عبارت ON) می گردد و نهایتاً اطلاعات موجود در این دو رکورد رو به یکدیگر متصل و در خروجی نمایش می دهد. این کار به ازای تک تک رکوردهای جدول اول انجام می شود.

---

join چیست - انواع عملگرها

نکته ای که در مورد عملگرهای sql server باید بدانیم دسته بندی آنها در دو گروه منطقی و فیزیکی است. آن دسته از عملگرهایی که ما در کورئری ها می نویسیم، عملگرهای منطقی هستند (Order by - group by- outer join ) و عمگر/عملگرهایی که sql server در زمان اجرای واقعی، جهت انجام عملیات از آن استفاده می کند عملگر فیزیکی نام دارد(scan - seek - merge). فعلا آشنایی در همین حد جهت ادامه مطلب آموزش فعلی کفایت می کند(به مبحث عملگرهای منطقی و فیزیکی در آموزش ترتیب اجرای دستورات در sql server پرداخته می شود).

در sql server به طور کلی 9 عملگر منطقی برای انجام عملیات جوین بین جداول وجود دارد (cross join- inner join- outer join و ...) که این عملگرها برای ما آشنا هستند و در کوئری نویسی، از آن ها استفاده می کنیم. اما تنها 3 عملگر فیزیکی جهت انجام عملیات جوین در زمان اجرای کوئری وجود دارد به عبارتی دیگر، هر جوینی که در کوئری های ما نوشته شود، در زمان اجرا سمت sql server تنها به یکی از سه عملگر فیزیکی جوین تبدیل می شود. در ادامه با عملگرهای فیزیکی جوین و نحوه عملکرد آن ها بیشتر آشنا می شویم.

Nested Loops Join: در مثال عملی قسمت قبل(لیست های کاغدی پرسنل و محل اشتغال)  از یک روش برای جوین اطلاعات لیست ها استفاده کردیم و گفتیم که sql server هم از یک عملگر فیزیکی جوین با چنین الگوریتمی استفاده می کند و این عملگر Nested Loops نام دارد. این عملگر هم دو ورودی (جدول) نیاز دارد که به ورودی اول (Outer Input) و به ورودی دوم (Inner Input) گفته می شود. روش انجام عملیات هم دقیقا مانند لیست های کاغذی است به این صورت که یک رکورد از جدول اول (Outer) انتخاب می شود و با توجه به فیلد مشترک بین دو جدول، جستجو در جدول دوم (Inner) انجام می شود تا رکورد/هایی با مقدار مشابه در فیلد مشترک پیدا شود و در نهایت خروجی نهایی تهیه شود.(آیکن این عملگر نیز بیانگر نحوه انجام این عملیات است)

شبه کد(Pseudo code) مراحل انجام عملیات Nested Loops:

For each row in Employees Outer input 
   For each row  in Department  Inner input 
      If Employees joins with Department
         Output(Employees, Department)

نکته اول: تعداد دفعات تکرار شدن عملیات تطبیق(یافتن رکورد با مقدار مشابه در جدول دوم) برابر است با تعداد رکوردهای جدول/ورودی اول (Outer Input).

نکته دوم: اینکه کدام یک از جداول به عنوان ورودی اول(Outer input) باشد و کدام یک به عنوان ورودی دوم(Inner Input) تنها و تنها توسط sql server و در واقع توسط Optimizer تعیین می شود(روش دارای هزینه کمتر ). پس ترتیب نوشتن جداول در کوئری، تعیین کننده Inner  و Outer نیست.

Merge Join: در این عملگر فیزیکی جوین در مرحله اول بایستی دو جدول(ورودی) مرتب(sort) شوند و در مرحله دوم عملیات جوین انجام می شود (در اصطلاح الگوریتمی دو مرحله ای است). پس یک نکته مهم در Merge Join این است که ورودی ها بایستی از قبل مرتب شده(sort) باشند. جهت مرتب بودن ورودی ها ممکن است سه حالت زیر را داشته باشیم:

    1. به دلیل وجود ایندکس روی جداول(ورودی ها)، رکوردها مرتب شده باشند.

    2. عملگرهای قبلی موجود در کوئری، منجر به sort شدن رکوردهای مورد نیاز، شده باشند.

    3. انجام عملیات sort در حین اجر (Force Sort Operation) در حالت مرتب نبودن رکورها قبل از عملیات جوین.

 مراحل انجام عملیات Merge Join:

    1. انتخاب اولین رکورد از ورودی های مرتب شده (در اینجا هم مانند عملگر Nested Loops، ورودی ها را با Inner و Outer نامگذاری می کنیم)

    2. در صورت یکسان بودن مقدار موجود در فیلد مشترک دو ورودی، هر دو رکورد(فیلدهای مورد نیاز) به عنوان خروجی انتخاب می شوند و رکورد بعدی، از Inner Input جهت تطبیق انتخاب می شود.

    3. در صورتی که مقدار موجود در فیلد مشترک، یکسان نباشد:

        3-1: اگر مقدار موجود در فیلد مشترک، Outer Input(ورودی بالا در Execution Plan) کوچکتر بود، رکورد بعدی از این ورودی انتخاب می شود.

        3-2: اگر مقدار موجود در فیلد مشترک، Inner Input(ورودی پایین در Execution Plan) کوچکتر بود، رکورد بعدی از این ورودی انتخاب می شود.

    4. مراحل 1 تا 3، تا پایان یافتن رکوردهای ورودی ها ادامه پیدا می کند.

فلوچارت مراحل انجام عملیات Merge Join:

 شبه کد(Pseudo code) مراحل انجام عملیات Merge Join:

Get first row Employees from input 1 
  Get first row Department from input 2
While not at the end of either input
 Begin
  If Employees joins with Department
    Begin
      Output(Employees, Department)
      Get next row Department from input 2
    end
   else if Employees < Department
     get next row Employees from input 1
  else
    get next row Department form input 2
end

نمایش تصویری مراحل انجام عملیات Merge Join:

Hash Match Join: سومین عملگر فیزیکی جوین که نسبت به Merge Join و Nested Loops پیچیده تر است. این عملگر هم مانند Merge Join دو مرحله ای(two passed algorithm) است و به عبارتی دیگر دارای setup cost است. مرحله(فاز) اول Build Phase نام دارد که در این مرحله یک Hash Table از یکی از ورودی ها(دارای هزینه کمتر - تعداد رکورد کمتر) ساخته می شود و مرحله دوم Probe Phase نام دارد و در این مرحله، به ازای رکوردهای ورودی دوم جهت یافتن انطباق، در Hash Table جستجو(Probe) انجام می شود. در این الگوریتم، ورودی اول (ورودی بالا در Execution Plan) را با نام Build Input و ورودی دوم(ورودی پایین در Execution Plan) را با نام Probe Input می شناسیم:

مراحل انجام عملیات Hash Match Join:

     1. Build Phase: در این فاز، تابع هشینگ (Hash Function) روی تک تک رکوردهای Build Input اعمال می شود و در این مرحله Hash Value (کلید هش) برای رکوردها (ستون/ های جوین) تولید می شود. بر اساس Hash Value تولید شده، رکوردها در باکت ها(Hash Bucket) توزیع می شوند به عبارتی دیگر رکوردهای داری Hash Value یکسان، در باکت های یکسانی قرار می گیرند. مقادیر  Hash Value به همراه آدرس بلاک مربوط به آن، جهت استفاده در مرحله Probe Phase در Hash Table نگهداری می شود.

نکته: تابع هشینگ (Hash Function) تنها بر روی فیلد/هایی که در شرط جوین وجود دارند اعمال می شود و نه روی همه فیلدها

    2. Probe Phase: بعد از اتمام فاز Build و توزیع رکوردها در باکت های حافظه، نوبت به فاز جستجو(Probe) می رسد در این فاز، ابتدا یک رکورد از Probe Input انتخاب می شود و تابع هشینگ (Hash Function) استفاده شده در مرحله Build روی این رکورد(ستون/ های جوین) اعمال می شود. سپس با جستجو در  hash Table، آدرس باکتی با مقدار حاصل شده(Hash Value) را پیدا می کنیم (دقت کنید تا اینجا، تنها آدرس باکتی را یافته ایم که حاوی رکوردهایی با Hash Value یکسان هستند) و نهایتا در این باکت جستجو می کنیم تا رکورد/های مورد نظر را پیدا کنیم. شکل زیر نمایشی از مراحل عنوان شده می باشد:

مشاهده فازهای انجام عملیات Hash Match Join  با جزئیات بیشتر:

---

join چیست - جمع بندی

قبل از هر چیز، یک نکته مهم رو مجدداً یاد آوری می کنم:

نکته: در عملیات جوین، اینکه کدام یک از جداول شرکت کننده در جوین، به عنوان Outer/Build Input و یا Inner/Probe Input باشند تنها و تنها توسط Optimizer و با توجه به هزینه(Cost) آن ها انتخاب می شود و اینکه ما در کوئری خودمان، جدولی را سمت چپ و یا سمت راست جوین نوشته باشیم، در این مورد کاملا بی تأثیر است.

با توجه به اهمیت و پیچیدگی خاص عملیات Join در sql server، در آموزش join چیست سعی شد تا از طریق ابزارهای مختلف مانند: مثال کارذبردی، تصاویر، کوئری و شبه کد این عملیات را به بهترین شکل آموزش دهیم. اما مبحث Join در sql server به join چیست ختم نمی شود و این آموزش تنها به عنوان یک شروع و پیش نیاز برای مباحث پیش رفته آتی در بهینه سازی و ترفندهای sql server است. در آینده مواردی مانند:

     a. بررسی نقاط ضعف و قوت هر کدام از عملگرهای فیزیکی جوین

     b. چه عواملی باعث انتخاب یک عملگر فیزیکی توسط Optimizer، جهت انجام عملیات Join می شود؟

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

     d. مقایسه کارایی عملگرهای جوین تحت شرایط مختلف (تعداد رکورد مختلف، ایندکس گذاری مختلف روی جداول و ...)

     e. تفاوت نوشتن شرط جوین در عبارت where و یا ON در کوئری

     f. مفهوم Nullify شدن جوین و نحوه جلوگیری از رخ دادن آن

     g. محدودیت الگوریتم های جوین در انجام جوین های منطقی

    و ...

 

• قبلی
• بعدی