JOINها در SQL با مثال‌های ساده

پایگاه‌های دادهٔ رابطه‌ای برای جلوگیری از تکرار، داده‌ها را در جدول‌های جداگانه نگه می‌دارند: مشتریان در یک جدول و سفارش‌ها در جدولی دیگر. JOIN راهی است برای کنار هم گذاشتن دوبارهٔ این داده‌ها هنگام کوئری گرفتن.

بهترین راه برای فهمیدن JOINها این است که کارشان را روی ردیف‌های واقعی ببینید. در تمام این راهنما از دو جدول کوچک استفاده می‌کنیم.

جدول‌های نمونه#

customers (مشتریان)

id	name
1	Ada
2	Grace
3	Linus

orders (سفارش‌ها)

id	customer_id	total
101	1	49.00
102	1	15.50
103	2	99.99
104	4	25.00

به دو نکته دقت کنید: Linus هیچ سفارشی ندارد و سفارش ۱۰۴ متعلق به مشتری ۴ است که وجود ندارد (شاید حسابش حذف شده). دقیقاً در همین موارد مرزی است که JOINهای مختلف رفتار متفاوتی دارند.

می‌خواهید همراه ما امتحان کنید؟ این SQLِ آماده‌سازی است که در SQLite، PostgreSQL و MySQL کار می‌کند:

SQL

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total DECIMAL(10, 2));

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO orders VALUES (101, 1, 49.00), (102, 1, 15.50), (103, 2, 99.99), (104, 4, 25.00);

پایگاه داده بدون ORDER BY ترتیب ردیف‌ها را تضمین نمی‌کند، پس نتایج زیر برای خوانایی مرتب شده‌اند.

INNER JOIN: فقط ردیف‌های منطبق#

INNER JOIN ردیف‌هایی را برمی‌گرداند که شرطِ JOIN در هر دو جدول برقرار باشد:

SQL

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

name	order_id	total
Ada	101	49.00
Ada	102	15.50
Grace	103	99.99

Linus حذف می‌شود چون سفارشی ندارد و سفارش ۱۰۴ هم حذف می‌شود چون مشتری ندارد. نوشتن JOIN به‌تنهایی یعنی INNER JOIN.

LEFT JOIN: همهٔ ردیف‌های جدول چپ#

LEFT JOIN همهٔ ردیف‌های جدول چپ (جدولِ بعد از FROM) را نگه می‌دارد و هر جا در سمت راست تطابقی نباشد، NULL می‌گذارد:

SQL

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

name	order_id	total
Ada	101	49.00
Ada	102	15.50
Grace	103	99.99
Linus	NULL	NULL

Linus برگشته و ستون‌های سفارشش NULL است. بعد از INNER JOIN، این پرکاربردترین JOIN است.

پیدا کردن ردیف‌های بی‌تطابق#

یک ترفند کلاسیک: ترکیب LEFT JOIN با WHERE ... IS NULL برای پیدا کردن «یتیم‌ها». این‌جا مشتریانی را پیدا می‌کنیم که هرگز سفارش نداده‌اند:

SQL

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

نتیجه: Linus.

RIGHT JOIN: همهٔ ردیف‌های جدول راست#

RIGHT JOIN قرینهٔ آن است: همهٔ ردیف‌های جدول راست را نگه می‌دارد.

SQL

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o ON o.customer_id = c.id;

name	order_id	total
Ada	101	49.00
Ada	102	15.50
Grace	103	99.99
NULL	104	25.00

حالا سفارشِ یتیمِ ۱۰۴ بدون نام مشتری ظاهر می‌شود. در عمل، بیشتر برنامه‌نویسان RIGHT JOIN را با جابه‌جا کردن ترتیب جدول‌ها به LEFT JOIN تبدیل می‌کنند. کوئری وقتی خواناتر است که جدولِ «همه را نگه دار» همیشه اول بیاید.

FULL OUTER JOIN: همه‌چیز از هر دو#

FULL OUTER JOIN همهٔ ردیف‌های هر دو جدول را نگه می‌دارد و هر جا بتواند آن‌ها را تطبیق می‌دهد:

