ساب‌کوئری در SQL Server

ساب‌کوئری یا Query تو در تو، کوئری‌ای است که داخل کوئری دیگر قرار می‌گیرد و نتیجه آن به کوئری اصلی داده می‌شود.

ساختار کلی:

SELECT ... 
FROM ... 
WHERE Column OPERATOR (SELECT ... FROM ... WHERE ...);

---

1️⃣ انواع ساب‌کوئری‌ها

1. ساب‌کوئری در قسمت WHERE (Scalar / Single value)

• برای مقایسه یک مقدار استفاده می‌شود.
• خروجی باید یک مقدار یا یک ستون واحد باشد.

🔹 مثال:

-- پیدا کردن کارمندانی که حقوقشان بالاتر از میانگین حقوق است
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

• نکته تستی: نمی‌توان از Aggregate مثل AVG() مستقیماً در WHERE بدون ساب‌کوئری استفاده کرد.

---

2. ساب‌کوئری با IN / NOT IN

• وقتی بخواهیم بررسی کنیم مقدار یک ستون در یک مجموعه قرار دارد یا نه.

🔹 مثال:

-- مشتریانی که سفارش دارند
SELECT * 
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

• NOT IN → مشتریانی که سفارشی ندارند
• نکته تستی: اگر ساب‌کوئری شامل NULL باشد، NOT IN می‌تواند هیچ رکوردی برنگرداند.
  • برای این مشکل بهتر است از LEFT JOIN ... IS NULL استفاده شود.

---

3. ساب‌کوئری در SELECT (Scalar Subquery)

• یک مقدار تک را به هر ردیف اضافه می‌کند.

🔹 مثال:

SELECT Name,
       (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;

• به هر مشتری تعداد سفارش‌ها اضافه می‌شود.

---

4. ساب‌کوئری در FROM (Derived Table / Inline View)

• ساب‌کوئری به صورت جدول موقت در FROM استفاده می‌شود.

🔹 مثال:

SELECT AvgSalaryByDept.DepartmentID, AvgSalaryByDept.AvgSalary
FROM (SELECT DepartmentID, AVG(Salary) AS AvgSalary
      FROM Employees
      GROUP BY DepartmentID) AS AvgSalaryByDept
WHERE AvgSalaryByDept.AvgSalary > 5000;

• نکته تستی: هر ساب‌کوئری در FROM باید Alias داشته باشد (AS AvgSalaryByDept).

---

5. ساب‌کوئری در HAVING

• وقتی می‌خواهیم روی گروه‌ها شرط بگذاریم.

🔹 مثال:

SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM Employees);

• گروه‌هایی که میانگین حقوق بالاتر از میانگین کل هستند، انتخاب می‌شوند.

---

6. ساب‌کوئری Correlated vs Non-Correlated

🔹 Non-Correlated (غیر وابسته)

• مستقل است و فقط یکبار اجرا می‌شود.
• مثال:

SELECT Name FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

🔹 Correlated (وابسته)

• به هر ردیف کوئری بیرونی وابسته است و برای هر ردیف اجرا می‌شود.
• مثال:

SELECT Name, Salary
FROM Employees E1
WHERE Salary > (SELECT AVG(Salary) 
                FROM Employees E2 
                WHERE E2.DepartmentID = E1.DepartmentID);

• هر ردیف E1 میانگین حقوق همان دپارتمان را بررسی می‌کند.

---

2️⃣ نکات تستی مهم درباره ساب‌کوئری‌ها

1. WHERE نمی‌تواند مستقیم از Aggregate بدون ساب‌کوئری استفاده کند.
2. NOT IN + NULL → می‌تواند باعث نشدن خروجی شود.
3. ساب‌کوئری در FROM حتماً باید Alias داشته باشد.
4. ساب‌کوئری Correlated کندتر از Non-Correlated است، چون برای هر ردیف اجرا می‌شود.
5. می‌توان ساب‌کوئری را در SELECT, WHERE, FROM, HAVING استفاده کرد.
6. برای ساب‌کوئری که چند مقدار برمی‌گرداند، از IN، ANY، ALL استفاده می‌شود.

