آشنایی با مفهوم Subquery

گاهی اوقات ما کوئری هایی می نویسیم که دارای بیش از یک Select و به صورت تو در تو می باشند که اصطلاحا به آن ها Nested Query می گویند. به درونی ترین کوئری، Inner Query یا Subquery و به بیرونی ترین کوئری Outer Query گفته می شود.

انواع Subquery از لحاظ ماهیت به دو دسته

1. مستقل (Self-Contained)
2. وابسته (Correlated)

و بر اساس نتایج Subqueryها به سه دسته

1. تک مقدار (Single-Valued/Scalar-Valued)
2. چند مقدار (Multi-Valued)
3. و Table-Valued

تقسیم بندی می شوند.

مستقل (Self-Contained)

وابستگی و ارتباطی به کوئری بیرونی ندارد. قبل از کوئری بیرونی اجرا می شود و نتیجه را در اختیار کوئری بیرونی قرار می دهد. از ویژگی های آن اشکال زدایی ساده می باشد که در ادامه خواهیم دید.

در Self-Contained تک مقدار، همانطور که از نام آن مشخص است دارای خروجی تک مقداری می باشد و در بخش Select و Where کوئری بیرونی می تواند مورد استفاده قرار بگیرد.
در این کوئری، جدیدترین سفارش ثبت شده در جدول Orders را با استفاده از Subquery مستقل نمایش خواهیم داد.

SELECT
	EmployeeID, CustomerID, OrderID
FROM dbo.Orders
	WHERE OrderID = (SELECT MAX(OrderID) FROM dbo.Orders);
GO

همانطور که ملاحظه می کنید، مقدار Where را برابر با یک Subquery قرار دادیم که بصورت مستقل عمل می کند و بزرگتری Id از جدول Orders را بدست می آورد. اگر خود Subquery را جداگانه اجرا کنیم به ما مقدار خواهد داد در نتیجه اشکال زدایی آن ساده می باشد.

در یک کوئری دیگر تعداد سفارش هر مشتری به‌ همراه تعداد کل سفارش ثبت‌ شده با استفاده از Subquery مستقل را نمایش خواهیم داد.

SELECT
	CustomerID,
	COUNT(OrderID) AS Num,
	(SELECT COUNT(OrderID) FROM dbo.Orders) AS Total
FROM dbo.Orders
GROUP BY CustomerID;
GO

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

در ادامه یک کوئری می نویسیم که دارای Subquery مستقل از نوع Multi-Valued خواهد بود. در اینجا از گزاره ای به نام IN استفاده خواهیم کرد. در خروجی چند مقدار، خروجی ما دارای چند مقدار و در قالب یک ستون می باشد.

در این کوئری تمام سفارش هایی را نمایش می دهیم که توسط کارمندانی ثبت شده اند که نام‌ خانوادگی آن‌ ها با کاراکتر 'ت' شروع شده باشد.

SELECT
	EmployeeID, OrderID
FROM dbo.Orders
	WHERE EmployeeID IN (SELECT E.EmployeeID FROM dbo.Employees AS E 
							WHERE E.lastname LIKE N'ت%');
GO

وابسته (Correlated)

کوئری درونی به کوئری بیرونی وابستگی دارد.

در این کوئری، جدیدترین سفارش هر مشتری را با استفاده از Correlated Subquery و در بخش Select، نمایش می دهیم.

SELECT
	DISTINCT O1.CustomerID,
	(SELECT MAX(OrderID) FROM dbo.Orders AS O2
		WHERE O2.CustomerID = O1.CustomerID) AS NewOrder
FROM dbo.Orders AS O1;
GO

بین کوئری درونی و کوئری بیرونی یک ارتباط برقرار کردیم.

همین کوئری را این بار در بخش Where می نویسیم.

SELECT
	O.CustomerID,
	O.OrderID
FROM dbo.Orders AS O
	WHERE O.OrderID = (SELECT MAX(O1.OrderID) FROM dbo.Orders AS O1
						WHERE O1.CustomerID = O.CustomerID);
GO

در بررسی Subquery، از گزاره EXISTS استفاده می کنیم. با استفاده از این گزاره، کوئری بیرونی در صورتی اجرا می شود که خروجی EXISTS حداقل شامل یک رکورد باشد.

در این کوئری، لیست تمامی سفارش های موجود در جدول Orders را به شرطی که حداقل یک مشتری با شهر تهران داشته باشیم، نمایش می دهیم.

SELECT * FROM dbo.Orders
	WHERE EXISTS (SELECT * FROM dbo.Customers
					WHERE City = N'تهران');
GO

در این کوئری، اطلاعات تمامی مشتریان دارای سفارش را نمایش می دهیم.

SELECT C.* FROM dbo.Customers AS C
	WHERE EXISTS (SELECT * FROM dbo.Orders AS O
					WHERE O.CustomerID = C.CustomerID);
GO

و حالا مشتری هایی را نمایش می دهیم که فاقد سفارش می باشند.

SELECT C.* FROM dbo.Customers AS C
	WHERE NOT EXISTS (SELECT * FROM dbo.Orders AS O
						WHERE O.CustomerID = C.CustomerID);
GO

پایان