CTE در SQL چیست؟ – توضیح به زبان ساده

در «زبان جست‌وجوی ساختاریافته» (Structured Query Language | SQL) آموزش استفاده از «عبارت‌های جدول مشترک» (Common Table Expressions |‌ CTEs) مانند بدست آوردن ابزار قدرتمندی برای کار با داده‌ها است. CTE در SQL باعث کارآمدتر شدن کدهای کوئری‌ها می‌شود. با کمک CTE-ها مدیریت کوئری‌های پیچیده ساده‌تر شده و خوانایی کدها نیز بهتر می‌شود. عبارت جدول مشترک در SQL یا همان CTE، «جدول موقتی» (Temporary Table) است که به برنامه نویسان برای کار بر روی داده‌های جدول اصلی به شکل ساده‌تر و با سرعت بیشتر کمک می‌کند. جدول‌های موقت CTE به‌دلیل تمرکز صِرف بر روی ستون‌ها و عملیات مورد نیاز به توسعه‌دهندگان کمک می‌کنند که کدهای تمیز‌تری بنویسند. در نتیجه کارآمدی و خوانایی کدها به میزان زیادی افزایش پیدا می‌کند.

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

CTE در SQL چیست؟

کلمه CTE از سرنام کلمات اصطلاح «Common Table Expression» به معنی «عبارت جدول مشترک» ایجاد شده است. CTE ابزار قدرتمندی است که به سازماندهی و پاکسازی داده‌ها کمک می‌کند. این تکنیک برای ساده‌تر کردن کدها و افزایش سرعت نتیجه‌گیری از کوئری‌های نوشته شده مورد استفاده قرار می‌گیرد. همچنین جدول موقت CTE برای اشاره به جدول اصلی به‌کار می‌رود. فرض کنیم جدول اصلی دارای تعداد بسیار زیادی ستون است. از طرفی هم مدیر پایگاه داده فقط به تعداد کمی از این ستون‌ها نیاز دارد. از روی جدول اصلی، به سادگی می‌توان جدول موقت CTE ایجاد کرد که تنها شامل ستون‌های مورد نیاز است.

فیلم آموزش پایگاه داده MySQL مای اس کیو ال در فرادرس

کلیک کنید

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

انواع CTE در SQL

در زمان استفاده از CTE-ها دو رویکرد کلی برای پیاده‌سازی آن‌ها وجود دارد که در فهرست زیر معرفی کرده‌ایم.

1. CTE-های غیربازگشتی
2. CTE-های بازگشتی

هر کدام از این رویکردها کاربرد و مزیت خاص خود را دارند که قبل از پیاده‌سازی باید به آن‌ها توجه کرد. به همین دلیل تصمیم گرفتیم که در این بخش، هر کدام از رویکردهای مختلف در زمان پیاده‌سازی CTE را معرفی کرده و به‌ اندازه کافی توضیح دهیم.

CTE-های غیربازگشتی

CTE-های غیربازگشتی، کوئری‌های پیچیده را ساده‌تر می‌کنند و خوانایی کدها را ارتقا می‌دهند. این نوع از کوئری‌ها به عنوان مجموعه نتایج موقت، کاربرد دارند. یعنی به توسعه دهندگان کمک می‌کنند کوئری اصلی و پیچیده را به تکه‌های مختلفی تقسیم کرده و این تکه‌ها را به صورت منظم در کنار یکدیگر سازماندهی کنند. تکه‌های سازماندهی شده‌ای که بعدا در کوئری‌های همسان قابل مراجعه‌اند.

مثال کاربردی از CTE-های غیربازگشتی

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

• جمع آوری داده‌های فروش از چندین جدول مختلف، باعث می‌شود که محاسبه اعداد مهم مربوط به بهره‌وری مانند KPI و سایر معیارهای اندازه‌گیری ساده‌تر انجام شود.
• فیلترکردن و انتقال اطلاعات قبل از اتصال جدول به سایر جدول‌ها باعث ارتقا کارایی کوئری و افزایش خوانایی کوئری‌ها می‌شود.

CTE-های بازگشتی

«عبارت‌ مشترک جدول» بازگشتی باعث فعال شدن ساختارهای پیمایش‌پذیر یا سلسله‌مراتبی در کوئری‌نویسی می‌شود. ساختار‌های مانند رابطه والد و فرزندی، ساختمان داده درخت‌ با قابلیت پیمایش‌پذیری یا پیدا کردن کوتاه‌ترین مسیر در گراف با کمک این رویکرد قابل پیاده‌سازی هستند. این ساختارها از بخش اولیه و «غیربازگشتی» (Non-Recursive) به نام «عضو لنگر» (Anchor Member) و بخش بازگشتی به نام «عضو بازگشتی» (Recursive Member) تشکلی شده‌اند که به CTE در SQL اشاره می‌کنند.

مثال کاربردی از CTE-های بازگشتی

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

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

فراگیری پایگاه داده SQL Server در فرادرس

