۱۴۰۴/۰۶/۲۹ Nebular

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

ساب‌کوئری‌ها به دو دسته اصلی تقسیم می‌شوند:

Non-Correlated Subquery (ساب‌کوئری غیر وابسته)
Correlated Subquery (ساب‌کوئری وابسته)

1️⃣ Non-Correlated Subquery (غیر وابسته)

🔹 تعریف ساده:

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

🔹 کاربردها:

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

🔹 مثال:

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

🔹 توضیح مثال:

ساب‌کوئری (SELECT AVG(Salary) FROM Employees) → یکبار اجرا می‌شود و میانگین کل حقوق‌ها را محاسبه می‌کند.
سپس هر ردیف جدول Employees بررسی می‌شود که آیا حقوق آن از میانگین بیشتر است یا نه.
خروجی = لیست کارمندانی که بالاتر از میانگین حقوق دارند.

🔹 نکته تستی:

Aggregate مثل AVG، MAX، MIN بدون ساب‌کوئری نمی‌تواند مستقیماً در WHERE استفاده شود.

2️⃣ Correlated Subquery (وابسته)

🔹 تعریف ساده:

ساب‌کوئری به هر ردیف کوئری بیرونی وابسته است.
برای هر ردیف کوئری اصلی اجرا می‌شود.
معمولاً وقتی می‌خواهیم مقدار خاصی را با مقادیر مرتبط به همان ردیف مقایسه کنیم، استفاده می‌شود.

🔹 کاربردها:

پیدا کردن رکوردهای برتر در گروه خاص (مثل بیشترین حقوق هر دپارتمان)
مقایسه هر ردیف با داده‌های مرتبط خودش

🔹 مثال:

-- پیدا کردن کارمندانی که بالاترین حقوق در دپارتمان خود را دارند
SELECT Name, DepartmentID, Salary
FROM Employees E1
WHERE Salary = (SELECT MAX(Salary)
                FROM Employees E2
                WHERE E2.DepartmentID = E1.DepartmentID);

🔹 توضیح مثال:

E1 → کوئری اصلی (هر ردیف جدول Employees)
ساب‌کوئری: SELECT MAX(Salary) FROM Employees E2 WHERE E2.DepartmentID = E1.DepartmentID
- برای هر ردیف E1 اجرا می‌شود و بیشترین حقوق در همان دپارتمان را محاسبه می‌کند.
سپس شرط مقایسه می‌شود که حقوق ردیف اصلی با بیشترین حقوق همان دپارتمان برابر است یا نه.
خروجی = فقط کارمندانی که بالاترین حقوق در دپارتمان خود دارند.

🔹 تفاوت کلیدی

ویژگی	Non-Correlated	Correlated
وابستگی به کوئری بیرونی	ندارد	دارد
تعداد اجرا	یک بار	برای هر ردیف کوئری اصلی اجرا می‌شود
سرعت	سریعتر	کندتر (به دلیل اجرای متعدد)
مثال	حقوق بالاتر از میانگین کل	بالاترین حقوق در هر دپارتمان

🔹 مثال دیگر Correlated

پیدا کردن مشتریانی که بیشترین تعداد سفارش را دارند:

SELECT CustomerID, Name
FROM Customers C1
WHERE CustomerID = (
    SELECT TOP 1 CustomerID
    FROM Orders O
    WHERE O.CustomerID = C1.CustomerID
    ORDER BY COUNT(*) DESC
);

این هم یک Correlated Subquery است، چون ساب‌کوئری به ردیف C1 وابسته است.

🔹 نکات تستی Correlated Subquery

همیشه از Alias برای جدول ساب‌کوئری و جدول اصلی استفاده کنید.
کند است، مخصوصاً روی جداول بزرگ → می‌توان با JOIN جایگزین کرد.
معمولاً برای محاسبه مقدار مرتبط با همان ردیف استفاده می‌شود (مثل حداکثر، حداقل، تعداد، جمع).
هر شرطی که داخل ساب‌کوئری به کوئری بیرونی وابسته باشد → Correlated است.

برای همچین سوالاتی راه حل های دیگری هم وجود دارد مثلا استفاده از join

