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

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

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

CREATE TABLE cakes (
    cake_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    flavor VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(15),
    street_address VARCHAR(255),
    city VARCHAR(255),
    zip_code VARCHAR(5),
    referrer_id INT,
    FOREIGN KEY (referrer_id) REFERENCES customers (customer_id)
);

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    cake_id INT NOT NULL,
    customer_id INT,
    pickup_date DATE NOT NULL,
    FOREIGN KEY (cake_id) REFERENCES cakes (cake_id),
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

قبل از بررسی انواع join ها، بیایید درباره کلمات “inner” و “outer” صحبت کنیم. استفاده از این کلمات در ایجاد یک عملیات join اختیاری است (

INNER JOIN

برابر

JOIN

است و

LEFT OUTER JOIN

مشابه

LEFT JOIN

است). این کلمات کلیدی برای ایجاد شفافیت اضافه می شوند؛ چرا که موجب می شوند مفهوم پیاده سازی شده را بهتر متوجه شویم. برخی از توسعه دهندگان نرم افزار استفاده از آن ها را ترجیح نمی دهند و استدلال می کنند که دلیلی ندارد تا از کلماتی اضافی و غیر ضروری در کوئری های پایگاه داده ها استفاده کنند. اما به طور کلی برای ایجاد خوانایی بیشتر و قابلیت درک، توصیه می شود تا با امکانات زبان SQL سازگار باشیم و از این کلمات کلیدی استفاده کنیم.

Inner Join

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

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

SELECT first_name, phone, orders.cake_id, pickup_date
FROM customers INNER JOIN orders
ON customers.customer_id = orders.customer_id;

/*
    +------------+------------+---------+-------------+
    | first_name | phone      | cake_id | pickup_date |
    +------------+------------+---------+-------------+
    | Linda      | 8095550114 |       4 | 2017-10-12  |
    | May        | 8015550130 |       4 | 2017-02-03  |
    | Frances    | 8345550120 |       1 | 2017-09-16  |
    | Matthew    | 8095550122 |       3 | 2017-07-20  |
    | Barbara    | 8015550157 |       2 | 2017-07-07  |
    ...
*/

اگر ما بخواهیم طعم کیک فروخته شده در سفارش مشتری را هم از نتیجه کوئری به دست بیاوریم، از ارتباط دیگری (

cake_id

) برای یک inner join اضافه (با جدول

cakes

) استفاده می کنیم؛ که به صورت زیر کوئری آن نوشته می شود:

SELECT first_name, phone, cakes.flavor, pickup_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN cakes ON orders.cake_id = cakes.cake_id;

/*
    +------------+------------+-----------+-------------+
    | first_name | phone      | flavor    | pickup_date |
    +------------+------------+-----------+-------------+
    | Frances    | 8345550120 | Chocolate | 2017-09-16  |
    | Theodore   | 8015550175 | Chocolate | 2017-08-13  |
    | James      | 8015550165 | Chocolate | 2017-10-12  |
    | Kathleen   | 8095550157 | Chocolate | 2017-09-24  |
    | Jennifer   | 8015550153 | Chocolate | 2017-06-22  |
    ...
*/

Left outer join

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

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

SELECT cake_id, pickup_date, customers.customer_id, first_name
FROM orders LEFT OUTER JOIN customers
ON orders.customer_id = customers.customer_id
ORDER BY pickup_date;

/*
    +---------+-------------+-------------+------------+
    | cake_id | pickup_date | customer_id | first_name |
    +---------+-------------+-------------+------------+
    |       2 | 2017-01-01  |        NULL | NULL       |
    |       3 | 2017-01-01  |      108548 | Eve        |
    |       1 | 2017-01-01  |      857831 | Neil       |
    |       4 | 2017-01-01  |        NULL | NULL       |
    |       3 | 2017-01-01  |      168516 | Maria      |
    ...
*/

Right outer join

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

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

 

SELECT customers.customer_id, first_name, pickup_date
FROM customers RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY pickup_date;

/*
    +-------------+------------+-------------+
    | customer_id | first_name | pickup_date |
    +-------------+------------+-------------+
    |        NULL | NULL       | 2017-01-01  |
    |      108548 | Eve        | 2017-01-01  |
    |      857831 | Neil       | 2017-01-01  |
    |        NULL | NULL       | 2017-01-01  |
    |        NULL | NULL       | 2017-01-01  |
    ...
*/

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

SELECT customers.customer_id, first_name, pickup_date
FROM orders LEFT OUTER JOIN customers
ON customers.customer_id = orders.customer_id
ORDER BY pickup_date;

/*
    same results as right outer join we just did!

    +-------------+------------+-------------+
    | customer_id | first_name | pickup_date |
    +-------------+------------+-------------+
    |        NULL | NULL       | 2017-01-01  |
    |      108548 | Eve        | 2017-01-01  |
    |      857831 | Neil       | 2017-01-01  |
    |        NULL | NULL       | 2017-01-01  |
    |        NULL | NULL       | 2017-01-01  |
    ...
*/

Full outer join

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

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

SELECT order_id, pickup_date, customers.customer_id, first_name
FROM orders FULL OUTER JOIN customers
ON orders.customer_id = customers.customer_id

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

UNION

روی outer join های چپ و راست، به دست آوریم که به شکل زیر می تواند پیاده شود.

SELECT order_id, pickup_date, customers.customer_id, first_name
FROM orders LEFT OUTER JOIN customers
ON orders.customer_id = customers.customer_id

UNION

SELECT order_id, pickup_date, customers.customer_id, first_name
FROM orders RIGHT OUTER JOIN customers
ON orders.customer_id = customers.customer_id;


/*
    +----------+-------------+-------------+------------+
    | order_id | pickup_date | customer_id | first_name |
    +----------+-------------+-------------+------------+
    |   900075 | 2017-05-17  |        NULL | NULL       |
    |   900079 | 2017-12-26  |      487996 | Frances    |
    |   900057 | 2017-10-25  |      498546 | Loretta    |
    |     NULL | NULL        |      640804 | Whitney    |
    |     NULL | NULL        |       58405 | Zoe        |
    ...
*/

Cross join

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

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