📌 CTE چیست؟

CTE یا عبارت جدول مشترک یک ساختار موقتی است که به ما اجازه می‌دهد نتیجه یک کوئری را مثل یک جدول مجازی تعریف کنیم و بعد از آن در کوئری‌های بعدی (همان کوئری جاری) استفاده کنیم.

🔹 به شکل ساده‌تر:
CTE مثل یک View موقت است که فقط در همان اجرای کوئری معتبر است.

---

📌 نحوه تعریف CTE

WITH CTE_Name (Column1, Column2, ...)
AS
(
    -- Query Definition
    SELECT ...
    FROM ...
    WHERE ...
)
-- استفاده از CTE
SELECT * FROM CTE_Name;

---

📌 ویژگی‌های مهم CTE

1. موقتی بودن: فقط در همان کوئری‌ای که تعریف شده معتبر است.
2. خوانایی بالا: کوئری‌های پیچیده را خواناتر و قابل نگهداری می‌کند.
3. امکان بازگشتی (Recursive): یکی از مزیت‌های اصلی CTE این است که می‌تواند بازگشتی باشد (مثل پیدا کردن سلسله مراتب سازمانی).
4. جایگزین Subquery و Derived Table: برای ساده‌تر کردن کد استفاده می‌شود.
5. ایجاد چندین CTE پشت سر هم: می‌توان چندین CTE تعریف کرد و بعد با هم استفاده کرد.
6. فقط در یک استیتمنت معتبر است: بعد از اجرای همان SELECT/INSERT/UPDATE/DELETE از بین می‌رود.

---

📌 مزایا

✅ ساده کردن کوئری‌های پیچیده (مخصوصاً زمانی که چندین Subquery داریم).
✅ بهبود خوانایی کد.
✅ قابلیت بازگشتی برای سلسله‌مراتب‌ها.
✅ قابل استفاده در SELECT، INSERT، UPDATE، DELETE.

📌 معایب

❌ بهینه‌سازی خودکار مثل View ندارد (ممکن است از نظر Performance سنگین‌تر شود).
❌ قابل استفاده در چندین Batch یا Session نیست.
❌ برخلاف Temp Table یا Table Variable نمی‌توان از آن در چند کوئری مجزا استفاده کرد.

---

📌 مثال‌های کاربردی

🔹 مثال 1: ساده کردن Subquery

سناریو: می‌خواهیم لیست کارمندان با حقوق بالاتر از متوسط را پیدا کنیم.

بدون CTE:

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

با CTE:

WITH AvgSalary AS (
    SELECT AVG(Salary) AS AvgSal
    FROM Employees
)
SELECT e.Name, e.Salary
FROM Employees e
JOIN AvgSalary a ON e.Salary > a.AvgSal;

📍 تریس:

1. CTE یک جدول مجازی AvgSalary می‌سازد با یک ستون (میانگین حقوق).
2. در کوئری اصلی، روی Employees جوین می‌شود تا فقط کارمندانی که حقوقشان بیشتر از AvgSal است برگردد.

---

🔹 مثال 2: چندین CTE پشت سر هم

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

WITH DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSal
    FROM Employees
    GROUP BY DepartmentID
),
AboveAvg AS (
    SELECT e.Name, e.Salary, e.DepartmentID
    FROM Employees e
    JOIN DeptAvg d ON e.DepartmentID = d.DepartmentID
    WHERE e.Salary > d.AvgSal
)
SELECT * FROM AboveAvg;

📍 تریس:

1. CTE اول (DeptAvg) → حقوق متوسط هر دپارتمان.
2. CTE دوم (AboveAvg) → کارمندانی که حقوقشان بالاتر از متوسط است.
3. کوئری نهایی → خروجی AboveAvg را نمایش می‌دهد.

---

🔹 مثال 3: بازگشتی (Recursive CTE)

سناریو: یک جدول Employees داریم که هر کارمند یک ManagerID دارد. می‌خواهیم کل سلسله‌مراتب یک مدیر خاص را نمایش دهیم.

WITH EmployeeHierarchy AS (
    -- Anchor Member
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL   -- مدیر اصلی

    UNION ALL

    -- Recursive Member
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

📍 تریس:

1. بخش اول (Anchor) → مدیران اصلی (ManagerID = NULL).
2. بخش دوم (Recursive) → تمام کارمندانی که زیرمجموعه مدیر قبلی هستند، Level آن‌ها یکی اضافه می‌شود.
3. این روند ادامه دارد تا سلسله مراتب کامل نمایش داده شود.

---

🔹 مثال 4: استفاده در UPDATE

سناریو: افزایش حقوق 10% فقط برای کارمندانی که بالاتر از متوسط هستند.

WITH AboveAvg AS (
    SELECT EmployeeID
    FROM Employees
    WHERE Salary > (SELECT AVG(Salary) FROM Employees)
)
UPDATE Employees
SET Salary = Salary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM AboveAvg);

📍 تریس:

1. CTE AboveAvg → پیدا کردن EmployeeID کارمندان بالاتر از متوسط.
2. UPDATE → افزایش 10% حقوق آن‌ها.

---

🔹 مثال 5: استفاده در DELETE

سناریو: حذف کارمندانی که پایین‌ترین حقوق را دارند.

WITH MinSalary AS (
    SELECT MIN(Salary) AS MinSal
    FROM Employees
)
DELETE FROM Employees
WHERE Salary = (SELECT MinSal FROM MinSalary);

📍 تریس:

1. CTE MinSalary → حداقل حقوق محاسبه می‌شود.
2. DELETE → هر کارمندی با آن حقوق حذف می‌شود.

---

📌 جمع‌بندی

• CTE یک جدول موقت در همان Query است.
• جایگزین Subquery و Derived Table می‌شود.
• خوانایی کوئری را زیاد می‌کند.
• امکان بازگشتی دارد (مهم‌ترین ویژگی‌اش).
• برای SELECT، INSERT، UPDATE، DELETE کاربرد دارد.