دستور With در SQL – از کاربرد تا نحوه استفاده به زبان ساده

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

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

دستور WITH در SQL چیست؟

عبارت دستوری WITH در SQL اولین بار، توسط اوراکل در Oracle 9i معرفی شده است. این دستور برای تعریف نتیجه یا جدول موقتی استفاده می‌شود که خروجی آن باید در کوئری اصلی به‌کار برده شود. کوئری اصلی با عبارت درون دستور WITH ترکیب می‌شود تا نتیجه نهایی بدست بیاید.

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

کلیک کنید

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

• توجه: دستور WITH در SQL توسط هر پایگاه‌ داده‌ای پشتیبانی نمی‌شود.

آموزش SQL Server از مبتدی تا پیشرفته

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

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

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

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

سینتکس دستور WITH در SQL

قبل از شروع به نمایش مثال‌ها و توضیحات عملی، لازم است که با سینتکس این دستور به صورت کامل آشنا شویم. به همین منظور، سینتکس خام دستور WITH در SQL را در کادر زیر نمایش داده‌ایم.

1--CTE
2WITH expression_name_1 (column_1, column_2,…,column_n)
3AS
4(CTE query definition 1),
5expression_name_2 (column_1, column_2,…,column_n)
6AS
7(CTE query definition 2)
8 
9--Final query using CTE
10SELECT expression_A, expression_B, ...
11FROM expression_name_2

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

در فهرست زیر پارامترهای کلیدی به‌کار رفته در کدهای بالا را توضیح داده‌ایم.

1. WITH :‌ این کلمه کلیدی برای ایجاد CTE-ها یا مجموعه داده‌های موقت به‌کار برده می‌شود.
2. expression_name (column_1, …, column_n) : با کمک این قطعه کد، نامی را برای مجموعه داده موقت ایجاد شده و ستون‌های آن تعریف می‌کنیم. از این مجموعه‌ داده موقت می‌توانیم در کوئری اصلی استفاده کنیم.
3. AS (….) : در این بخش کوئری را تعریف می‌کنیم که CTE را تعریف می‌کند. برای تعریف CTE-های تودرتو کوئری‌های نوشته شده در AS دوم اغلب اشاره به اولین CTE می‌کنند.
4. SELECT expression_A, expression_B FROM expression_name : این قطعه کد، کوئری اصلی را مشخص می‌کند. معمولا در کوئری اصلی با استفاده از عبارت SELECT در SQL - یا یکی از عبارت‌های INSERT یا UPDATE یا DELETE یا MERGE - بر روی یک یا بیشتر CTE-های تولید شده عملیاتی را اجرا می‌کنند. بر اثر اجرای این فرایند، نتیجه نهایی تولید می‌شود.

همه پارامترهای اشاره شده در بالا اجباری هستند. برنامه‌نویس ممکن است که با توجه به نیاز خود، هر کدام از عبارت‌های دستوری WHERE و GROUP BY و ORDER BY و HAVING در SQL را برای استفاده انتخاب کند.

مثال پیاده سازی این سینتکس

در مثال پایین، کدهای مربوط به سینتکس دستور WITH  را همراه با مثال ساده‌ای پیاده‌سازی کردیم. در ادامه مطلب، روال کاری این مثال را نیز توضیح داده‌ایم.

1WITH temporaryTable (averageValue)
2AS (
3    SELECT AVG (Attr1)
4    FROM Table
5)
6    SELECT Attr1
7    FROM Table, temporaryTable
8    WHERE Table.Attr1 > temporaryTable.averageValue;

در کوئری بالا، عبارت WITH  برای تعریف جدول موقتی به نام temporaryTable  به‌کار برده شده است. این جدول موقت فقط شامل یک ستون به نام averageValue است. ویژگی averageValue شامل مقدار میانگین داده‌‌های ستون Attr1 از جدول Table  است. عبارت SELECT  که همراه با عبارت دستوری WITH  آمده فقط آن داده‌هایی را تعریف می‌کند که مقدار آن‌ها درون ستون Attr1 از جدول Table  بزرگتر از میانگین مقدار بدست آمده از عبارت WITH  است.

