زیرپرس‌وجوها: راز پشت کوئری‌های هوشمند در PostgreSQL

در بسیاری از تحلیل‌های واقعی، نمی‌توان پاسخ را تنها با یک دستور SELECT ساده به‌دست آورد.
گاهی لازم است نتیجه‌ی یک پرس‌وجو (Query) را در پرس‌وجوی دیگری استفاده کنیم – مثلاً:

• سفارش‌هایی را پیدا کنیم که هزینه‌ی حمل آن‌ها بیشتر از میانگین همه‌ی سفارش‌ها است.
• مشتریانی را فهرست کنیم که حداقل یک سفارش ثبت کرده‌اند.
• کشورهایی را نشان دهیم که بیش از ۲۰ سفارش دارند.

در چنین مواردی، از زیرپرس‌وجوها (Subqueries) استفاده می‌کنیم؛ یعنی کوئری‌هایی که درون کوئری‌های دیگر قرار می‌گیرند و مانند توابع کمکی، داده‌ی لازم را برای بخش اصلی برمی‌گردانند.

PostgreSQL به ما اجازه می‌دهد زیرپرس‌وجوها را در بخش‌های مختلف بنویسیم:

• در SELECT (برای محاسبات)،
• در WHERE (برای فیلتر کردن)،
• و در FROM (برای ساخت جداول موقت).

همچنین برای منطق پیچیده‌تر، می‌توانیم از ابزارهایی مانند:

• EXISTS (بررسی وجود داده)،
• SOME / ALL (مقایسه با مجموعه‌ای از مقادیر)،
• و CTE یا WITH (برای تعریف زیرپرس‌وجوهای خواناتر و قابل‌استفاده‌ی مجدد) استفاده کنیم.

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

---

🔹 مفهوم اصلی زیرپرس‌وجو (Subquery)

یک زیرپرس‌وجو در واقع یک جدول موقت است که فقط برای همان کوئری وجود دارد.
این جدول موقت می‌تواند:

• یک مقدار اسکالر (مثلاً خروجی تابع تجمیعی)،
• یک ردیف کامل،
• یا یک جدول با چند ردیف و ستون
  را برگرداند.

بنابراین، باید مطمئن باشیم که خروجی زیرپرس‌وجو با شرط یا مقایسه‌ی ما سازگار است:

• اگر خروجی یک مقدار اسکالر است، می‌توانیم آن را با یک ستون مقایسه کنیم (=, >, <, <>).
• اگر خروجی چند مقدار یا چند ستون دارد، باید از عملگرهایی مثل IN, EXISTS یا مقایسه‌ی چندتایی (tuple comparison) استفاده کنیم.

---

🔹 زیرپرس‌وجوی تک‌مقداری (Scalar Subquery)

گاهی نیاز داریم یک مقدار کلی (مثل میانگین، مجموع یا بیشترین مقدار) را محاسبه کنیم و آن را در یک شرط یا محاسبه دیگر استفاده کنیم.

مثال:
نمایش سفارش‌هایی که هزینه‌ی حمل آن‌ها بیشتر از میانگین کل سفارش‌ها است:

SELECT order_id, freight
FROM orders
WHERE freight > (SELECT AVG(freight) FROM orders);

✅ این کوئری با یک زیرپرس‌وجوی اسکالر کار می‌کند که فقط یک مقدار (میانگین) برمی‌گرداند.

تمرین: سفارش‌هایی را پیدا کنید که هزینه‌ی حمل آن‌ها کمتر از میانگین کل باشد.

---

🔹 زیرپرس‌وجو در SELECT (ستون محاسبه‌شده)

اگر بخواهیم بدون JOIN، اطلاعات اضافی برای هر ردیف اضافه کنیم، می‌توانیم از زیرپرس‌وجو در بخش SELECT استفاده کنیم.

مثال: افزودن نام دسته‌بندی هر محصول بدون استفاده از JOIN:

SELECT 
    product_id,
    product_name,
    (SELECT category_name 
     FROM categories c
     WHERE c.category_id = p.category_id) AS category_name
FROM products p;

---

🔹 زیرپرس‌وجو در WHERE (فیلتر مبتنی بر مجموعه)

وقتی خروجی زیرپرس‌وجو چند مقدار است، نمی‌توانیم از مقایسه‌ی ساده استفاده کنیم؛ باید از IN کمک بگیریم.

مثال – نمایش سفارش‌های مشتریان آلمان:

