زیرپرس‌وجو، EXISTS و CTE

زیرپرس‌وجو: پرس‌وجو در دل پرس‌وجو

گاهی برای جواب یک سؤال، اول باید سؤال دیگری جواب داده شود: «کالاهایی که گران‌تر از میانگین قیمت هستند.» جواب اول (میانگین) را در پرانتز می‌نویسیم:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

سه شکل رایج:

• زیرپرس‌وجوی تک‌مقداری (مثل بالا): یک عدد برمی‌گرداند و مثل یک ثابت در مقایسه می‌نشیند.
• IN (زیرپرس‌وجو): چندین مقدار برمی‌گرداند. مثلاً مشتری‌های دارای حداقل یک سفارش paid: WHERE id IN (SELECT customer_id FROM orders WHERE status = 'paid')
• EXISTS (زیرپرس‌وجو): فقط می‌پرسد «حداقل یک ردیف هست؟». معمولاً به ردیف بیرونی وصل می‌شود:

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.id AND o.status = 'cancelled');

تله‌ی NOT IN با NULL

x NOT IN (a, b, NULL) یعنی x <> a AND x <> b AND x <> NULL. آخری همیشه «نامعلوم» است، پس کل شرط هیچ‌وقت درست نمی‌شود و هیچ ردیفی برنمی‌گردد!

-- زیرپرس‌وجو Tehran و NULL (شهر Pouya) را برمی‌گرداند
SELECT name FROM customers
WHERE city NOT IN (SELECT city FROM customers WHERE name IN ('Sara', 'Pouya'));
-- نتیجه: خالی، نه Maryam و Reza

دو راه امن:

1. در زیرپرس‌وجو NULL ها را حذف کنید: ... WHERE city IS NOT NULL
2. از NOT EXISTS استفاده کنید که با NULL مشکل ندارد (ترجیح حرفه‌ای‌ها).

برای «مشتری‌های بدون سفارش»، NOT EXISTS یا همان LEFT JOIN + IS NULL (درس قبل) هر دو امن‌اند.

CTE با WITH: اسم گذاشتن روی گام‌ها

وقتی یک پرس‌وجو چند گام دارد (اول مجموع هر سفارش، بعد میانگین مجموع‌ها)، زیرپرس‌وجوهای تودرتو سخت‌خوان می‌شوند. CTE (Common Table Expression) به نتیجه‌ی یک گام اسم می‌دهد:

WITH order_totals AS (
  SELECT oi.order_id, SUM(oi.quantity * p.price) AS total
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY oi.order_id
)
SELECT AVG(total) AS avg_order_value
FROM order_totals;

مثل یک جدول موقت است که فقط برای همین پرس‌وجو وجود دارد. می‌توان چند CTE را با کاما پشت هم نوشت و هرکدام می‌تواند از قبلی‌ها استفاده کند. کد را به گام‌های کوچک و قابل‌آزمایش تقسیم می‌کند، و همین کار را در گزارش‌های واقعی می‌کنید.