JOIN و تجمیع: تکثیر ردیف‌ها و Self Join

گزارش چندجدولی

تحلیل واقعی معمولاً JOIN و GROUP BY را با هم می‌خواهد. مثلاً فروش هر دسته (فقط سفارش‌های پرداخت‌شده):

SELECT p.category, SUM(oi.quantity * p.price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY revenue DESC;

| category | revenue | |---|---| | electronics | 1100 | | bags | 900 | | stationery | 200 | | home | 150 |

ترتیب اجرا (درس قبل): اول JOIN ها و WHERE، بعد گروه‌بندی، بعد SELECT و مرتب‌سازی.

تکثیر ردیف‌ها (fan-out)

وقتی جدولی را با جدول «چند» تا JOIN می‌کنید، ردیف جدول «یک» تکرار می‌شود. هر سفارش چند قلم دارد، پس بعد از orders JOIN order_items ردیف‌های هر سفارش به تعداد اقلامش کپی می‌شوند: ۸ سفارش ← ۱۲ ردیف.

اگر بعد از این JOIN تعداد سفارش‌ها را با COUNT(*) بشمارید، در واقع اقلام را شمرده‌اید. راه‌حل: COUNT(DISTINCT o.id). و هر مقداری که روی سطح سفارش است (مثلاً هزینه‌ی ارسال) را قبل از JOIN با اقلام جمع نزنید، وگرنه چندبار حساب می‌شود.

نشانه‌ی خطر: وقتی بعد از JOIN عددی بزرگ‌تر از انتظار می‌بینید، اول تعداد ردیف‌ها را بعد از هر JOIN بشمارید.

Self Join: جدول با خودش

گاهی باید ردیف‌های یک جدول را با هم مقایسه کرد؛ مثلاً «جفت مشتری‌هایی که هم‌شهری‌اند». جدول را دوبار با دو نام مستعار می‌آوریم:

SELECT a.name AS first, b.name AS second
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;

شرط a.id < b.id دو کار می‌کند: هر ردیف را با خودش جفت نمی‌کند و هر جفت را فقط یک بار می‌دهد (Sara–Ali دارد، Ali–Sara ندارد). چون NULL = NULL درست نیست، مشتریِ بدون شهر با هیچ‌کس جفت نمی‌شود.

منابع و مطالعه‌ی بیشتر

• Joins Between Tables، مستندات PostgreSQL: توضیح رسمی INNER، LEFT و Self Join با مثال.
• SQLBolt: بخش‌های «Multi-table queries with JOINs» را آنجا خودت تایپ و اجرا کن.

JOIN جایی است که اغلب آدم‌ها فقط «حفظ» می‌کنند، نه می‌فهمند. چیزی که این فصل با سؤال‌های «تعداد ردیف چند است؟» تمرین می‌دهد، در ذهن ساختن همان جدول میانی است. و مرور فاصله‌دار، تله‌هایی مثل COUNT(*) بعد از LEFT JOIN را تا وقت لازم در یادت نگه می‌دارد.