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 ایجاد کرد که تنها شامل ستون‌های مورد نیاز است.

با استفاده از خوانایی مناسب ایجاد شده توسط 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 استفاده می کنیم. بعد از نوشتن خط اول دستور هم باید کوئری مورد نظر خود را درون پرانتزهای باز و بسته تعریف کنیم.

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

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

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

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

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

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

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

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

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

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

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

مثال اول برای روش پیاده سازی CTE

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

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

تصویر بالا، جدولی به نام employee  را برای نمایش جزئیات مربوط به کارمندان نشان می‌دهد. در فهرست پایین تمام ستون‌های مربوط به جدول کارمندان employee  را معرفی کرده‌ایم.

• emp_id : این ستون به شناسه یکتا یا ID مربوط به هر کارمند اشاره می‌کند.
• emp_name : داده‌های این ستون به نام هر کارمند اشاره دارد.
• email_id : داده‌های این ستون نمایانگر آدرس ‌ایمیل مربوط به هر کارمند در آن ردیف است.
• city : در این ستون نام شهرهایی نوشته شده‌اند که کارمندان در آن‌ها سکونت می‌کنند.
• Verification_status : اگر اطلاعات مربوط به هر کارمند تایید شده باشد، مقدار مربوط به این ستون با کلمه Yes  پر شده و اگر اطلاعات مربوط به کارمند نامعتبر باشد یا هنوز تایید نشده با کلمه No  پر می‌شود.

فرض کنید که می خواهیم اطلاعات خاصی را درباره کارمندان استخراج کنیم. پس برای رسیدن به این هدف باید از CTE-های SQL استفاده کنیم.

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

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

بر اثر اجرای کدهای بالا جدول موقت CTE ساخته شده و در خروجی نمایش داده می‌شود.

در تصویر بالا، mycte  نام جدول موقت CTE است که ساخته‌ایم. جدول mycte  شامل اطلاعات مربوط به ستون‌های emp_name و Verification_status است. این جدول شامل جزئیات مربوط به کارمندانی است که اطلاعاتشان کامل یا تایید اعتبار نشده است. یعنی همان داده‌هایی که در فرض مسئله به دنبال کسب آن بودیم.

برنامه نویسان پایگاه داده از روی جدول مرجع می‌توانند یک یا چند CTE مختلف ایجاد کنند. فقط کافی است که بین تعریف هر دو CTE علامت کاما «,» قرار بدهند.

دوری از خطا در پیاده سازی عبارت های جدول مشترک

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

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

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

مثال دوم برای روش پیاده سازی CTE

در این مثال روش استفاده از دستور JOIN در SQL را برای پیاده‌سازی CTE نمایش داده‌ایم. کدهای زیر با زبان SQL و در پایگاه داده MySQL نوشته شده‌اند.

خروجی حاصل از اجرای کدهای بالا به ازای جدول محصولات به نام product به شکل زیر است.

خروجی حاصل از اجرای کدهای بالا به ازای جدول فروش با نام sales هم به شکل زیر است.

در کدهای بالا دو جدول مجزای محصولات به نام product و فروش به نام sales را به صورت همزمان ایجاد کرده‌ایم. در فهرست زیر، توضیحات مربوط به ستون‌های جدول product بیان شده‌اند.

• p_id:‌ داده‌های این ستون به ID  یکتای محصول اشاره دارد.
• p_name: در این ستون نام مربوط به هر محصول یادداشت شده است.
• category:‌ در این ستون دسته‌بندی‌هایی را ذخیره‌ کرده‌ایم که محصول مربوطه به آن تعلق دارد.

در فهرست زیر هم توضیحات مربوط به ستون‌های جدول فروش sales را ارائه داده‌ایم.

• p_id:‌ داده‌های این ستون به ID  یکتای محصول اشاره دارد.
• p_name: در این ستون نام مربوط به هر محصول یادداشت شده است.
• gross_sales: در این ستون، قیمت فروش ناخالص هر محصول یادداشت شده است.

بعد از تعریف جدول‌های product و sales هم مقادیر دلخواهی را به آن جدول‌ها وارد کردیم.

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