توجه: در زمان اجرای کوئری شامل عبارت دستوری WITH، در ابتدا باید کوئری‌های درون این عبارت دستوری ارزیابی شوند. سپس نتیجه محاسبات انجام شده، درون جدول موقتی ذخیره می‌شود و بعد از آن کوئری اصلی با استفاده از داده‌های جدول موقت اجرا خواهد شد.

مثال هایی از کار با عبارت WITH

در این بخش از مطلب به بررسی روش کار عبارت WITH در SQL می‌پردازیم. برای بیان بهتر این مسئله از مثال‌های مختلفی استفاده کرده‌ایم.

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

کلیک کنید

مثال اول: شناسایی کارمندان با حقوق بالاتر از میانگین

در این مثال، جدولی به نام Employee  داده شده است که در آن «شماره‌ شناسایی» (EmployeeID)، «نام‌» (Name) و میزان «حقوق» (Salary) کارمندان مختلف ذخیره شده. هدف این است، کارمندانی را شناسایی کنیم که میزان حقوق دریافتی آن‌ها از میانگین حقوق کلی همه کارمندان بیشتر است.

اطلاعات مربوط به کارمندان را می‌توان در جدول SQL زیر به نام Employee  مشاهده کرد.

برای حل این مسئله از کوئری پیاده‌سازی شده در کادر پایین استفاده کردیم. برای نوشتن کوئری می‌توان مشاهده کرد که از دستور Where استفاده شده است. در SQL از دستور WHERE برای فیلتر کردن داده‌ها بر پایه‌های شرایط اعمال شده استفاده می‌کنیم. برای آشنایی کامل با این دستور می‌توانید مطلب دستور Where در SQL، از کاربرد تا نحوه استفاده به زبان ساده را از مجله فرادرس مطالعه کنید.

1WITH temporaryTable (averageValue) AS (
2    SELECT AVG(Salary)
3    FROM Employee
4)
5        SELECT EmployeeID,Name, Salary 
6        FROM Employee, temporaryTable 
7        WHERE Employee.Salary > temporaryTable.averageValue;

خروجی حاصل از اجرای کوئری بالا بر روی جدول Employee  به صورت زیر نمایش داده می‌شود.

با توجه به جدول Employee  میزان حقوق میانگین برابر با «۷۰۵۹۱» واحد پولی، محاسبه می‌شود. بنابراین، همه کارمندانی که حقوق دریافتی آن‌ها بیشتر از مقدار میانگین محاسبه شده است، در جدول خروجی به صورت بالا نمایش داده می‌شوند.

مثال دوم: هواپیماهایی با بالاترین حقوق برای خلبان ها

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

برای حل مسئله بالا، کوئری راه حل را به صورت زیر پیاده‌سازی کرده‌ایم.

1WITH totalSalary(Airplane, total) AS (
2    SELECT Airplane, SUM(Salary)
3    FROM Pilot
4    GROUP BY Airplane
5),
6    AirplaneAverage (avgSalary) AS (
7    SELECT avg(Salary)
8    FROM Pilot 
9)
10    SELECT Airplane
11    FROM totalSalary, AirplaneAverage
12    WHERE totalSalary.total > AirplaneAverage.avgSalary;

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

بعد انجام محاسبات لازم، قابل تشخیص است که مجموع حقوق همه خلبانان هواپیمای ایرباس ۳۸۰-آ برابر با «۲۹۸۸۳۰» واحد پولی است. در همین حال مجموع حقوق پرداختی به خلبانان هواپیمای بوئینگ برابر با «۴۵۰۰۰» واحد است. بنابراین، میانگین حقوق همه خلبانان در جدول Pilot برابر با «۵۷۳۰۵» واحد به‌دست می‌آید. از آنجا که مجموع حقوق همه خلبانان هواپیمای ایرباس ۳۸۰-آ بیشتر از میزان میانگین کل حقوق پرداختی به خلبان‌هاست، بنابراین هواپیمای ایرباس ۳۸۰-آ به عنوان جواب در خروجی، نمایش داده می‌شود.

کاربردها و نکات مهم دستور WITH در SQL

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

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

کلیک کنید

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

کاربردهای عبارت دستوری WITH در SQL

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

مزایا و کاربردهای کلیدی «عبارت‌های رایج جدول» ( Common Table Expressions | CTE) را می‌توان به صورت فهرست شده در زیر بیان کرد.

