۱۴۰۴/۰۷/۰۱ Nebular

مثال های ساب کوئری

سوال پیدا کردن کارمندانی که بالاترین حقوق در دپارتمان خود را دارند

این سوال از مهمترین تمرین های SQL است . که اون رو به روش های مختلف (Subquery, JOIN + GROUP BY, CTE, Window Function) مینویسیم و کاملا تریس میکنیم .

📌 داده فرضی برای تریس

جدول Employees:

EmployeeID	Name	DepartmentID	Salary
1	Ali	10	5000
2	Sara	10	7000
3	Reza	20	6000
4	Mina	20	6000
5	Amir	30	4000

روش 1: Subquery

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

تحلیل

برای هر ردیف از E1 (کارمند بیرونی)، یک Subquery اجرا می‌شود.
Subquery بالاترین حقوق در همان DepartmentID را پیدا می‌کند.
اگر حقوق کارمند برابر با اون بود → برگردانده میشه.

خروجی

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000

🔹 مزیت: ساده و واضح.
🔹 عیب: ممکنه روی داده‌های خیلی بزرگ کند باشه چون برای هر کارمند، Subquery اجرا میشه.

🔹 روش 2: JOIN + GROUP BY

SELECT E.Name, E.DepartmentID, E.Salary
FROM Employees E
JOIN (
    SELECT DepartmentID, MAX(Salary) AS MaxSalary
    FROM Employees
    GROUP BY DepartmentID
) M ON E.DepartmentID = M.DepartmentID AND E.Salary = M.MaxSalary;

تحلیل

در Subquery M → برای هر DepartmentID بیشترین حقوق محاسبه میشه.
خروجی M:

DepartmentID	MaxSalary
10	7000
20	6000
30	4000

بعد جدول اصلی Employees با M JOIN میشه:
شرط JOIN میگه: DepartmentID برابر باشه + حقوق برابر با MaxSalary باشه.

خروجی (همون قبلی):

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000
Amir	30	4000

🔹 مزیت: خیلی سریع‌تر از روش Subquery correlated روی دیتاست بزرگ.
🔹 عیب: یک JOIN اضافه باید نوشته بشه.

🔹 روش 3: CTE (خوانایی بیشتر)