همین طور که در کدهای بالا قابل مشاهده است، برای رسیدن به این هدف، عبارت جدول مشترکی با نام TEMP_CTE  را ایجاد کرده‌ایم. برای اتصال دو جدول product و sales به یکدیگر از دستور JOIN استفاده کردیم. می‌خواستیم که هر دو جدول بر اساس ردیف‌های سازگار بین آن‌ها به هم متصل شوند. بنابراین از دستور INNER JOIN در SQL استفاده کردیم. دستور INNER JOIN با نام JOIN نیز شناخته می‌‌شود. بنابراین اگر در کوئری خود به‌جای عبارت INNER JOIN فقط و به صورت ساده از دستور JOIN استفاده کنیم، باز هم کوئری نوشته شده معتبر است. ستون مشترک بین هر دو جدول، ستون p_id است.

برای پیدا کردن مجموع قیمت ناخالص محصولات و تعداد محصول موجود در هر دسته، باید به ترتیب از توابع تجمعی Sum و Count استفاده کنیم. برای استفاده از این توابع تجمعی هم لازم است که دستور GROUP BY در SQL وجود داشته باشد. در واقع با کمک کدهای بالا، ابتدا نتایج خود را بر اساس دسته محصولات گروه‌بندی کردیم. سپس مجموعه قیمت ناخالص هر دسته را همراه با تعداد محصول موجود در هر دسته محاسبه شده‌اند.

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

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

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

• افزایش وضوح کدها: شکستن کوئری‌های پیچیده به تکه‌های کوچکتر و قابل مدیریت با استفاده از CTE-ها می‌تواند به مقدار زیادی وضوح کدها را افزایش داده و در نتیجه خوانایی کدها را هم بهتر کند. در اثر این اتفاق، فرایند‌ نگهداری، اصلاح و تغییر کوئری‌ها در آینده برای خود شخص یا سایر توسعه‌دهندگان احتمالی به میزان زیادی ساده‌تر می‌شود.
• «ماژولار شدن» (Modularization): عبارت‌های جدول مشترک مانند مجموعه نتایج موقت نام‌گذاری شده‌ای عمل می‌کنند که قابل استفاده در کوئری اصلی هستند. این استراتژی قانونمند به توسعه‌دهندگان کمک می‌کند که بر روی تغییرات خاص در داده‌ها و محاسبات تعریف شده در هر CTE تمرکز کنند. در نتیجه درک منطق کلی برنامه بسیار ساده‌تر می‌شود.
• استفاده دوباره از کدها: «قابلیت استفاده دوباره» (Reusability) به این معنا است که چندین بار مختلف در کوئری یکسانی می‌‌توان به CTE مرجعه کرد. زیرا این CTE دارای نام مشخص است. این مسئله نیاز به تکرار زیر‌کوئری‌های پیچیده را کاهش می‌دهد. در نتیجه باعث کاهش افزونگی و افزایش کارآمدی در کدها می‌شود.
• «مدیریت داده سلسله‌مراتبی» (Hierarchical Data Management): CTE-های بازگشتی، نوع خاصی از CTE هستند که برای مدیریت داده‌ها به صورت سلسله‌مراتبی، مانند «درخت‌های خانواده» (Family Trees) یا نمودار‌های سازمانی به‌کار برده می‌شوند. این نوع از CTE-ها به طور خاص برای وظایفی مانند ردیابی صورت مواد تشکیل دهنده هر کالا یا ساختارهای مرتب شده براساس طبقه‌بندی، مفید هستند.

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

کلیک کنید

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

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

