بررسی و توضیح SQL Joins

خیلی وقتها در پروژه هایی که از دیتابیسهای رابطه ای (Relational Dateabse) استفاده می‌شود برای افزایش پرفرمنس و یا اینکه برای دریافت مقدار مشخصی لازم است چندین جدول را با هم join کنیم. همانطور که میدانید این join کردن با استفاده از کلیدهای خارجی که ارتباطی را بین جداول ایجاد کرده اند ایجاد می‌شود.

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

INNER JOIN

Inner join اشتراک مقادیر دو جدول را در نتیجه خروجی قرار می‌دهد.


SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

که نتیجه آن را در تصویر زیر مشاهده می‌کنید:

FULL OUTER JOIN

FULL OUTER JOIN اجتماع مقادیر دو جدول را در نتیجه خروجی قرار می‌دهد. یعنی ابتدا رکوردهای جدول A را با جدول B مطابقت میدهد در صورت تطابق آنها را در کنارهم قرار میدهد و در غیراینصورت مقادیر آن را NULL قرار می‌دهد.


SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

همانطور که نتیجه را در تصویر زیر می‌بینید مقادیری که مشترک نیستند نیز در خروجی وجود دارد.

در صورتیکه بخواهیم نقاط اشتراک دو جدول را حذف کنیم میتوانیم به صورت زیر شرطی را قرار دهیم که نقاط مشترک را در خروجی حذف کند.


SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

LEFT OUTER JOIN

LEFT OUTER JOIN تمام مقادیر جدول A و نقاط مشترک جدول B را در خروجی قرار می‌دهد.


SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

همانطور که در خروجی مشاهده می‌کنید تنها مقادیر جدول A و مقادیر مشترک جدول B در خروجی وجود دارد.

حالا اگر بخواهیم همین خروجی را ویرایش کنیم و مقادیر مشترک جدول را پاک کنیم با قراردادن یک شرط میتوانیم آن را به صورت زیر تغییر دهیم.


SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

که مطابق تصویر زیر خروجی تغییر پیدا میکند.

اگر بخواهیم به همه حالتهای SQL Join را باهم مقایسه کنیم در تصویر زیر تفاوت هر کدام را به راحتی می‌توانیم متوجه شویم.

SQL Self JOIN

برای مثال وقتی می‌خواهیم لیست تمام کاربرانی را پیدا کنیم که متعلق به یک شهر و یا کشور یکسان هستند پس برای اینکار به صورت زیر اقدام می‌کنیم.


SELECT B.FirstName AS FirstName1, B.LastName AS LastName1, 
       A.FirstName AS FirstName2, A.LastName AS LastName2, 
       B.City, B.Country
  FROM Customer AS A JOIN Customer AS B
    ON A.Id <> B.Id
   AND A.City = B.City 
   AND A.Country = B.Country
 ORDER BY A.Country

Laravel SQL Join

همانطور که میدانیم وقتی که پروژه ما با استفاده از لاراول توسعه داده شده باشد با استفاده از query builder لاراول به راحتی میتوانیم جداول مخلتف را join کنیم و شرطهایی که لازم هست را با query موردنظرمان ادغام کنیم.

در نمونه کدهای زیر که از داکیومنت لاراول گرفته شده است میتوانیم چند نمونه کد در این رابطه ببینیم.


/// Inner Join Clause

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

/// Left Join / Right Join Clause
$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

$users = DB::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

نکته: اگر بخواهیم که از داخل یک ستون که نوع آن JSON است مقداری را به query خود به عنوان یک ستون اضافه کنیم به صورت زیر اقدام می‌کنیم. در query زیر data یک ستون از نوع json است که مقدار source داخل آن را با اسم referrer در نتیجه query به صورت یک ستون چاپ می‌کنیم.


SELECT data->>"$.source" AS referrer, language FROM `campaigns`