در SQL برای بیشتر سوالات مثل “مقایسه هر رکورد با Aggregate کل جدول” یا “پیدا کردن رکوردهایی با بیشترین/کمترین مقدار در گروه”، همیشه چند روش وجود دارد.

بیاییم راه‌های جایگزین برای مثال Correlated Subquery را بررسی کنیم:

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

روش 1: Correlated Subquery

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

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

روش 2: استفاده از `JOIN` با Derived Table (ساب‌کوئری در FROM)

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

توضیح:
1. ساب‌کوئری داخل FROM → بیشترین حقوق هر دپارتمان را محاسبه می‌کند.
2. JOIN → به جدول اصلی متصل می‌شود و فقط کارمندانی که حقوقشان با MaxSalary برابر است، انتخاب می‌شوند.
مزیت: سریع‌تر از Correlated Subquery روی جدول بزرگ

روش 3: استفاده از `RANK()` یا `ROW_NUMBER()` (Window Function)

SELECT Name, DepartmentID, Salary
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rnk
    FROM Employees
) AS Ranked
WHERE rnk = 1;

توضیح:
- PARTITION BY DepartmentID → گروه‌بندی بر اساس دپارتمان
- ORDER BY Salary DESC → مرتب‌سازی بر اساس حقوق
- RANK() → به هر کارمند یک رتبه داخل گروه می‌دهد
- شرط WHERE rnk = 1 → فقط کسانی که بالاترین حقوق دارند
مزیت: بسیار بهینه و قابل توسعه، حتی برای چند رتبه بالاتر یا شرط‌های پیچیده

2️⃣ مثال: مقایسه حقوق با میانگین حقوق کل

روش 1: Non-Correlated Subquery

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

ساده و مستقیم

روش 2: استفاده از JOIN (تقریباً غیر رایج برای این حالت)

SELECT E.Name, E.Salary
FROM Employees E
JOIN (SELECT AVG(Salary) AS AvgSalary FROM Employees) AS AvgTbl
ON E.Salary > AvgTbl.AvgSalary;

همان نتیجه را می‌دهد
مزیت: اگر بخواهید AvgSalary را با چند جدول دیگر ترکیب کنید، JOIN مفید است

روش 3: استفاده از CTE

WITH AvgSalaryCTE AS (
    SELECT AVG(Salary) AS AvgSalary FROM Employees
)
SELECT E.Name, E.Salary
FROM Employees E, AvgSalaryCTE A
WHERE E.Salary > A.AvgSalary;

مزیت: واضح و قابل توسعه برای کوئری‌های پیچیده

✅ جمع‌بندی

هدف	Correlated Subquery	JOIN + Derived Table	Window Function / CTE
پیدا کردن رکوردهای با Max/Min در گروه	ساده ولی کند روی جدول بزرگ	سریع‌تر و قابل فهم	بسیار بهینه، انعطاف‌پذیر
مقایسه با Aggregate کل جدول	Non-Correlated Subquery	JOIN یا CTE	مناسب برای کوئری‌های پیچیده یا ترکیبی

💡 نکته تستی:
در آزمون SQL معمولاً می‌توان هم جواب با ساب‌کوئری را انتخاب کرد و هم با JOIN یا Window Function. اما در جداول بزرگ و دنیای واقعی، روش JOIN یا Window Function سریع‌تر و استانداردتر است.

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

بیاییم کوئری Correlated Subquery شما را به صورت کامل تریس کنیم و مرحله به مرحله اجرا را بررسی کنیم تا دقیقاً بفهمیم چه اتفاقی می‌افتد.

🏗️ کوئری اصلی

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

1️⃣ مرحله اول: Outer Query و Inner Query

Outer Query (E1) → جدول Employees را می‌گیرد و هر ردیف را بررسی می‌کند.
Inner Query (E2) → برای هر ردیف E1 اجرا می‌شود و بیشترین حقوق همان دپارتمان را محاسبه می‌کند.
بنابراین این یک Correlated Subquery است: Inner Query به ستون Outer Query وابسته است (E1.DepartmentID).

2️⃣ فرض جدول Employees نمونه

EmpID	Name	DepartmentID	Salary
1	Ali	1	5000
2	Sara	1	7000
3	Reza	2	6000
4	Mina	2	6500
5	Arash	3	8000

3️⃣ مرحله به مرحله اجرا