1. افزایش خوانایی کوئری‌ها: CTE-ها کوئری‌های پیچیده را به بخش‌های کوچکتر و با امکان مدیریت بیشتر، تقسیم می‌کنند. از طریق نام‌گذاری هر بخش و ایزوله‌سازی آن درون CTE توسعه دهندگان می‌توانند به سادگی هدف هر بخش از کوئری را تشخیص داده و در نتیجه منطق کلی کوئری بزرگ نوشته شده را درک کنند. در نتیجه خوانایی کدها به میزان زیادی افزایش پیدا می‌کند.
2. افزایش قابلیت نگهداری: استفاده از CTE در SQL باعث افزایش خوانایی و نگهداری ساده‌تر کدها می‌شود. وقت‌هایی که کوئری به روزرسانی شده یا تغییر داده می‌شود، توسعه دهندگان می‌توانند به سرعت CTE-های مربوطه را شناسایی کرده و بدون تاثیر گذاشتن روی باقی قسمت‌های کد تغییرات مد نظر خود را اعمال کنند. این ماژولار شدن کدها، عیب‌یابی کدها را ساده‌تر کرده و احتمال بروز خطا را به میزان زیادی کاهش می‌دهد.
3. کپسوله‌سازی منطق: CTE در SQL به توسعه دهندگان کمک می‌کند بخش‌های خاصی از منطق کوئری را به شکل ایزوله نگهداری کنند. با این کار، رویکرد ماژولاری را برای طراحی کوئری‌ها ترویج می‌کنند. این انتزاع باعث می‌شود که کوئری قابلیت نگهداری بیشتری داشته و امکان استفاده دوباره از کدها بیشتر شود. زیرا از CTE یکسانی را می‌توان بارها در همان کوئری استفاده کرد. در نتیجه نوشتن کدهای تکراری به میزان زیادی کاهش پیدا می‌کند.

آشنایی با سایر پایگاه های داده موجود

نرم‌افزارهای پایگاه داده متنوعی برای نگهداری، کار و مدیریت داده‌ها با استفاده از زبان SQL وجود دارند. به عنوان مثال می‌توان از برنامه‌هایی مانند MySQL و SQLite و PostgreSQL نام برد. برای کسب توانایی کار با این پایگاه‌های داده‌ی رابطه‌ای، منابع خوبی در فرادرس تولید و منتشر شده‌اند. انواع گوناگون این پایگاه‌های داده، هر کدام در ارتباط با زبان‌ برنامه‌نویسی خاص یا با توجه به نوع خاصی از پروژه‌ها یا سخت‌افزار در دسترس، کاربرد بهتری دارند. فرادرس، فیلم‌های آموزشی تخصصی متناسب با پایگاه‌های داده مختلف را از سطوح ابتدایی تا حرفه‌ای تهیه کرده است. در پایین پنج مورد از این فیلم‌های آموزشی را فهرست کرده‌ایم. برای دیدن فیلم‌ها آموزشی بیشتر، با کلیک بر روی تصویر زیر می‌توانید وارد صفحه مربوط به مجموعه آموزش پایگاه داده فرادرس شوید.

• فیلم آموزش پایگاه داده MySQL فرادرس
• فیلم آموزش مقدماتی اوراکل اپکس، تولید و توسعه نرم افزارهای تحت وب با Oracle APEX در فرادرس
• فیلم آموزش پایگاه داده پیشرفته، دوره جامع و کاربردی در فرادرس
• فیلم آموزش مقدماتی آپاچی کاساندرا Apache Cassandra در فرادرس
• فیلم آموزش مقدماتی PostgreSQL برای مدیریت پایگاه داده در فرادرس

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

درباره CTE-ها چند نکته مهم است که باید مورد توجه قرار دهیم. با رعایت این نکات و توجه به محل استفاده صحیح از CTE می‌توانیم کیفیت کوئری‌های خود را به میزان زیادی ارتقا داده و سرعت انجام کارها را نیز زیادتر کنیم.

• «نام‌گذاری توصیفی» (Descriptive Naming): به منظور تقویت وضوح کدهای نوشته شده، نام‌های با معنی برای CTE-های خود انتخاب کنید. در نتیجه درک روش کار کدهای خود CTE و نگهداری آن ساده‌تر می‌‌شود.
• «مختصر بودن» (Conciseness): سعی کنید که همیشه CTE نوشته شده فقط بر روی وظیفه‌ یا نقل و انتقال خاصی تمرکز کند. از افزودن پیچیدگی‌های غیرضروری دوری کنید. زیرا که این پیچیدگی‌ها می‌توانند هدف CTE را در‌ هاله ابهام فرو ببرند.
• CTE-های «بازگشتی» (Recursive) و «غیربازگشتی» (Non-Recursive): برای وظایف شامل استفاده از داده‌ها به صورت سلسله‌مراتبی یا عملیات پیمایشی از CTE-های بازگشتی استفاده کنید. اما از CTE-های غیربازگشتی برای ساده‌تر کردن کوئری‌های پیچیده‌ای استفاده کنید که شامل چنین ساختارهایی نیستند.
• «بهینه‌سازی عملکرد کوئری» (Optimize Query Performance): وقتی که چندین CTE مختلف را به صورت زنجیره‌وار به یکدیگر متصل می‌کنیم، برای بهینه‌سازی عملکرد کوئری باید ترتیب اجرای این CTE-ها را در نظر داشته باشیم. این کار باعث تضمین فراخوانی داده‌ها و اجرای پردازش کوئری به صورت کارآمدتر می‌‌شود.
• «خوانایی» (Readability): برای افزایش خوانایی کدها در کوئری‌های SQL اولویت قائل شوید. به منظور رسیدن به این هدف، باید به ساخت CTE-ها و عبارت‌های متصل به آن‌ها به شکلی شفاف و سازماندهی شده بپردازید. در این صورت فهم کدهای نوشته شده برای سایر توسعه‌دهندگان هم بسیار ساده‌تر می‌شود.