WITH MaxSalaries AS (
    SELECT DepartmentID, MAX(Salary) AS MaxSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT E.Name, E.DepartmentID, E.Salary
FROM Employees E
JOIN MaxSalaries M
  ON E.DepartmentID = M.DepartmentID AND E.Salary = M.MaxSalary;

تحلیل

CTE درست مثل همون Subquery در روش JOIN هست، فقط به جای اینکه وسط کوئری بنویسی، بیرون تعریف می‌کنی → خواناتر و قابل استفاده مجدد میشه.

خروجی

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000
Amir	30	4000

🔹 روش 4: Window Function (ROW_NUMBER یا RANK)

SELECT Name, DepartmentID, Salary
FROM (
    SELECT E.*,
           RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rk
    FROM Employees E
) X
WHERE rk = 1;

تحلیل

Window Function → داده‌ها رو پارتیشن‌بندی (گروه‌بندی) می‌کنه بر اساس DepartmentID.
در هر دپارتمان، بر اساس Salary به ترتیب نزولی رتبه می‌ده:
- Sara → رتبه 1 در دپارتمان 10
- Ali → رتبه 2 در دپارتمان 10
- Reza → رتبه 1 در دپارتمان 20
- Mina → رتبه 1 در دپارتمان 20
- Amir → رتبه 1 در دپارتمان 30
در نهایت فقط کسانی که rk = 1 هستند انتخاب میشن.

خروجی

Name	DepartmentID	Salary
Sara	10	7000
Reza	20	6000
Mina	20	6000
Amir	30	4000

🔹 مزیت: خیلی پرقدرت و مناسب مسائل پیچیده‌تر (مثلاً اگر بخوایم Top 3 در هر دپارتمان).
🔹 عیب: برای دیتابیس‌هایی که Window Function ساپورت نمی‌کنن، قابل استفاده نیست.

✅ جمع‌بندی:

Subquery → ساده و مستقیم.
JOIN + GROUP BY → بهینه‌تر روی داده‌های بزرگ.
CTE → همون JOIN ولی خواناتر.
Window Function → پیشرفته‌تر و انعطاف‌پذیرتر (برای Top N).

روش 5: EXISTS :

سوال :

“مشتریانی که مجموع سفارششان بالاتر از میانگین مجموع سفارش همه مشتری‌هاست.”

این همون Aggregation + Subquery کلاسیکه. من همه روش‌ها (Subquery, JOIN+GROUP BY, CTE, Window Function) رو می‌زنم و کامل تریس می‌کنم.

📌 داده فرضی برای تریس

جدول Customers:

CustomerID	Name
1	Ali
2	Sara
3	Reza

جدول Orders:

OrderID	CustomerID	Amount
101	1	200
102	1	300
103	2	400
104	2	600
105	3	150

1. Subquery (ساده‌ترین حالت)

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

تحلیل

زیرکوئری داخلی: مجموع سفارش هر مشتری

CustomerID	Total
1	500
2	1000
3	150

زیرکوئری خارجی: میانگین این مجموع‌ها → (500 + 1000 + 150) ÷ 3 = 550
شرط HAVING: فقط کسانی که SUM(Amount) > 550 → مشتری 2 (Sara).

خروجی

CustomerID	TotalAmount
2	1000

2. JOIN + GROUP BY

راه حل مشابه ولی با JOIN بین Customers و Orders برای وضوح:

SELECT C.CustomerID, C.Name, SUM(O.Amount) AS TotalAmount
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.Name
HAVING SUM(O.Amount) > (
    SELECT AVG(Total) 
    FROM (
        SELECT SUM(Amount) AS Total
        FROM Orders
        GROUP BY CustomerID
    ) AS T
);

تحلیل

همون کار روش 1 رو می‌کنه، فقط Customers رو هم join کردیم که اسم مشتری هم بیاد.

خروجی

CustomerID	Name	TotalAmount
2	Sara	1000

3. CTE (خوانایی بیشتر)

WITH CustomerTotals AS (
    SELECT CustomerID, SUM(Amount) AS TotalAmount
    FROM Orders
    GROUP BY CustomerID
),
AvgTotal AS (
    SELECT AVG(TotalAmount) AS AvgTotal
    FROM CustomerTotals
)
SELECT C.CustomerID, C.Name, CT.TotalAmount
FROM Customers C
JOIN CustomerTotals CT ON C.CustomerID = CT.CustomerID
CROSS JOIN AvgTotal A
WHERE CT.TotalAmount > A.AvgTotal;

تحلیل

CustomerTotals → مجموع سفارش هر مشتری (مثل جدول T در روش اول).
AvgTotal → میانگین اون مجموع‌ها.
CROSS JOIN باعث میشه مقدار AvgTotal به هر ردیف وصل بشه.
شرط WHERE → فقط کسانی که بیشتر از میانگین دارند.

خروجی

CustomerID	Name	TotalAmount
2	Sara	1000

4. Window Function

SELECT CustomerID, Name, TotalAmount
FROM (
    SELECT C.CustomerID, C.Name, SUM(O.Amount) AS TotalAmount,
           AVG(SUM(O.Amount)) OVER () AS AvgTotal
    FROM Customers C
    JOIN Orders O ON C.CustomerID = O.CustomerID
    GROUP BY C.CustomerID, C.Name
) X
WHERE TotalAmount > AvgTotal;

تحلیل

اول SUM(O.Amount) → مجموع سفارش هر مشتری.
AVG(SUM(...)) OVER () → میانگین مجموع‌ها در کل جدول (بدون نیاز به Subquery دوم).
- Customer 1 → 500, Avg = 550
- Customer 2 → 1000, Avg = 550
- Customer 3 → 150, Avg = 550
شرط WHERE → فقط Customer 2

خروجی

CustomerID	Name	TotalAmount
2	Sara	1000

✅ جمع‌بندی

Subquery → مستقیم و پایه‌ای.
JOIN → همون منطق ولی با اسم مشتری.
CTE → خواناتر و مرحله‌ای.
Window Function → حرفه‌ای و خیلی سریع روی دیتای زیاد.

۱. کِی از `WHERE` در Subquery استفاده می‌کنیم؟

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

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

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

اینجا:

هر کارمند (یک ردیف) باید با حداکثر حقوق در دپارتمان خودش مقایسه بشه.
بنابراین Subquery به ازای هر ردیف اجرا میشه.
چون سطح داده «ردیف-به-ردیف» هست → نیاز به GROUP BY در بیرون نداریم.

🔹 ۲. کِی از `GROUP BY` استفاده می‌کنیم؟

وقتی می‌خوای چیزی رو در سطح گروه/مشتری/دپارتمان محاسبه کنی (aggregation).

مثال: مشتریانی که مجموع سفارششان بالاتر از میانگین است

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

اینجا:

اول باید مجموع سفارش هر مشتری محاسبه بشه (aggregation).
پس نیاز به GROUP BY داریم.
بعد این مجموع‌ها با میانگین کل مقایسه میشن.

🔹 تفاوت اصلی

مسائل ردیف-به-ردیف → از Subquery در WHERE استفاده می‌کنیم (مثل کارمند و حداکثر حقوق).
مسائل جمعی (aggregation) → از GROUP BY + HAVING استفاده می‌کنیم (مثل مشتری و مجموع سفارش).

🔹 راهنمای عملی (تحلیل مسئله)

وقتی یک سؤال SQL داری، قبل از نوشتن کوئری از خودت بپرس:

❓ «آیا باید روی هر ردیف جداگانه بررسی کنم یا روی یک گروه از ردیف‌ها؟»
- اگر روی هر ردیف → Subquery در WHERE یا EXISTS.
- اگر روی گروه → GROUP BY + HAVING.
❓ «خروجی من در چه سطحیه؟»
- اگر خروجی در سطح کارمند، سفارش، محصول باشه → بیشتر WHERE + Subquery.
- اگر خروجی در سطح مشتری، دپارتمان یا دسته‌بندی باشه → بیشتر GROUP BY.

✅ به همین خاطر:

برای کارمند و بالاترین حقوق → مقایسه‌ی ردیف-به-ردیف → WHERE Subquery.
برای مشتری و مجموع سفارش‌ها → نیاز به جمع‌زدن و مقایسه گروهی → GROUP BY.

🧾 جدول تشخیص Subquery در `WHERE` vs `GROUP BY`

نوع سؤال / مسئله	سطح داده (ردیف یا گروه)	ابزار مناسب	مثال
مقایسه هر ردیف با یک مقدار محاسبه‌شده	ردیف-به-ردیف	Subquery در `WHERE`	«کارمندانی که حقوقشان = بیشترین حقوق در دپارتمان خودشان»
بررسی وجود یا شرط خاص روی یک ردیف مرتبط	ردیف-به-ردیف	`EXISTS` یا Subquery در `WHERE`	«مشتریانی که سفارش دارند»
نیاز به محاسبه جمع/میانگین/حداکثر برای هر گروه (مشتری، دپارتمان، محصول)	گروهی	`GROUP BY` + Aggregation	«مشتریانی که مجموع سفارششان بالاتر از 1000 است»
مقایسه مقدار گروهی با یک معیار کلی (میانگین کل، بیشترین کل)	گروهی	`GROUP BY` + `HAVING` + Subquery	«مشتریانی که مجموع سفارششان بالاتر از میانگین همه مشتری‌هاست»
انتخاب n-امین رکورد (مثلاً بالاترین حقوق)	ردیف در گروه	Subquery در `WHERE` یا `JOIN` با `MAX()`	«کارمندی که بیشترین حقوق در هر دپارتمان دارد»
انتخاب چند رکورد برتر (Top N)	گروهی ولی پیشرفته	Window Function (ROW_NUMBER / RANK)	«۳ مشتری برتر در هر شهر»

🔹 چند قانون طلایی برای تشخیص

اگر می‌بینی توی صورت سؤال کلمه‌هایی مثل «بیشترین هر دپارتمان»، «کمترین برای هر گروه»، «مجموع هر مشتری» → احتمالاً باید GROUP BY بزنی.
اگر می‌بینی صورت سؤال می‌گه «هر کارمند را با … مقایسه کن» یا «هر ردیف بررسی شود» → معمولاً Subquery در WHERE یا EXISTS.
اگر می‌خوای «روی کل جدول» یک عدد محاسبه کنی (مثل میانگین کل، بیشترین کل) → Subquery سطح بالا.
اگر می‌خوای «روی هر گروه» عدد محاسبه کنی (مثل مجموع سفارش مشتری) → GROUP BY.

✅ مثال تطبیقی:

«کارمندانی که بیشترین حقوق در دپارتمان خود دارند»
→ ردیف-به-ردیف، چون هر کارمند با حداکثر دپارتمان خودش مقایسه میشه.
«مشتریانی که مجموع سفارششان بالاتر از میانگین همه مشتری‌هاست»
→ گروهی، چون اول باید مجموع سفارش هر مشتری محاسبه بشه (GROUP BY).

سوالات تمرینی

❓ سوال 1

کارمندانی را پیدا کنید که کمترین حقوق در دپارتمان خودشان را دارند.

❓ سوال 2

محصولاتی را بیابید که تعداد فروش‌شان بیشتر از میانگین تعداد فروش همه محصولات است.

❓ سوال 3

دانشجویانی را پیدا کنید که نمره آن‌ها در یک درس خاص برابر با بالاترین نمره همان درس است.

❓ سوال 4

فروشندگانی را پیدا کنید که مجموع فروش آن‌ها بالاتر از ۱ میلیون باشد.

❓ سوال 5

مشتریانی را پیدا کنید که حداقل یک سفارش داده‌اند.

✅ پاسخ‌ها + تحلیل

✔️ پاسخ 1: کارمند با کمترین حقوق در دپارتمان

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

اینجا نیاز به مقایسه ردیف-به-ردیف داریم (هر کارمند با مینیمم دپارتمان خودش).
پس WHERE + Subquery مناسبه.

✔️ پاسخ 2: محصولات با تعداد فروش بالاتر از میانگین

SELECT ProductID, COUNT(*) AS SaleCount
FROM Sales
GROUP BY ProductID
HAVING COUNT(*) > (
    SELECT AVG(ProductSaleCount)
    FROM (
        SELECT COUNT(*) AS ProductSaleCount
        FROM Sales
        GROUP BY ProductID
    ) X
);

اینجا باید اول تعداد فروش هر محصول محاسبه بشه (GROUP BY).
بعد با میانگین مقایسه بشه → پس نیاز به GROUP BY + HAVING.

✔️ پاسخ 3: دانشجویان با بالاترین نمره در هر درس

SELECT StudentID, CourseID, Grade
FROM Grades G1
WHERE Grade = (
    SELECT MAX(Grade)
    FROM Grades G2
    WHERE G2.CourseID = G1.CourseID
);

اینجا هر دانشجو (ردیف) باید با بیشترین نمره همان درس مقایسه بشه.
پس WHERE + Subquery.

✔️ پاسخ 4: فروشندگان با مجموع فروش بالای ۱ میلیون

SELECT SalesmanID, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY SalesmanID
HAVING SUM(Amount) > 1000000;

اینجا جمع فروش هر فروشنده مد نظره (aggregation).
پس GROUP BY بدون Subquery هم جواب میده.

✔️ پاسخ 5: مشتریانی که حداقل یک سفارش داده‌اند

SELECT C.CustomerID, C.Name
FROM Customers C
WHERE EXISTS (
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

اینجا شرط «وجود» داریم (حداقل یک سفارش).
پس مناسب‌ترین راه → EXISTS.

🔑 جمع‌بندی

سوال 1 و 3 → ردیف به ردیف → Subquery در WHERE.
سوال 2 و 4 → گروهی → GROUP BY + HAVING.
سوال 5 → وجود → EXISTS.

مثال های ساب کوئری

تحلیل

خروجی

🔹 روش 2: JOIN + GROUP BY

تحلیل

خروجی (همون قبلی):

🔹 روش 3: CTE (خوانایی بیشتر)

تحلیل

خروجی

🔹 روش 4: Window Function (ROW_NUMBER یا RANK)

تحلیل

خروجی

📌 داده فرضی برای تریس

1. Subquery (ساده‌ترین حالت)

تحلیل

خروجی

2. JOIN + GROUP BY

تحلیل

خروجی

3. CTE (خوانایی بیشتر)

تحلیل

خروجی

4. Window Function

تحلیل

خروجی

✅ جمع‌بندی

۱. کِی از WHERE در Subquery استفاده می‌کنیم؟

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

🔹 ۲. کِی از GROUP BY استفاده می‌کنیم؟

مثال: مشتریانی که مجموع سفارششان بالاتر از میانگین است

🔹 تفاوت اصلی

🔹 راهنمای عملی (تحلیل مسئله)

🧾 جدول تشخیص Subquery در WHERE vs GROUP BY

🔹 چند قانون طلایی برای تشخیص

سوالات تمرینی

❓ سوال 1

❓ سوال 2

❓ سوال 3

❓ سوال 4

❓ سوال 5

✅ پاسخ‌ها + تحلیل

✔️ پاسخ 1: کارمند با کمترین حقوق در دپارتمان

✔️ پاسخ 2: محصولات با تعداد فروش بالاتر از میانگین

✔️ پاسخ 3: دانشجویان با بالاترین نمره در هر درس

✔️ پاسخ 4: فروشندگان با مجموع فروش بالای ۱ میلیون

✔️ پاسخ 5: مشتریانی که حداقل یک سفارش داده‌اند

🔑 جمع‌بندی

۱. کِی از `WHERE` در Subquery استفاده می‌کنیم؟

🔹 ۲. کِی از `GROUP BY` استفاده می‌کنیم؟

🧾 جدول تشخیص Subquery در `WHERE` vs `GROUP BY`