• افزایش خوانایی کدها: «برنامه‌نویسی ادیبانه» (Literate programming) توسط «دونالد نات» (Donald Knuth) معرفی شده است. در این روش بر روی سازماندهی کد به صورتی که از منطق انسانی پیروی کند، تمرکز می‌کنند. با این روش کدنویسی، خواندن کدها بسیار ساده و مانند خواندن داستان می‌شود. عبارت WITH در SQL با اجازه دادن به ساخت جدول‌های مجازی یا CTE-ها و شکستن وظایف بزرگ به تکه‌های کوچک‌تر از این ایده پشتیبانی می‌کند. در کوئری نوشته شده بعدا می‌توان تکه‌های کوچک کوئری را با یکدیگر ترکیب کرد. این کار را در عوض نوشتن همه چیز در کوئری بزرگ انجام می‌دهیم.
• افزایش قابلیت نگهداری کدها: خوانایی کدها ارتباط مستقیم با قابلیت نگهداری آن‌ها دارد. همین‌طور که کوئری‌ها و پایگاه‌های داده در طول زمان رشد می‌کنند، به طور متداولی نیاز به عیب‌یابی و رفع خطا نیز پیدا می‌شود. به‌همین ترتیب هم می‌دانیم که کدهای که خواناتر، نگهداری ساده‌تری هم دارند.
• جایگزین ویو: CTE-ها می‌توانند جای ویوها را بگیرند. زیرا از عملیات‌هایی مانند SELECT یا MERGE یا UPDATE یا DELETE یا INSERT در SQL پشتیبانی می‌کنند. این مسئله به طور خاص، زمانی مفید است که برای کار با بعضی از ساختارها مجوز مورد نظر را نداریم یا نیاز به راه حل موقتی برای کوئری مجزایی داریم که بدون ایجاد ویو دائمی مسئله‌ای را حل کند.
• غلبه بر محدودیت‌های عبارت‌ها: CTE-ها برای غلبه بر محدودیت‌های عبارت‌هایی مانند عبارت دستوری SELECT کمک می‌کنند. برای مثال در زمان پیاده‌سازی عبارت GROUP BY در SQL بدون استفاده از توابع قطعی می‌توان از WITH استفاده کرد.
• پردازش ساختارهای سلسله مراتبی: یکی از پیشرفته‌ترین کاربردهای CTE-ها استفاده در ساختارهای CTE به صورت بازگشتی است. کوئری‌های بازگشتی می‌توانند خود را فراخوانی کنند. این مسئله به پیمایش ساختارهای سلسله مراتبی پیچیده کمک می‌کند.

نکات مهم دستور WITH در SQL

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

• استفاده از دستور WITH زمانی خوب است که همراه با عبارات پیچیده SQL به‌کار برده شود. استفاده از این عبارت دستوری در ترکیب با کوئری‌های ساده چندان توصیه نمی‌شود.
• با کمک این عبارت دستوری می‌توانیم کوئری‌های پیچیده SQL را به کوئری‌های ساده‌تر و کوچکتر تجزیه کنیم. در نتیجه عیب‌یابی و پردازش کوئری‌های پیچیده با آسانی بیشتری ممکن می‌شود.
• عبارت دستوری WITH در SQL به صورت کلی به عنوان جایگزینی برای زیرکوئری‌های معمولی در نظر گرفته می‌شود.
• عبارت دستوری WITH در SQL می‌تواند به طرز چشم‌گیری کارایی کوئری‌ها را ارتقا دهد. دستور WITH این کار را با کمک به بهینه‌سازی کوئری برای استفاده مجدد از نتایج موقت بدست آمده انجام می‌دهد. یعنی در واقع از انجام ارزیابی‌های تکراری برای کوئری‌های یکسان جلوگیری می‌کند.

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

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

• فیلم آموزش پایگاه داده MySQL فرادرس
• فیلم آموزش اوراکل، دوره مدیریت بانک اطلاعاتی با Oracle سطح مقدماتی در فرادرس
• فیلم آموزش مقدماتی «آپاچی کاساندرا» (Apache Cassandra) با فرادرس
• فیلم آموزش رایگان پایگاه داده ClickHouse، مبانی و مقدمات در فرادرس
• فیلم آموزش مقدماتی PostgreSQL برای مدیریت پایگاه داده در فرادرس

جمع‌بندی

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

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

کلیک کنید

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