مقایسه CTE در مقابل سایر تکنیک های SQL

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

• «زیرکوئری‌ها» (Subqueries)
• «جدول‌های موقت» (Temp Tables)

بررسی مقایسه‌های توضیح داده شده را از گزینه اول یعنی زیرکوئری‌ها شروع می‌کنیم.

مقایسه CTE با زیرکوئری

CTE و زیرکوئری دارای تفاوت‌های جزئی هستند که در این بخش از مطلب به بیان آن‌ها پرداخته‌ایم. تفاوت‌های این ابزارهای کوئری‌نویسی را به صورت خلاصه در دو مورد مهم زیر می‌توان خلاصه کرد.

• نتایج عملکرد: CTE-ها و زیرکوئری‌ها اغلب ویژگی‌های عملکردی یکسانی را ارائه می‌دهند. البته استفاده از CTE-ها، بخصوص در کوئری‌های پیچیده، باعث ارتقا خوانایی و قابلیت کار بر روی داده‌ها می‌شود. در بعضی از موارد نیز ممکن است که بهینه‌ساز کوئری، برنامه‌های اجرایی بهتری را برای CTE-ها تولید کند و در نتیجه سرعت کار این تکنیک هم بیشتر می‌شود.
• موارد کاربرد به ازای هر رویکرد: از CTE در SQL Server برای ساده‌تر کردن کوئری‌های بزرگ استفاده کنید. به این صورت که کوئری‌های بزرگ را به تکه‌های کوچکتر و با خوانایی بیشتر تقسیم کنید. همچنین وقتی که به استفاده از زیرکوئری خاصی به صورت تکراری و چندباره در کوئری اصلی نیاز شود نیز می‌توان از CTE استفاده کرد. اما در جاهایی که استفاده از CTE-ها مورد نیاز نیست، مانند استفاده به صورت فقط یکبار، ساده یا «عملیات هم‌بسته» (Correlated Operations) از «زیرکوئری‌ها» (Subqueries) استفاده کنید.

مقایسه CTE با جدول موقت

با اینکه CTE، به‌خودی خود، نوعی جدول موقت محسوب می‌شود اما ساختار «جدول‌های موقت» (Temp tables) از CTE مستقل است. در این بخش از مطلب، CTE-ها را با جدول‌های موقت مقایسه کرده و به صورت دقیق‌تری شباهت‌ها و تفاوت‌های این دو ابزار کوئری‌نویسی در SQL را بیان کرده‌ایم.

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

جمع‌بندی

«عبارت‌ جدول مشترک» (Common Table Expression | CTE) در SQL ابزار قدرتمندی برای ساده‌ترسازی کوئری‌های پیچیده و تقویت خوانایی کدهاست. CTE-ها وظایف پیچیده را به تکه‌های کوچک‌تر و قابل مدیریتی تبدیل می‌کنند. این کار در نهایت باعث می‌شود توسعه دهندگان بیشترین بهره را از کوئری‌های پایگاه داده خود ببرند. CTE ابزاری چندکاره‌ با قابلیت استفاده برای حل کردن بیشتر چالش‌های SQL است. استفاده از CTE مزایای زیادی از قبیل ساده‌سازی تحلیل اطلاعات برای دانشمندان داده، دیباگ کردن ساده‌تر کوئری‌ها، استخراج موثرتر اطلاعات از پایگاه داده‌های بزرگ و غیره دارد.

فیلم مجموعه آموزش اس کیو ال سرور – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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