SELECT order_id, customer_id
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE country = 'Germany'
);

تمرین‌ها:

• سفارش‌های مشتریان فرانسه را نمایش دهید.
• محصولاتی که تأمین‌کننده‌ی آن‌ها در بریتانیا است را پیدا کنید.

---

🔹 EXISTS و NOT EXISTS

وقتی فقط می‌خواهیم بررسی کنیم آیا داده‌ای وجود دارد یا خیر، از EXISTS استفاده می‌کنیم.
این روش معمولاً در دیتاست‌های بزرگ از IN سریع‌تر است.

مثال – مشتریانی که سفارش دارند:

SELECT c.customer_id, c.company_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

مثال – مشتریانی که هیچ سفارشی ندارند:

SELECT c.customer_id, c.company_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

---

🔹 مقایسه چند ستونی (Tuple Comparison)

اگر زیرپرس‌وجو چند ستون برمی‌گرداند، می‌توانیم از مقایسه‌ی چندتایی استفاده کنیم.

مثال – سفارش‌هایی که مقصد آن آلمان و شهر برلین است:

SELECT order_id, ship_country, ship_city
FROM orders
WHERE (ship_country, ship_city) = ('Germany', 'Berlin');

---

🔹 SOME و ALL

برای مقایسه‌ی یک مقدار با مجموعه‌ای از مقادیر زیرپرس‌وجو، از SOME (یا ANY) و ALL استفاده می‌کنیم:

• > ALL(subquery) → بزرگ‌تر از تمام مقادیر
• > SOME(subquery) یا > ANY(subquery) → بزرگ‌تر از حداقل یک مقدار

مثال – سفارش‌هایی با هزینه‌ی حمل بیشتر از همه‌ی سفارش‌های آلمان:

SELECT order_id, freight
FROM orders
WHERE freight > ALL(
    SELECT freight
    FROM orders
    WHERE ship_country = 'Germany'
);

مثال – سفارش‌هایی با هزینه‌ی حمل بیشتر از برخی سفارش‌های فرانسه:

SELECT order_id, freight
FROM orders
WHERE freight > SOME(
    SELECT freight
    FROM orders
    WHERE ship_country = 'France'
);

---

🔹 CTE یا WITH Clause

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

مثال – کشورهایی که بیش از ۲۰ سفارش دارند:

WITH country_orders AS (
    SELECT ship_country, COUNT(*) AS total_orders
    FROM orders
    GROUP BY ship_country
)
SELECT *
FROM country_orders
WHERE total_orders > 20;

💡 نکته: سعی کنید کوئری‌های پیچیده را به بخش‌های کوچک‌تر تقسیم کرده و برای هر بخش یک CTE بسازید.

---

🔹 زیرپرس‌وجو در FROM (جدول مشتق‌شده)

گاهی می‌خواهیم خروجی زیرپرس‌وجو را مانند یک جدول موقت در بخش FROM استفاده کنیم تا روی آن محاسبات انجام دهیم.

مثال – محاسبه‌ی میانگین هزینه‌ی حمل بر اساس منطقه:

SELECT region, AVG(freight)
FROM (
    SELECT 
        CASE
            WHEN ship_country IN ('France','Germany','UK') THEN 'Europe'
            WHEN ship_country IN ('Brazil','Argentina') THEN 'South America'
            ELSE 'Other'
        END AS region,
        freight
    FROM orders
) AS regional_data
GROUP BY region;

---

✅ جمع‌بندی

مفهوم	کلمات کلیدی / ساختار	توضیح / کاربرد
زیرپرس‌وجوی اسکالر	(SELECT …) در SELECT یا WHERE	مقایسه با یک مقدار واحد
بررسی وجود	EXISTS, NOT EXISTS	بررسی وجود یا عدم وجود داده
مقایسه چند ستونی	(col1, col2) = (val1, val2)	مقایسه‌ی چند مقدار همزمان
مقایسه با مجموعه	SOME, ALL	مقایسه با مجموعه‌ای از مقادیر
عبارت مشترک (CTE)	WITH name AS (SELECT …)	زیرپرس‌وجوی خواناتر و قابل استفاده‌ی مجدد
جدول مشتق‌شده	زیرپرس‌وجو در FROM	ساخت جدول موقت برای تحلیل
بهینه‌سازی	شکستن کوئری پیچیده به چند مرحله	بهبود خوانایی و کارایی