JOINها از INNER تا شبیه‌سازی FULL OUTER، و UNION

ترکیب جدول‌ها

قدرت مدل رابطه‌ای در JOIN است: داده‌ای که نرمال‌سازی آن را در چند جدول پخش کرده، هنگام خواندن دوباره کنار هم قرار می‌گیرد. در این فصل از طرح فروشگاه فرش فصل قبل استفاده می‌کنیم.

-- INNER JOIN: فقط سفارش‌هایی که مشتری دارند (یعنی همه، به لطف FK)
SELECT o.id, c.full_name, o.total_amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'paid';

-- LEFT JOIN: همه‌ی مشتری‌ها، حتی بدون سفارش
SELECT c.full_name, COUNT(o.id) AS orders_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.full_name;

-- anti-join: مشتری‌هایی که هرگز خرید نکرده‌اند
SELECT c.id, c.full_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

بزرگ‌ترین دام LEFT JOIN: شرط در ON یا در WHERE؟

-- می‌خواهیم همه‌ی مشتری‌ها + تعداد سفارش‌های پرداخت‌شده
-- غلط: WHERE ردیف‌های NULL را حذف می‌کند و LEFT عملاً INNER می‌شود
SELECT c.full_name, COUNT(o.id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.full_name;

-- درست: شرط روی جدول سمت راست داخل ON
SELECT c.full_name, COUNT(o.id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.full_name;

قاعده: شرط‌های جدول سمت «اختیاری» (راست در LEFT JOIN) را در ON بگذارید؛ شرط‌های جدول اصلی در WHERE.

انواع دیگر

شبیه‌سازی FULL OUTER JOIN با UNION

فرض کنید موجودی انبار (stock_counts) و فهرست محصولات را مقایسه می‌کنیم و هم محصولات بی‌شمارش و هم شمارش‌های بی‌محصول را می‌خواهیم:

SELECT p.sku, s.qty
FROM products p LEFT JOIN stock_counts s ON s.sku = p.sku
UNION ALL
SELECT s.sku, s.qty
FROM products p RIGHT JOIN stock_counts s ON s.sku = p.sku
WHERE p.sku IS NULL;

بخش دوم فقط ردیف‌هایی را می‌آورد که در بخش اول نبوده‌اند، پس UNION ALL کافی و سریع‌تر از UNION است. UNION ساده تکراری‌ها را حذف می‌کند و برای این کار باید نتیجه را مرتب یا هش کند.

INTERSECT و EXCEPT

-- MySQL 8.0.31 به بعد: مشتری‌هایی که هم در ۱۴۰۲ و هم در ۱۴۰۳ خرید کرده‌اند
SELECT customer_id FROM orders WHERE created_at >= '2023-03-21' AND created_at < '2024-03-20'
INTERSECT
SELECT customer_id FROM orders WHERE created_at >= '2024-03-20' AND created_at < '2025-03-21';

نکته‌هایی که کمتر کسی می‌داند

• در MySQL، JOIN بدون ON و CROSS JOIN و کاما، همگی ضرب دکارتی‌اند؛ یک ON فراموش‌شده روی دو جدول ده‌هزارتایی، صد میلیون ردیف می‌سازد.
• JOIN روی ستون‌هایی با نوع یا collation متفاوت (مثلاً INT در یک طرف و VARCHAR در طرف دیگر) جلوی استفاده از ایندکس را می‌گیرد؛ در EXPLAIN نوع ALL می‌بینید.
• USING (customer_id) وقتی نام ستون در هر دو جدول یکی است کوتاه‌تر از ON است و ستون را فقط یک بار در SELECT * برمی‌گرداند.
• ترتیب نوشتن جدول‌ها در INNER JOIN بر عملکرد اثری ندارد؛ بهینه‌ساز ترتیب را خودش انتخاب می‌کند. STRAIGHT_JOIN این انتخاب را لغو می‌کند؛ فقط آخرین راه‌حل.
• نام ستون‌های خروجی UNION از SELECT اول گرفته می‌شود و ستون‌ها فقط بر اساس جایگاه جفت می‌شوند؛ اگر در بخش دوم ترتیب ستون‌ها را جابه‌جا بنویسید، MySQL خطا نمی‌دهد و داده بی‌صدا زیر عنوان اشتباه می‌نشیند.