🔹 Outer Query ردیف 1 → E1 = Ali, DepartmentID = 1, Salary = 5000

Inner Query برای E1 = Ali:

SELECT MAX(Salary)
FROM Employees E2
WHERE E2.DepartmentID = 1;

داده‌های E2 که DepartmentID = 1 هستند: Ali (5000), Sara (7000)
MAX(Salary) = 7000
شرط Outer: 5000 = 7000 → False → Ali حذف می‌شود

🔹 Outer Query ردیف 2 → E1 = Sara, DepartmentID = 1, Salary = 7000

Inner Query برای E1 = Sara:

SELECT MAX(Salary)
FROM Employees E2
WHERE E2.DepartmentID = 1;

MAX(Salary) = 7000
شرط Outer: 7000 = 7000 → True → Sara انتخاب می‌شود

🔹 Outer Query ردیف 3 → E1 = Reza, DepartmentID = 2, Salary = 6000

Inner Query برای E1 = Reza:

SELECT MAX(Salary)
FROM Employees E2
WHERE E2.DepartmentID = 2;

MAX(Salary) = 6500
شرط Outer: 6000 = 6500 → False → Reza حذف می‌شود

🔹 Outer Query ردیف 4 → E1 = Mina, DepartmentID = 2, Salary = 6500

Inner Query برای E1 = Mina:

SELECT MAX(Salary)
FROM Employees E2
WHERE E2.DepartmentID = 2;

MAX(Salary) = 6500
شرط Outer: 6500 = 6500 → True → Mina انتخاب می‌شود

🔹 Outer Query ردیف 5 → E1 = Arash, DepartmentID = 3, Salary = 8000

Inner Query برای E1 = Arash:

SELECT MAX(Salary)
FROM Employees E2
WHERE E2.DepartmentID = 3;

MAX(Salary) = 8000
شرط Outer: 8000 = 8000 → True → Arash انتخاب می‌شود

4️⃣ خروجی نهایی

Name	DepartmentID	Salary
Sara	1	7000
Mina	2	6500
Arash	3	8000

فقط کسانی که حقوقشان برابر با بالاترین حقوق دپارتمانشان است، باقی مانده‌اند ✅

5️⃣ نکات مهم Trace

Correlated Subquery → Inner Query برای هر ردیف Outer Query اجرا می‌شود.
Alias (E1, E2) ضروری است تا SQL بداند ستون‌ها به کدام جدول اشاره دارند.
هر Inner Query مقدار MAX(Salary) همان دپارتمان را محاسبه می‌کند و Outer Query با آن مقایسه می‌کند.
روی جدول بزرگ ممکن است کند شود → جایگزین سریع‌تر: JOIN + GROUP BY یا Window Function.

بیاییم همان مسئله را (پیدا کردن کارمندانی که بالاترین حقوق در دپارتمان خود را دارند) با JOIN + GROUP BY و Window Function حل کنیم و مرحله به مرحله تریس کنیم تا ببینیم چطور اجرا می‌شوند.

1️⃣ روش 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
) AS DeptMax
ON E.DepartmentID = DeptMax.DepartmentID
   AND E.Salary = DeptMax.MaxSalary;

مرحله به مرحله تریس

فرض جدول Employees:

EmpID	Name	DepartmentID	Salary
1	Ali	1	5000
2	Sara	1	7000
3	Reza	2	6000
4	Mina	2	6500
5	Arash	3	8000

1️⃣ ساب‌کوئری در FROM:

SELECT DepartmentID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DepartmentID;

خروجی ساب‌کوئری (DeptMax):

DepartmentID	MaxSalary
1	7000
2	6500
3	8000

2️⃣ JOIN با جدول اصلی

شرط JOIN:

E.DepartmentID = DeptMax.DepartmentID AND E.Salary = DeptMax.MaxSalary

بررسی هر ردیف:

E.Name	E.DepartmentID	E.Salary	DeptMax.MaxSalary	Match?
Ali	1	5000	7000	No
Sara	1	7000	7000	Yes
Reza	2	6000	6500	No
Mina	2	6500	6500	Yes
Arash	3	8000	8000	Yes

فقط ردیف‌هایی که Match = Yes هستند، در خروجی قرار می‌گیرند.

3️⃣ خروجی نهایی:

Name	DepartmentID	Salary
Sara	1	7000
Mina	2	6500
Arash	3	8000

✅ مشابه Correlated Subquery، ولی سریع‌تر روی جداول بزرگ

2️⃣ روش Window Function

کد:

SELECT Name, DepartmentID, Salary
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rnk
    FROM Employees
) AS Ranked
WHERE rnk = 1;

مرحله به مرحله تریس

1️⃣ بخش داخلی:

SELECT *,
       RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rnk
FROM Employees;

PARTITION BY DepartmentID → گروه‌بندی داخلی برای هر دپارتمان
ORDER BY Salary DESC → مرتب‌سازی از بیشترین حقوق به کمترین
RANK() → به هر ردیف رتبه در گروه می‌دهد

Name	DepartmentID	Salary	rnk
Ali	1	5000	2
Sara	1	7000	1
Reza	2	6000	2
Mina	2	6500	1
Arash	3	8000	1

2️⃣ بخش بیرونی:

WHERE rnk = 1

فقط ردیف‌هایی که رتبه 1 دارند انتخاب می‌شوند → یعنی بالاترین حقوق هر دپارتمان

3️⃣ خروجی نهایی:

Name	DepartmentID	Salary
Sara	1	7000
Mina	2	6500
Arash	3	8000

✅ نتیجه دقیقاً مشابه دو روش دیگر

🔹 مقایسه روش‌ها

روش	توضیح	مزایا	معایب
Correlated Subquery	ساب‌کوئری وابسته به Outer Query	ساده و مستقیم	کند روی جدول بزرگ
JOIN + GROUP BY	محاسبه Max در ساب‌کوئری و JOIN	سریع، واضح	کمی طولانی‌تر
Window Function (RANK/ROW_NUMBER)	محاسبه رتبه در هر گروه	بهینه، انعطاف‌پذیر، برای چند رتبه برتر مناسب	نیازمند نسخه SQL Server پشتیبانی‌کننده

❓ مسئله

پیدا کردن مشتریانی که بیشتر از میانگین مجموع سفارشات همه مشتری‌ها خرید کرده‌اند

فرض کنیم دو جدول داریم:

Customers

CustomerID	Name
1	Ali
2	Sara
3	Reza

Orders

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

1️⃣ روش حل با ساب‌کوئری (Subquery)

کد SQL:

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

2️⃣ مرحله به مرحله تریس

🔹 مرحله 1: محاسبه مجموع سفارشات هر مشتری

SELECT SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID;

خروجی (ساب‌کوئری داخلی):
| CustomerID | TotalAmount |
|————|————-|
| 1 | 300 |
| 2 | 150 |
| 3 | 500 |

🔹 مرحله 2: محاسبه میانگین مجموع سفارشات

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

محاسبه میانگین:

AVG(300,150,500)=300+150+5003=316.67AVG(300, 150, 500) = \frac{300+150+500}{3} = 316.67AVG(300,150,500)=3300+150+500=316.67

پس میانگین = 316.67

🔹 مرحله 3: محاسبه مجموع سفارشات هر مشتری و مقایسه با میانگین

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

مجموع سفارشات مشتری‌ها:

CustomerID	Name	TotalAmount
1	Ali	300
2	Sara	150
3	Reza	500

مقایسه با میانگین = 316.67
- Ali → 300 < 316.67 → حذف
- Sara → 150 < 316.67 → حذف
- Reza → 500 > 316.67 → انتخاب ✅

🔹 خروجی نهایی

CustomerID	Name	TotalAmount
3	Reza	500

3️⃣ روش جایگزین: با CTE

کد:

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

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

4️⃣ روش جایگزین: با Window Function

کد:

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

توضیح:
- AVG(SUM(O.Amount)) OVER () → میانگین کل مجموع سفارشات محاسبه می‌شود (Window Function)
- سپس هر ردیف با این میانگین مقایسه می‌شود
خروجی همانند روش‌های قبل است.

✅ جمع‌بندی

روش	توضیح	مزایا
ساب‌کوئری تو در تو (Nested Subquery)	ساده و واضح	کلاسیک، آزمونی
CTE	خواناتر و قابل توسعه	واضح و قابل استفاده در کوئری‌های پیچیده
Window Function	سریع و حرفه‌ای	مناسب جداول بزرگ و تحلیل داده‌ها

