Subquery و CTE در SQL Server | خانه برنامه‌نویسان البرز

فصل 14 از 24 درس 1 از 1

Subquery و CTE

T-SQL | Derived Table، EXISTS و Recursive CTE

Subquery و CTE Query پیچیده را به بخش‌های منطقی تقسیم می‌کنند. EXISTS برای بررسی وجود Match و CTE برای خوانایی، Queryهای چندمرحله‌ای و ساختارهای Recursive کاربرد دارد.

متن این فصل با رویکرد مرجع فنی و قابل استفاده در پروژه واقعی تنظیم شده است؛ مثال‌ها در امتداد مدل StoreDb نوشته شده‌اند و اصطلاحات اصلی SQL Server و T-SQL به‌صورت یکدست به‌کار می‌روند.

1. مفاهیم و قراردادهای اصلی

در طراحی پایگاه داده، Syntax تنها بخشی از مسئله است. نوع داده، Constraint، الگوی دسترسی، Transaction و هزینه اجرای Query باید هم‌زمان با نیاز Domain در نظر گرفته شوند.

مفهوم	کارکرد
Scalar Subquery	یک مقدار
Correlated Subquery	وابسته به Row بیرونی
EXISTS	بررسی وجود
Derived Table	Query داخل FROM
CTE	Result نام‌دار موقت
Recursive CTE	بازگشت سلسله‌مراتبی

2. ساختار و Syntax پایه

CTE درست پیش از Statement مصرف‌کننده تعریف می‌شود و Materialization آن تضمین‌شده نیست. انتخاب میان JOIN، EXISTS و Subquery بر اساس معنا و Plan انجام می‌شود.

WITH CustomerTotals AS
(
    SELECT
        CustomerId,
        COUNT(*) AS OrderCount,
        SUM(TotalAmount) AS TotalAmount
    FROM dbo.Orders
    GROUP BY CustomerId
)
SELECT c.Id, c.FirstName, ct.OrderCount, ct.TotalAmount
FROM dbo.Customers AS c
JOIN CustomerTotals AS ct ON ct.CustomerId = c.Id;

3. سناریوی StoreDb

برای Categoryهای Parent/Child، Recursive CTE سلسله‌مراتب را می‌پیماید. Max recursion و وجود Cycle باید در طراحی داده کنترل شود.

نمونه‌های این فصل بر مدل فروشگاهی StoreDb بنا شده‌اند تا ارتباط مفاهیم میان فصل‌ها حفظ شود و Queryها در یک Context یکپارچه قابل بررسی باشند.

WITH CategoryTree AS
(
    SELECT Id, Name, ParentCategoryId, 0 AS Depth
    FROM dbo.Categories
    WHERE ParentCategoryId IS NULL

    UNION ALL

    SELECT c.Id, c.Name, c.ParentCategoryId, t.Depth + 1
    FROM dbo.Categories AS c
    JOIN CategoryTree AS t ON t.Id = c.ParentCategoryId
)
SELECT * FROM CategoryTree;

4. اصول طراحی و Performance

طراحی پایدار باید هم صحت داده و هم هزینه اجرای عملیات را پوشش دهد. انتخاب ساده‌تر زمانی ترجیح دارد که Contract داده را روشن‌تر و رفتار Optimizer را قابل پیش‌بینی‌تر نگه دارد.

EXISTS برای سؤال «آیا وجود دارد؟» خوانا و مناسب است.
CTE برای نام‌گذاری مراحل Query استفاده شود، نه به‌عنوان Cache فرضی.
Recursive Data از Cycle محافظت شود.
Plan میان فرم‌های معادل اندازه‌گیری شود.

5. خطاهای رایج و نکات ایمنی

دستورهای SQL می‌توانند مستقیماً داده و ساختار را تغییر دهند؛ بنابراین بازبینی Context، تعداد Rowهای هدف و اثر Transaction قبل از اجرای تغییرات اهمیت عملی دارد.

Subquery Correlated پرهزینه بدون Index.
Recursive CTE با Cycle داده.
فرض Materialize شدن CTE.
Nesting عمیق Derived Table و کاهش خوانایی.

6. چک‌لیست نهایی فصل

نوع Subquery مناسب انتخاب شده است.
EXISTS برای وجود Match قابل استفاده است.
CTE Query پیچیده را روشن‌تر کرده است.
Recursive CTE محدودیت و Cycle را در نظر می‌گیرد.

7. جمع‌بندی

خروجی این فصل باید به تصمیمی قابل اتکا در طراحی یا Query منجر شود؛ صحت داده، قابلیت نگهداری و رفتار Performance سه معیار ثابت برای ارزیابی نمونه‌ها هستند.

محتوای درس Subquery و CTE