CTE یا Common Table Expression در SQL: آموزش کامل با مثال‌های کاربردی

CTE یا Common Table Expression در SQL: آموزش کامل با مثال‌های کاربردی


در دنیای پایگاه داده، یکی از پرکاربردترین مفاهیم برای نوشتن کوئری‌های تمیز، خوانا و قابل نگهداری، استفاده از CTE در SQL یا همان Common Table Expression است. بسیاری از برنامه‌نویسان SQL در پروژه‌های پیچیده با کوئری‌هایی روبه‌رو می‌شوند که شامل چندین زیرکوئری تو در تو هستند. در چنین شرایطی، استفاده از CTE یا Common Table Expression در SQL می‌تواند ساختار کوئری را ساده‌تر، واضح‌تر و کارآمدتر کند. در این مقاله، به‌صورت گام‌به‌گام مفهوم CTE را توضیح می‌دهیم، تفاوت آن با Subquery و View را بررسی می‌کنیم و با مثال‌های واقعی نحوه استفاده از آن را آموزش می‌دهیم.

CTE یا Common Table Expression در SQL چیست؟

CTE مخفف Common Table Expression است و در واقع یک جدول موقت است که می‌توان آن را درون یک دستور SQL تعریف کرد. این جدول موقت فقط در همان دستور قابل استفاده است و بعد از اجرای کوئری از بین می‌رود.
ساختار کلی CTE در SQL به‌صورت زیر است:

WITH CTE_Name AS (
SELECT column1, column2
FROM TableName
WHERE condition
)
SELECT * FROM CTE_Name;

در این ساختار، بخش WITH برای تعریف CTE استفاده می‌شود و در ادامه می‌توان آن را مانند یک جدول معمولی در کوئری اصلی به کار برد. استفاده از CTE یا Common Table Expression در SQL باعث می‌شود بتوان داده‌ها را در چند مرحله پردازش کرد و منطق کوئری را به بخش‌های مجزا تقسیم نمود.

CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی
CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی

مزایای استفاده از CTE در SQL

کاربرد CTE یا Common Table Expression در SQL مزایای متعددی دارد که باعث محبوبیت آن در میان توسعه‌دهندگان پایگاه داده شده است:

  • افزایش خوانایی کوئری‌ها: وقتی کوئری‌ها پیچیده می‌شوند، CTE کمک می‌کند بخش‌های مختلف آن تفکیک و منظم شوند.

  • استفاده چندباره از نتیجه موقت: شما می‌توانید از یک CTE در چند بخش از کوئری استفاده کنید بدون نیاز به تکرار کد.

  • جایگزین مناسب Subquery: به‌جای نوشتن زیرکوئری‌های تو در تو، می‌توان از CTE برای شفاف‌تر کردن منطق استفاده کرد.

  • پشتیبانی از بازگشتی‌ها (Recursive Queries): یکی از ویژگی‌های خاص CTE در SQL، امکان ایجاد کوئری‌های بازگشتی برای کار با داده‌های سلسله‌مراتبی است.

CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی
CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی

تفاوت CTE در SQL با Subquery و View

برای درک بهتر کاربرد CTE در SQL، لازم است تفاوت آن با Subquery و View را بدانیم.

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

  • در مقایسه با View: CTE موقتی است و بعد از اجرای کوئری از بین می‌رود، اما View در دیتابیس ذخیره می‌شود و قابل استفاده در آینده است.
    بنابراین اگر فقط برای یک کوئری خاص به یک مجموعه داده موقت نیاز دارید، CTE در SQL بهترین گزینه است.

مثال ساده از CTE در SQL

فرض کنید جدولی به نام Employees داریم که شامل ستون‌های EmployeeID، Name و Salary است. حالا می‌خواهیم فقط کارمندانی را ببینیم که حقوقشان بالاتر از میانگین است. بدون استفاده از CTE در SQL ممکن است کوئری این‌طور نوشته شود:

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

اما با CTE در SQL می‌توان این کوئری را به شکل زیر ساده و تمیز نوشت:

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

در این مثال، ابتدا با استفاده از CTE میانگین حقوق را در یک جدول موقت به نام AvgSalary محاسبه کردیم و سپس از آن در کوئری اصلی بهره بردیم.

مثال پیشرفته از CTE در SQL: محاسبه جمع فروش هر کارمند

جدول فرضی Sales را در نظر بگیرید که شامل ستون‌های EmployeeID، SaleAmount و SaleDate است. هدف ما محاسبه مجموع فروش هر کارمند در یک بازه زمانی خاص است.
کد زیر با استفاده از CTE در SQL این کار را انجام می‌دهد:

WITH EmployeeSales AS (
SELECT EmployeeID, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE SaleDate BETWEEN ‘2025-01-01’ AND ‘2025-12-31’
GROUP BY EmployeeID
)
SELECT e.EmployeeID, e.Name, s.TotalSales
FROM Employees e
JOIN EmployeeSales s ON e.EmployeeID = s.EmployeeID
ORDER BY s.TotalSales DESC;

در اینجا، ابتدا با CTE مجموع فروش هر کارمند را محاسبه کرده‌ایم و سپس در کوئری اصلی با جدول Employees ترکیب کرده‌ایم تا اطلاعات کامل نمایش داده شود. این مثال نشان می‌دهد که چگونه CTE در SQL می‌تواند منطق پردازش داده را در چند مرحله سازمان‌دهی کند.

CTE بازگشتی (Recursive CTE) در SQL

یکی از قدرتمندترین ویژگی‌های CTE در SQL، قابلیت بازگشتی است.
به کمک این قابلیت، می‌توان ساختارهای سلسله‌مراتبی مانند درخت سازمانی، دسته‌بندی محصولات یا ساختار فایل‌ها را نمایش داد.
فرض کنید جدولی به نام Departments داریم با ستون‌های DeptID، DeptName و ParentDeptID. برای نمایش سلسله‌مراتب بخش‌ها می‌توان از کوئری زیر استفاده کرد:

WITH RecursiveCTE AS (
SELECT DeptID, DeptName, ParentDeptID, 0 AS Level
FROM Departments
WHERE ParentDeptID IS NULL
UNION ALL
SELECT d.DeptID, d.DeptName, d.ParentDeptID, r.Level + 1
FROM Departments d
INNER JOIN RecursiveCTE r ON d.ParentDeptID = r.DeptID
)
SELECT * FROM RecursiveCTE;

در این مثال، CTE در SQL به‌صورت بازگشتی عمل می‌کند و با هر بار اجرای بخش UNION ALL سطوح جدید سلسله‌مراتب را اضافه می‌کند.

محدودیت‌ها و نکات مهم CTE یا Common Table Expression در SQL 

با وجود مزایای زیاد، CTE در SQL محدودیت‌هایی نیز دارد:

  • فقط در همان دستور قابل استفاده است و قابل ذخیره در دیتابیس نیست.

  • در برخی نسخه‌های SQL Server یا MySQL ممکن است محدودیت عمق بازگشت داشته باشد.

  • در کوئری‌های خیلی بزرگ ممکن است عملکرد نسبت به View کمی کندتر باشد.
    با این حال، در بیشتر موارد استفاده از CTE یا Common Table Expression در SQL  باعث تمیزتر شدن و بهینه‌تر شدن کوئری می‌شود.

CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی
CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی

بهترین زمان برای استفاده از CTE در SQL

بر اساس تجربه متخصصان پایگاه داده، موارد زیر بهترین زمان برای استفاده از CTE یا Common Table Expression در SQL  است:

  • زمانی که کوئری پیچیده است و نیاز به تفکیک منطقی دارد.

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

  • زمانی که ساختار داده به‌صورت درختی یا سلسله‌مراتبی است.
    در تمام این موارد، CTE در SQL به شما کمک می‌کند تا کوئری‌های قابل‌فهم‌تر و تمیزتری بنویسید.

CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی
CTE یا Common Table Expression در SQL آموزش کامل با مثال‌های کاربردی

جمع‌بندی

در این مقاله یاد گرفتیم که CTE در SQL یا Common Table Expression یکی از ابزارهای کلیدی برای ساده‌سازی کوئری‌ها و افزایش خوانایی کدها است. با استفاده از CTE می‌توان داده‌ها را مرحله‌به‌مرحله پردازش کرد، کوئری‌های بازگشتی نوشت و از تکرار کد جلوگیری نمود. چه در پروژه‌های کوچک و چه در سیستم‌های داده‌ای پیچیده، استفاده از CTE یا Common Table Expression در SQL باعث می‌شود ساختار کوئری شما حرفه‌ای‌تر و بهینه‌تر باشد.

بازدید از مقاله: 10

دپارتمان آموزش کیسان

sqlعلم دادههوش تجاری

لینکدینواتس اپتلگرامایمیلچاپلینک کوتاه

بدون دیدگاه

دیدگاهتان را بنویسید