SQL Query Cheat Sheet برای حل سریع مسائل

نوع مسئله	نشانه‌ها در سؤال	ابزار مناسب	الگوی کوئری پایه
انتخاب ساده (Filtering)	«مشتریانی که در تهران هستند» یا «حقوق > 5000»	`WHERE`	`SELECT ... FROM Table WHERE شرط`
مقایسه با یک مقدار محاسبه‌شده	«کارمندانی که حقوق‌شان بالاتر از میانگین است»	Subquery (غیرمرتبط)	`WHERE Salary > (SELECT AVG(Salary) FROM Employees)`
مقایسه داخل همان جدول (Correlated)	«کارمندانی که بیشترین حقوق در دپارتمان خود را دارند»	Subquery (مرتبط)	`WHERE Salary = (SELECT MAX(Salary) FROM Employees E2 WHERE E2.DepartmentID = E1.DepartmentID)`
ترکیب دو جدول	«لیست مشتری‌ها با سفارش‌هایشان»	`INNER JOIN`	`SELECT ... FROM Customers C JOIN Orders O ON C.CustomerID = O.CustomerID`
تمام داده‌های یک جدول + داده‌های مرتبط (اختیاری)	«همه مشتری‌ها + حتی اگر سفارش نداشته باشند»	`LEFT JOIN`	`... LEFT JOIN ... ON ... WHERE O.CustomerID IS NULL` (برای پیدا کردن بدون سفارش)
خلاصه‌سازی	«تعداد سفارش هر مشتری» یا «مجموع حقوق هر دپارتمان»	`GROUP BY + Aggregates`	`SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID`
فیلتر روی نتایج خلاصه‌شده	«مشتریانی که بیش از 2 سفارش دارند»	`HAVING`	`... GROUP BY CustomerID HAVING COUNT(*) > 2`
مقایسه چند مجموعه	«مشتریانی که سفارش دارند اما فاکتور ندارند»	Set Operations (`EXCEPT`, `INTERSECT`) یا `NOT EXISTS`	`SELECT ... FROM Orders EXCEPT SELECT ... FROM Invoices`
یافتن یکتاها	«تعداد شهرهای مختلف مشتریان»	`DISTINCT`	`SELECT COUNT(DISTINCT City) FROM Customers`
حذف یا تغییر داده‌ها	«حذف سفارش‌های مشتری X» یا «افزایش حقوق دپارتمان Y»	`DELETE`, `UPDATE`	`DELETE FROM Orders WHERE CustomerID = 5` `UPDATE Employees SET Salary = Salary*1.1 WHERE DepartmentID = 2`

📌 نکات تستی طلایی:

NOT IN vs NOT EXISTS vs LEFT JOIN
برای پیدا کردن داده‌هایی که وجود ندارند، بهتره از NOT EXISTS یا LEFT JOIN ... IS NULL استفاده کنی. چون NOT IN با NULL مشکل‌ساز میشه.
HAVING فقط بعد از GROUP BY میاد، برای شرط روی توابع تجمعی.
شرط روی ردیف‌ها باید در WHERE باشه.
Correlated Subquery همیشه به ردیف بیرونی اشاره می‌کنه.
تستی‌ها دوست دارن اینو قاطی کنن.
JOIN vs Subquery
معمولاً هر چیزی که با Subquery میشه نوشت، با JOIN هم قابل حله (ولی لزوماً بهینه نیست).

🎯 مثال سریع با این Cheat Sheet

سؤال: “مشتریانی که بیشتر از 2 سفارش دارند”

راه‌حل با GROUP BY

اگر جدول Orders داریم (هر ردیف = یک سفارش):

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 2;

🔎 توضیح:

GROUP BY CustomerID → همه سفارش‌های هر مشتری رو کنار هم جمع می‌کنیم.
COUNT(*) → تعداد سفارش هر مشتری.
HAVING COUNT(*) > 2 → فقط مشتری‌هایی که بیشتر از ۲ سفارش دارن رو نگه می‌داریم.

2️⃣ راه‌حل با Window Function

SELECT DISTINCT CustomerID
FROM (
    SELECT CustomerID, COUNT(*) OVER(PARTITION BY CustomerID) AS OrderCount
    FROM Orders
) t
WHERE OrderCount > 2;