SQL Server یکی از بهترین و محبوب‌ترین نر‌م‌فزارهایی است که می‌تواند برای ساخت، نگهداری و مدیریت بانک‌های اطلاعاتی به کاربران خود کمک کُند. این پایگاه داده، توسط شرکت مایکروسافت طراحی و تولید شده است و قابلیت ذخیره، مدیریت و بازیابی داده‌ها را در پایگاه داده رابطه‌ای (RDBMS) فراهم می‌کند. در دنیای مدرن، تقریبا تمام سازمان‌ها حداقل یک سیستم مدیریت بانک اطلاعاتی دارند. بنابراین، داشتن دانش مرتبط با بانک‌های اطلاعاتی برای افراد علاقه‌مند به فعالیت در حوزه تولید نرم‌افزار یا مدیریت اطلاعات، بسیار ضروری است. آموزش کار با SQL Server یکی از همین‌ دوره‌ها است که افراد را برای ورود به بازار کار آماده می‌کند.

وب‌سایت آموزشی فرادرس، یکی از بهترین موسسات آموزشی است که برای آموزش کار با پایگاه داده SQL Server فیلم‌های بسیار مناسبی را تولید کرده. در سطوح مختلف علمی - از مبتدی گرفته تا حرفه‌ای - فرادرس، فیلم‌های متنوعی را برای آموزش SQL Server طراحی و منتشر کرده است. به طور کل، از مزایای فیلم‌های آموزشی فرادرس، می‌توان به مواردی مانند کیفیت بالای علمی و ویدئویی دوره‌ها، هزینه مقرون‌به‌صرفه و عدم وجود محدودیت زمانی برای یادگیری توسط این فیلم‌ها اشاره کرد.

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

• فیلم آموزش کوئری‌ نویسی پیشرفته در SQL Server با فرادرس
• فیلم آموزش SQL Server دوره تکمیلی فرادرس
• فیلم آموزش رایگان پروژه محور SQL Server درباره ایجاد ویو سفارش فروشگاهی در فرادرس
• فیلم آموزش کوئری‌ نویسی پیشرفته در SQL Server فرادرس
• فیلم آموزش پروژه محور اس کیو ال سرور درباره ساخت دیکشنری برای دیتابیس با فرادرس

ایجاد و استفاده از CTE در SQL

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

فیلم آموزش کوئری‌ نویسی پیشرفته در SQL Server در فرادرس

کلیک کنید

در این بخش مطلب می آموزیم که چگونه CTE ایجاد کرده و از CTE-های تولید شده چطور استفاده کنیم.

سینتکس CTE در SQL

برای تعریف کردن CTE باید از کلمه کلیدی WITH در SQL استفاده کرده و بعد از آن، نام CTE مورد نظر را بنویسیم. سپس می‌توانیم لیستی از ستون‌ها را به صورت اختیاری بنویسیم. در انتها هم از کلمه کلیدی AS  برای شروع تعریف CTE استفاده می کنیم. بعد از نوشتن خط اول دستور هم باید کوئری مورد نظر خود را درون پرانتزهای باز و بسته تعریف کنیم.

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

1WITH CTE_NAME AS
2( 
3 SELECT column_name1, column_name2,..., column_nameN
4 FROM table_name
5 WHERE condition
6)
7SELECT column_name1, column_name2,..., column_nameN
8FROM CTE_NAME;

کوئری نویسی با استفاده از CTE

در این بخش برای انتخاب و دست‌کاری داده‌ها در جدول‌های SQL از عبارت‌های جدول مشترک استفاده می کنیم.

انتخاب داده از درون CTE-ها

بعد از اینکه CTE را تعریف کردیم، با استفاده از عبارت SELECT در SQL می‌توان از درون آن داده‌های مورد نظر خود را انتخاب و استخراج کنیم.

1SELECT column1, column2
2
3FROM CTE_name
4
5WHERE ...

حذف اضافه و به روزرسانی داده ها با استفاده از CTE

با استفاده از CTE-ها می‌توانیم داده‌های درون جدول‌های پایه را تغییر دهیم. در کادر زیر روش وارد کردن، به‌روزرسانی و حذف داده‌های پایگاه داده را با مراجعه به CTE نمایش داده‌ایم. در کدهای زیر به ترتیب دستور‌های Insert  و Update  و Delete در SQL را پیاده‌سازی کرده‌ایم.

1-- Insert
2
3INSERT INTO target_table
4
5SELECT ... FROM CTE_name ...
6
7-- Update
8
9UPDATE target_table
10
11SET ...
12
13FROM CTE_name
14
15WHERE ...
16
17-- Delete
18
19DELETE target_table
20
21FROM target_table
22
23JOIN CTE_name ON …

مثال‌ های بیشتر برای تعریف و استفاده از CTE

در این بخش از مطلب، درباره تعریف و استفاده از CTE دو مثال فرضی را پیاده‌سازی کرده‌ایم. برای هر کدام از مثال‌ها در ابتدا جدول‌های شامل داده‌ای‌ ایجاد کرده سپس سناریو مورد نظر را اجرا می‌‌کنیم.

فیلم آموزش پایگاه داده MySQL مای اس کیو ال در فرادرس

کلیک کنید

سناریو ساده و کاربردی CTE