JOINها، EXISTS و LATERAL

ترکیب جدول‌ها بدون غافل‌گیری

INNER JOIN فقط ردیف‌های منطبق را نگه می‌دارد، LEFT JOIN همه‌ی ردیف‌های چپ را (با NULL برای سمت راستِ بی‌جفت)، FULL JOIN همه‌ی ردیف‌های هر دو طرف، و CROSS JOIN هر ردیف را با همه‌ی ردیف‌های دیگر. تا این‌جا آشناست؛ دام‌ها در جزئیات است.

شرط در ON یا در WHERE؟

-- همه‌ی مشتری‌ها + سفارش‌های تأییدشده‌شان (مشتری بدون سفارش هم می‌ماند)
SELECT c.full_name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'confirmed';

-- اشتباه رایج: همین شرط در WHERE، LEFT JOIN را عملاً INNER می‌کند
SELECT c.full_name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'confirmed';

semi-join و anti-join

-- مشتری‌هایی که دست‌کم یک سفارش دارند (بدون تکرار ردیف)
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- نقشه‌هایی که هیچ‌وقت سفارش نداشته‌اند
SELECT d.code, d.title FROM designs d
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.design_code = d.code);

برای anti-join همیشه NOT EXISTS بنویسید، نه NOT IN. اگر زیرکوئری NOT IN حتی یک NULL برگرداند، نتیجه‌ی کل شرط NULL می‌شود و هیچ ردیفی برنمی‌گردد.

LATERAL: زیرکوئری‌ای که ردیف بیرونی را می‌بیند

زیرکوئری معمولی در FROM نمی‌تواند به جدول‌های کنارش ارجاع دهد. با LATERAL می‌تواند؛ مثل یک حلقه‌ی for روی هر ردیف. کلاسیک‌ترین کاربرد: «N ردیف برتر هر گروه».

-- سه سفارش آخر هر مشتری کاشانی
SELECT c.full_name, last3.id, last3.created_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.created_at
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 3
) AS last3
WHERE c.city = 'کاشان';

-- با LEFT JOIN LATERAL مشتری بدون سفارش هم می‌ماند
SELECT c.full_name, s.cnt, s.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT count(*) AS cnt, sum(oi.line_total_rial) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = c.id
) s ON true;

با ایندکس orders (customer_id, created_at DESC)، کوئری اول برای هر مشتری فقط سه ورودی ایندکس می‌خواند؛ روی جدول میلیونی تفاوت ثانیه و میلی‌ثانیه است.

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

• در LEFT JOIN، count(*) مشتری بی‌سفارش را ۱ می‌شمارد؛ count(o.id) درست ۰ می‌دهد چون NULLها را نمی‌شمارد.
• توابعی که مجموعه برمی‌گردانند (unnest، jsonb_array_elements، generate_series) در FROM به‌طور ضمنی LATERAL‌اند: FROM products p, unnest(p.tags) AS t بدون کلمه‌ی LATERAL کار می‌کند.
• با بیش از ۸ جدول در یک کوئری، برنامه‌ریز (به خاطر join_collapse_limit و from_collapse_limit) دیگر همه‌ی ترتیب‌ها را امتحان نمی‌کند و ترتیب نوشتن شما مهم می‌شود؛ از ۱۲ جدول به بالا الگوریتم ژنتیک (GEQO) وارد می‌شود و پلن ممکن است بین اجراها فرق کند.
• JOIN ... USING (customer_id) ستون مشترک را یک بار در خروجی می‌آورد؛ اما NATURAL JOIN را هرگز در کد تولید استفاده نکنید: افزودن یک ستون هم‌نام (مثل created_at) بی‌صدا شرط JOIN را عوض می‌کند.
• برای «آیا وجود دارد؟» در برنامه، SELECT EXISTS (SELECT 1 FROM ...) بسیار سریع‌تر از count(*) > 0 است؛ با اولین ردیف متوقف می‌شود.