SQL

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.id;

name	order_id	total
Ada	101	49.00
Ada	102	15.50
Grace	103	99.99
Linus	NULL	NULL
NULL	104	25.00

برای تطبیق داده‌ها کاربردی است: مقایسهٔ دو فهرست و پیدا کردن چیزهایی که در هر طرف کم است.

پشتیبانی پایگاه‌های داده

MySQL از FULL OUTER JOIN پشتیبانی نمی‌کند؛ می‌توانید آن را با ترکیب یک LEFT JOIN و یک RIGHT JOIN به کمک UNION شبیه‌سازی کنید. SQLite از نسخهٔ ۳.۳۹.۰ به بعد از RIGHT و FULL پشتیبانی می‌کند.

جدول خلاصه#

هر JOIN کدام ردیف‌ها را نگه می‌دارد، به شکل نمودار ون

JOIN	ردیف‌های بی‌تطابق را از کجا نگه می‌دارد	کاربرد معمول
`INNER JOIN`	هیچ‌کدام	داده‌ای که باید در هر دو طرف وجود داشته باشد
`LEFT JOIN`	جدول چپ	«همهٔ مشتریان، به‌علاوهٔ سفارش‌هایشان در صورت وجود»
`RIGHT JOIN`	جدول راست	به‌ندرت؛ معمولاً به LEFT JOIN تبدیل می‌شود
`FULL OUTER JOIN`	هر دو جدول	تطبیق دو مجموعه داده

تجمیع بعد از JOIN#

JOINها همراه با GROUP BY واقعاً قدرتمند می‌شوند. این‌جا تعداد سفارش و مجموع خرید هر مشتری را می‌بینیم، از جمله مشتریانی که هیچ سفارشی ندارند:

SQL

SELECT
  c.name,
  COUNT(o.id)               AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

name	order_count	total_spent
Grace	1	99.99
Ada	2	64.50
Linus	0	0.00

تفاوت COUNT(column) و COUNT(*)

COUNT(o.id) فقط مقدارهای غیر NULL را می‌شمارد، پس Linus به‌درستی 0 می‌گیرد. COUNT(*) ردیف‌ها را می‌شمارد و برای Linus عدد 1 را می‌داد، چون LEFT JOIN همچنان یک ردیف برای او تولید می‌کند.

اشتباه‌های رایج#

فیلتر کردن جدول راست در WHERE بعد از LEFT JOIN. شرطی مثل WHERE o.total > 20 بی‌سروصدا ردیف‌های NULL را حذف می‌کند و LEFT JOIN شما را به INNER JOIN تبدیل می‌کند. شرط را به بخش ON منتقل کنید: LEFT JOIN orders AS o ON o.customer_id = c.id AND o.total > 20.
استفاده از شرط JOIN اشتباه. JOIN روی ستون‌های اشتباه یا بدون شرط واقعی می‌تواند یک ضرب دکارتی بسازد: هر ردیف با تک‌تک ردیف‌های دیگر جفت می‌شود.
JOIN روی ستون‌هایی با نوع دادهٔ متفاوت. این کار می‌تواند مانع استفادهٔ پایگاه داده از ایندکس‌ها شود و کوئری را کند کند.

جمع‌بندی#

INNER JOIN فقط ردیف‌هایی را برمی‌گرداند که در هر دو طرف منطبق باشند.
LEFT JOIN همهٔ ردیف‌های جدول چپ را برمی‌گرداند و هر جا تطابقی نباشد NULL می‌گذارد.
RIGHT JOIN قرینهٔ LEFT است و معمولاً به LEFT JOIN بازنویسی می‌شود.
FULL OUTER JOIN همه‌چیز را از هر دو جدول برمی‌گرداند.
LEFT JOIN ... WHERE right.id IS NULL ردیف‌های بی‌تطابق را پیدا می‌کند.

دو جدول بالا را در SQLite (sqlite3 shop.db) بسازید و هر کوئری را خودتان اجرا کنید. پنج دقیقه آزمایش از یک ساعت خواندن بهتر است.