SQL CTE (Common Table Expression)

توضیح و مثال جامع برای SQL CTE (Common Table Expression)

مقدمه:

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

مزایای استفاده از CTE:

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

نحوه ی تعریف CTE:

یک CTE با استفاده از دستور WITH تعریف می‌شود.

ساختار کلی یک CTE به صورت زیر است:

WITH cte_name (column1, column2, ...)
AS
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

در این ساختار:

• cte_name: نامی است که به CTE اختصاص می‌دهید.
• column1, column2, ...: نام ستون‌های نتیجه CTE را مشخص می‌کند.
• SELECT ...: پرس و جویی که نتایج CTE را تولید می‌کند.

مثال:

فرض کنید می‌خواهید لیستی از نام و آدرس مشتریان را که بیش از ۱۰۰۰ دلار خرید کرده‌اند، بدست آورید.

WITH customer_orders AS (
    SELECT customers.name, customers.address, orders.amount
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
)
SELECT customer_orders.name, customer_orders.address
FROM customer_orders
WHERE customer_orders.amount > ۱۰۰۰;

در این مثال:

• customer_orders نام CTE است.
• name, address و amount نام ستون‌های CTE هستند.
• پرس و جوی داخلی SELECT اطلاعات مورد نیاز را از جداول customers و orders استخراج می‌کند.
• پرس و جوی خارجی فقط name و address مشتریانی را که بیش از ۱۰۰۰ دلار خرید کرده‌اند، نمایش می‌دهد.

انواع CTE:

دو نوع اصلی CTE وجود دارد:

• CTE تعریف شده: این نوع CTE فقط در پرس و جوی که در آن تعریف شده است قابل استفاده است.
• CTE موقت با قابلیت مشاهده: این نوع CTE در تمام پرس و جوهای در جلسه جاری قابل استفاده است.

برای تعریف یک CTE موقت با قابلیت مشاهده، از کلمه کلیدی MATERIALIZED استفاده کنید.

WITH MATERIALIZED customer_orders AS (
    ...
)
SELECT ...

نکات مهم:

• CTE ها فقط در سیستم‌های مدیریت پایگاه داده رابطه‌ای (RDBMS) که از استاندارد SQL پشتیبانی می‌کنند، قابل استفاده هستند.
• استفاده از CTE ها می‌تواند بر عملکرد پرس و جو تاثیر بگذارد. قبل از استفاده از CTE در پرس و جوهای تولید، باید آنها را به طور کامل آزمایش و بهینه سازی کنید.