بهترین کاربردهای Subquery: از بهینه‌سازی تا مدیریت داده‌ها
Subquery، که اغلب به آن کوئری داخلی (Inner Query) یا کوئری تودرتو (Nested Query) نیز گفته می‌شود، یک دستور SELECT است که درون دستور SQL دیگری مانند SELECT، INSERT، UPDATE، DELETE یا حتی درون یک ساب‌کوئری دیگر قرار می‌گیرد. استفاده از ساب‌کوئری‌ها یک راه قدرتمند برای انجام عملیات پیچیده داده‌ای است و به حل مسائل مختلف پایگاه داده کمک می‌کند.
یک ساب‌کوئری می‌تواند برای فیلتر کردن داده‌ها، انجام محاسبات تجمعی، بررسی وجود داده‌ها و بسیاری موارد دیگر به کار رود. هدف اصلی آن بازگرداندن داده‌ها است که توسط کوئری بیرونی (Outer Query) استفاده می‌شوند.
انواع Subquery
ساب‌کوئری‌ها را می‌توان بر اساس تعداد ردیف‌هایی که بازمی‌گردانند و نحوه ارتباطشان با کوئری بیرونی به دسته‌های مختلفی تقسیم کرد:
Subquery تک‌سطری
این نوع Subquery دقیقاً یک ردیف و یک ستون را برمی‌گرداند. معمولاً با عملگرهای مقایسه‌ای مانند (=, >, =, <=, ) استفاده می‌شود.
Subquery چندسطری
این Subquery یک یا چند ردیف و یک ستون را برمی‌گرداند. با عملگرهای چندسطری مانند IN، NOT IN، ANY، ALL و EXISTS استفاده می‌شود.
Subquery همبسته (Correlated Subquery)
یک Subquery همبسته به کوئری بیرونی وابسته است و برای هر ردیف از کوئری بیرونی یک بار اجرا می‌شود. این نوع ساب‌کوئری از یک یا چند ستون از کوئری بیرونی برای پردازش خود استفاده می‌کند.
کاربردهای Subquery در SQL Server
Subquery در سناریوهای مختلفی برای مدیریت و بازیابی داده‌ها در SQL Server کاربرد دارند. در ادامه به بررسی مهم‌ترین کاربردهای ساب‌کوئری‌ها می‌پردازیم:
استفاده از Subquery در عبارت SELECT (ساب‌کوئری اسکالر)
Subquery در عبارت SELECT، که به آن ساب‌کوئری اسکالر (Scalar Subquery) نیز می‌گویند، باید همیشه یک مقدار (تک ردیف و تک ستون) را برگرداند. این روش برای نمایش یک مقدار تجمعی یا یک مقدار مرجع در کنار نتایج اصلی کوئری بسیار مفید است.
مثلاً، برای نمایش شناسه محصول، نام محصول، قیمت واحد و میانگین قیمت واحد تمام محصولات، می‌توانیم از یک ساب‌کوئری در عبارت SELECT استفاده کنیم:
SELECT
ProductID,
ProductName,
ListPrice,
(SELECT AVG(ListPrice) FROM Production.Product) AS AverageListPrice
FROM
Production.Product;
این کوئری، اطلاعات هر محصول را به همراه میانگین قیمت لیست همه محصولات نمایش می‌دهد. ساب‌کوئری میانگین کل را محاسبه کرده و برای هر ردیف از کوئری بیرونی بازگردانده می‌شود.
استفاده از Subquery در عبارت FROM (جدول مشتق شده)
هنگامی که یک Subquery در عبارت FROM استفاده می‌شود، نتیجه آن به عنوان یک جدول موقت شناخته شده به عنوان “جدول مشتق شده” (Derived Table) عمل می‌کند. این جدول مشتق شده سپس می‌تواند با سایر جداول جوین شود یا برای فیلتر کردن داده‌ها مورد استفاده قرار گیرد.
به عنوان مثال، برای یافتن محصولات با بالاترین قیمت در هر زیردسته، ابتدا می‌توانیم یک جدول مشتق شده ایجاد کنیم که حداکثر قیمت را برای هر زیردسته محاسبه کند:
SELECT
p.Name AS ProductName,
p.ListPrice,
ps.Name AS ProductSubcategoryName
FROM
Production.Product p
INNER JOIN
(SELECT ProductSubcategoryID, MAX(ListPrice) AS MaxSubcategoryPrice
FROM Production.Product
GROUP BY ProductSubcategoryID) AS SubcategoryMaxPrice
ON p.ProductSubcategoryID = SubcategoryMaxPrice.ProductSubcategoryID
AND p.ListPrice = SubcategoryMaxPrice.MaxSubcategoryPrice
INNER JOIN
Production.ProductSubcategory ps
ON p.ProductSubcategoryID = ps.ProductSubcategoryID;
این کوئری لیستی از محصولاتی را برمی‌گرداند که قیمت آن‌ها برابر با حداکثر قیمت در زیردسته خودشان است، به همراه نام زیردسته محصول. ساب‌کوئری در FROM، حداکثر قیمت را برای هر زیردسته محاسبه می‌کند و نتایج آن به عنوان یک جدول موقت عمل می‌کند.
استفاده از Subquery در عبارت WHERE
یکی از رایج‌ترین کاربردهای Subqueryها در عبارت WHERE است که برای فیلتر کردن ردیف‌ها بر اساس نتایج حاصل از یک کوئری دیگر استفاده می‌شود. این کار به افزایش دقت فیلترینگ و کاهش حجم داده‌های پردازش شده کمک می‌کند.
Subquery تک‌سطری با عملگرهای مقایسه‌ای
برای مقایسه یک ستون با یک مقدار واحد برگشتی از Subquery:
مثلاً، برای یافتن همه سفارشاتی که مبلغ کل آن‌ها بیشتر از میانگین مبلغ کل همه سفارشات است:
SELECT
SalesOrderID,
OrderDate,
TotalDue
FROM
Sales.SalesOrderHeader
WHERE
TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader);
این کوئری، همه سفارشاتی را نمایش می‌دهد که مبلغ کل آن‌ها از میانگین مبلغ کل سفارشات موجود در سیستم بیشتر است. Subquery داخلی میانگین کل مبلغ سفارشات را محاسبه می‌کند.
Subquery چندسطری با عملگر IN
برای انتخاب ردیف‌هایی که مقدار یک ستون در مجموعه‌ای از مقادیر برگشتی از Subquery وجود دارد:
مثلاً، برای یافتن همه محصولاتی که در سفارشات خاصی (با OrderQty بیشتر از 5) استفاده شده‌اند:
SELECT
ProductID,
Name
FROM
Production.Product
WHERE
ProductID IN (SELECT ProductID FROM Sales.SalesOrderDetail WHERE OrderQty > 5);
این کوئری، لیستی از محصولات را بازمی‌گرداند که حداقل در یک سفارش با تعداد بیشتر از 5 واحد حضور داشته‌اند. Subquery داخلی تمام شناسه‌های محصول را از جزئیات سفارش‌هایی که شرط را برآورده می‌کنند، جمع‌آوری می‌کند.
Subquery چندسطری با عملگرهای ANY / ALL
این عملگرها برای مقایسه یک ستون با هر (ANY) یا همه (ALL) مقادیر برگشتی از یک Subquery چندسطری استفاده می‌شوند.
-
ANY: اگر هر یک از مقادیر Subquery شرط را برآورده کند، درست است.
مثلاً، برای یافتن محصولاتی که قیمت لیست آن‌ها کمتر از حداقل قیمت هر زیردسته باشد (که در واقع به معنی کوچکترین قیمت در کل است، اما برای مثال ANY):
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice < ANY (SELECT ListPrice FROM Production.Product WHERE ProductSubcategoryID = 1);این کوئری محصولاتی را برمی‌گرداند که قیمت لیست آن‌ها کمتر از حداقل یک محصول در زیردسته با شناسه 1 باشد. در عمل، می‌توان از MIN نیز استفاده کرد، اما این مثال کاربرد ANY را نشان می‌دهد.
-
ALL: اگر همه مقادیر Subquery شرط را برآورده کنند، درست است.
مثلاً، برای یافتن محصولاتی که قیمت لیست آن‌ها بیشتر از حداکثر قیمت در هر زیردسته باشد (که در واقع به معنی بزرگترین قیمت در کل است، اما برای مثال ALL):
SELECT Name, ListPrice FROM Production.Product WHERE ListPrice > ALL (SELECT ListPrice FROM Production.Product WHERE ProductSubcategoryID = 1);این کوئری محصولاتی را برمی‌گرداند که قیمت لیست آن‌ها بیشتر از همه محصولات در زیردسته با شناسه 1 باشد. این یعنی قیمت آن از گران‌ترین محصول آن زیردسته نیز بیشتر است.
Subquery همبسته با عملگر EXISTS
عملگر EXISTS برای بررسی وجود ردیف‌ها در نتیجه یک Subquery استفاده می‌شود. این عملگر به ویژه برای ساب‌کوئری‌های همبسته که به کوئری بیرونی وابسته هستند، کارآمد است.
مثلاً، برای یافتن مشتریانی که حداقل یک سفارش در سال 2014 داشته‌اند:
SELECT
c.CustomerID,
p.FirstName,
p.LastName
FROM
Sales.Customer c
INNER JOIN
Person.Person p ON c.PersonID = p.BusinessEntityID
WHERE EXISTS (
SELECT 1
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID = c.CustomerID
AND YEAR(soh.OrderDate) = 2014
);
این کوئری، لیستی از مشتریانی را برمی‌گرداند که حداقل یک سفارش در سال 2014 ثبت کرده‌اند. ساب‌کوئری EXISTS برای هر مشتری بررسی می‌کند که آیا سفارشی با شرایط مشخص شده وجود دارد یا خیر.
استفاده از Subquery در دستور INSERT
Subqueryها می‌توانند برای درج داده‌ها از یک کوئری SELECT به یک جدول دیگر استفاده شوند. این روش به جای درج مقادیر ثابت، نتایج یک کوئری را درج می‌کند.
مثلاً، برای درج اطلاعات سفارشات قدیمی به یک جدول آرشیو (با فرض وجود جدول `Sales.SalesOrderHeaderArchive`):
INSERT INTO Sales.SalesOrderHeaderArchive (
SalesOrderID,
RevisionNumber,
OrderDate,
DueDate,
ShipDate,
Status,
OnlineOrderFlag,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
SalesPersonID,
TerritoryID,
BillToAddressID,
ShipToAddressID,
ShipMethodID,
CreditCardID,
CreditCardApprovalCode,
CurrencyRateID,
SubTotal,
TaxAmt,
Freight,
TotalDue,
Comment,
rowguid,
ModifiedDate
)
SELECT
SalesOrderID,
RevisionNumber,
OrderDate,
DueDate,
ShipDate,
Status,
OnlineOrderFlag,
SalesOrderNumber,
PurchaseOrderNumber,
AccountNumber,
CustomerID,
SalesPersonID,
TerritoryID,
BillToAddressID,
ShipToAddressID,
ShipMethodID,
CreditCardID,
CreditCardApprovalCode,
CurrencyRateID,
SubTotal,
TaxAmt,
Freight,
TotalDue,
Comment,
rowguid,
ModifiedDate
FROM
Sales.SalesOrderHeader
WHERE
OrderDate < '2014-01-01';
این دستور، تمام سفارشات ثبت شده قبل از تاریخ 1 ژانویه 2014 را از جدول اصلی `Sales.SalesOrderHeader` به جدول `Sales.SalesOrderHeaderArchive` منتقل می‌کند. ساب‌کوئری `SELECT` داده‌هایی که باید درج شوند را انتخاب می‌کند.
استفاده از Subquery در دستور UPDATE
Subqueryها می‌توانند برای به روزرسانی ستون‌های یک جدول با استفاده از مقادیر برگشتی از یک کوئری دیگر استفاده شوند. این بسیار مفید است وقتی که نیاز به به روزرسانی یک ستون بر اساس داده‌های مرتبط در جداول دیگر دارید.
مثلاً، برای به روزرسانی فیلد `Comment` برای سفارشاتی که مبلغ کل آن‌ها بیشتر از یک مقدار خاص است (مثلاً 5000):
UPDATE
Sales.SalesOrderHeader
SET
Comment = 'High value order'
WHERE
SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > 5000);
این کوئری، ستون `Comment` را برای تمام سفارشاتی که مبلغ کل (TotalDue) آن‌ها بیشتر از 5000 است، به ‘High value order’ تغییر می‌دهد. ساب‌کوئری `IN` شناسه‌های سفارشات واجد شرایط را شناسایی می‌کند.
استفاده از Subquery در دستور DELETE
Subqueryها می‌توانند برای حذف ردیف‌ها از یک جدول بر اساس نتایج یک کوئری دیگر استفاده شوند. این کار به حذف دقیق داده‌ها بر اساس شرایط پیچیده‌تر کمک می‌کند.
مثلاً، برای حذف سفارشات باستانی که در جدول آرشیو نیز وجود دارند (با فرض اینکه قبلاً منتقل شده‌اند):
DELETE FROM
Sales.SalesOrderHeader
WHERE
SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderHeaderArchive);
این دستور، تمام سفارشاتی را از جدول `Sales.SalesOrderHeader` حذف می‌کند که شناسه‌های آن‌ها در جدول `Sales.SalesOrderHeaderArchive` موجود هستند. این کار پس از آرشیو کردن داده‌ها، به پاکسازی جدول اصلی کمک می‌کند. ساب‌کوئری `IN` شناسه‌های سفارشات آرشیو شده را بازمی‌گرداند.