🔎 توضیح:

COUNT(*) OVER(PARTITION BY CustomerID) → تعداد سفارش‌های هر مشتری محاسبه میشه و روی همه ردیف‌هاش تکرار میشه.
بعد با DISTINCT یا ساب‌کوئری، فقط یک بار CustomerID رو می‌گیریم.

📌 خروجی دقیقاً مثل روش اول میشه.

🔑 نکته استادانه

برای این مسئله ساده، روش GROUP BY بهترین و بهینه‌ترین انتخابه.
ولی اگر بخوای همزمان اطلاعات دیگه از سفارش‌ها (مثلاً تاریخ آخرین سفارش، مقدار کل خرید، …) رو هم بیاری، Window Function خیلی قدرتمندتر میشه چون مجبور نیستی چند بار JOIN یا Subquery بزنی.

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

1️⃣ Non-Correlated Subquery (غیر وابسته)

🔹 تعریف ساده:

🔹 کاربردها:

🔹 مثال:

🔹 توضیح مثال:

2️⃣ Correlated Subquery (وابسته)

🔹 تعریف ساده:

🔹 کاربردها:

🔹 مثال:

🔹 توضیح مثال:

🔹 تفاوت کلیدی

🔹 مثال دیگر Correlated

🔹 نکات تستی Correlated Subquery

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

روش 1: Correlated Subquery

روش 2: استفاده از JOIN با Derived Table (ساب‌کوئری در FROM)

روش 3: استفاده از RANK() یا ROW_NUMBER() (Window Function)

2️⃣ مثال: مقایسه حقوق با میانگین حقوق کل

روش 1: Non-Correlated Subquery

روش 2: استفاده از JOIN (تقریباً غیر رایج برای این حالت)

روش 3: استفاده از CTE

✅ جمع‌بندی

🏗️ کوئری اصلی

1️⃣ مرحله اول: Outer Query و Inner Query

2️⃣ فرض جدول Employees نمونه

3️⃣ مرحله به مرحله اجرا

🔹 Outer Query ردیف 1 → E1 = Ali, DepartmentID = 1, Salary = 5000

🔹 Outer Query ردیف 2 → E1 = Sara, DepartmentID = 1, Salary = 7000

🔹 Outer Query ردیف 3 → E1 = Reza, DepartmentID = 2, Salary = 6000

🔹 Outer Query ردیف 4 → E1 = Mina, DepartmentID = 2, Salary = 6500

🔹 Outer Query ردیف 5 → E1 = Arash, DepartmentID = 3, Salary = 8000

4️⃣ خروجی نهایی

5️⃣ نکات مهم Trace

1️⃣ روش JOIN + GROUP BY

کد:

مرحله به مرحله تریس

فرض جدول Employees:

1️⃣ ساب‌کوئری در FROM:

2️⃣ JOIN با جدول اصلی

3️⃣ خروجی نهایی:

2️⃣ روش Window Function

کد:

مرحله به مرحله تریس

1️⃣ بخش داخلی:

2️⃣ بخش بیرونی:

3️⃣ خروجی نهایی:

🔹 مقایسه روش‌ها

❓ مسئله

1️⃣ روش حل با ساب‌کوئری (Subquery)

کد SQL:

2️⃣ مرحله به مرحله تریس

🔹 مرحله 1: محاسبه مجموع سفارشات هر مشتری

🔹 مرحله 2: محاسبه میانگین مجموع سفارشات

🔹 مرحله 3: محاسبه مجموع سفارشات هر مشتری و مقایسه با میانگین

🔹 خروجی نهایی

3️⃣ روش جایگزین: با CTE

کد:

4️⃣ روش جایگزین: با Window Function

کد:

✅ جمع‌بندی

SQL Query Cheat Sheet برای حل سریع مسائل

📌 نکات تستی طلایی:

🎯 مثال سریع با این Cheat Sheet

راه‌حل با GROUP BY

2️⃣ راه‌حل با Window Function

🔑 نکته استادانه

روش 2: استفاده از `JOIN` با Derived Table (ساب‌کوئری در FROM)

روش 3: استفاده از `RANK()` یا `ROW_NUMBER()` (Window Function)