---

3️⃣ مثال‌های ترکیبی

🔹 مثال 1: پیدا کردن مشتریانی که بیشتر از میانگین سفارشات همه مشتری‌ها خرید کردند

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > (SELECT AVG(TotalAmount) 
                      FROM (SELECT SUM(Amount) AS TotalAmount
                            FROM Orders
                            GROUP BY CustomerID) AS SubQ);

متن سوال:

«پیدا کردن مشتریانی که بیشتر از میانگین سفارشات همه مشتری‌ها خرید کردند»

اینجا دو برداشت ممکن داریم:

---

1️⃣ برداشت اول (میانگین کل سفارش‌ها – AVG(OrderAmount))

اگر بگیم “سفارشات همه مشتری‌ها” یعنی تک‌تک رکوردهای جدول Orders، پس:

• هر رکورد یک سفارش است.
• میانگین تمام رکوردها = AVG(OrderAmount)

پس سوال میشه:
«مشتریانی که میانگین خریدشان بیشتر از میانگین همه سفارش‌هاست»

کد:

SELECT CustomerID, AVG(OrderAmount) AS AvgPurchase
FROM Orders
GROUP BY CustomerID
HAVING AVG(OrderAmount) > (SELECT AVG(OrderAmount) FROM Orders);

📌 اینجا داریم میانگین خرید هر مشتری رو با میانگین تمام سفارش‌ها مقایسه می‌کنیم.
این جواب درست هست اگر متن سوال رو اینطوری تفسیر کنیم.

---

2️⃣ برداشت دوم (میانگین مجموع خرید مشتری‌ها)

اگر بگیم “میانگین سفارشات همه مشتری‌ها” یعنی اول هر مشتری رو یک واحد در نظر بگیریم، ببینیم هر کدوم در کل چقدر خرید کرده‌اند، بعد میانگین این مجموع‌ها رو بگیریم.

اینجا نیاز به GROUP BY داریم برای محاسبه مجموع هر مشتری.
کد:

SELECT CustomerID, SUM(OrderAmount) AS TotalPurchase
FROM Orders
GROUP BY CustomerID
HAVING SUM(OrderAmount) > (
    SELECT AVG(TotalPurchase)
    FROM (
        SELECT CustomerID, SUM(OrderAmount) AS TotalPurchase
        FROM Orders
        GROUP BY CustomerID
    ) AS CustomerTotals
);

📌 اینجا داریم کل خرید هر مشتری رو با میانگین کل خرید مشتری‌ها مقایسه می‌کنیم.

---

🔹 کدوم درسته؟

این بستگی به منظور طراح سوال داره:

• اگر تأکید روی “هر سفارش” باشه → برداشت 1 (AVG(OrderAmount)).
• اگر تأکید روی “هر مشتری” باشه → برداشت 2 (AVG(SUM(OrderAmount))).

چون توی جمله گفته «میانگین سفارشات همه مشتری‌ها»، بعضی‌ها تفسیر می‌کنن یعنی تک‌تک سفارش‌ها (برداشت اول).
اما توی خیلی از تمرین‌های کلاسیک دیتابیس منظورشون برداشت دومه، چون دنبال مجموع خرید مشتری‌ها هستن.

🔹 مثال 2: پیدا کردن کارمندانی که بالاترین حقوق در دپارتمان خود دارند

SELECT Name, DepartmentID, Salary
FROM Employees E1
WHERE Salary = (SELECT MAX(Salary)
                FROM Employees E2
                WHERE E2.DepartmentID = E1.DepartmentID);

• این یک Correlated Subquery است.

🔹 مثال 3: استفاده در SELECT

SELECT Name,
       (SELECT COUNT(*) 
        FROM Orders 
        WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;