Subqueries

Приветствую всех! Сегодня поговорим о подзапросах и рассмотрим как обычно примеры.

Подзапросы-Subqueries

Подзапросы (вложенные запросы) – это запросы, используемые в инструкциях SELECT, INSERT, UPDATE или DELETE.

Связанные Подзапросы-Related Subqueries

Подзапрос является связанным, если в нем (в предложениях WHERE, HAVING) указан столбец таблицы внешнего запроса.

Правила написания подзапросов-Rules

• Вложенные запросы могут быть указаны в инструкциях: SELECT, INSERT, UPDATE или DELETE.
• Каждый подзапрос может содержать один или более подзапросов.
• Предложение WHERE внешнего запроса должно быть совместимо для соединения со столбцом в списке выбора вложенного запроса.

Создадим таблицу на основе которой и рассмотрим подзапросы:

CREATE TABLE SubTest1
(id1 int,
 name varchar(50));
 GO

CREATE TABLE SubTest2
(id2 int,
 name varchar(50));
GO

INSERT SubTest1
VALUES  (1,'one'),
		(2,'two'),
		(3,'three'),
		(4,'four'),
		(5,'five'),
		(9,'nine'),
		(10,'ten');
GO

INSERT SubTest2
VALUES  (1,'one'),
		(2,'two'),
		(3,'three'),
		(4,'four'),
		(5,'five'),
		(6,'six'),
		(7,'seven'),
		(8,'eight');
GO
SELECT * FROM SubTest1;
SELECT * FROM SubTest2;

Вложенные запросы

SELECT * FROM SubTest1
WHERE id1 IN	  
			(SELECT id2 FROM SubTest2); -- вложенный запрос

Вложенный запрос может также содержать конструкцию WHERE

SELECT * FROM SubTest1
WHERE id1 =	  
			(SELECT id2 FROM SubTest2 WHERE name = 'four');

Вложенные запросы можно применять совместно с JOIN‘s

SELECT FirstName + ' ' + LastName as Name, BirthDate 
FROM Person.Person as pc       
JOIN HumanResources.Employee as he 
ON pc.BusinessEntityID = he.BusinessEntityID
WHERE BirthDate = (SELECT MIN(BirthDate) FROM HumanResources.Employee );

Связанные вложенные запросы

• Внешний запрос получает строку и она передается во внутренний запрос.
• Внутренний запрос выполняется с учетом полученных значений.
• Внутренний запрос передает во внешний результируещее значение.
• Внешний запрос использует эти значения для завершения намеченной обработки.

SELECT * FROM SubTest1 AS ST1					  
WHERE name /* 4 */ = /* 3 */(SELECT name					   --|(2)
							 FROM SubTest2 AS ST2			   --|(2)
							 WHERE ST1.id1 = ST2.id2); -- (1)  --|(2)

SELECT * FROM SubTest1 AS ST1					  
WHERE name=(SELECT name				
			FROM SubTest2 AS ST2			 
			WHERE ST1.id1 = ST2.id2);

Более быстро выполняется следующим образом: EXISTS — возвращает true, если выборка возвращает хотя бы одно значение. после нахождения нужного значения EXISTS, не продолжает поиск по таблице.

SELECT * FROM SubTest1  AS ST1
WHERE EXISTS
    (SELECT * FROM SubTest2	ST2
     WHERE ST1.id1 = ST2.id2);

Cвязанный вложенный запрос в списке выборки, (запятая)

SELECT * ,(SELECT name	FROM SubTest2 AS ST2 WHERE ST1.id1 = ST2.id2) AS Name2 
FROM SubTest1 AS ST1;

SELECT *,
	   (SELECT id2 FROM SubTest2 AS ST2	WHERE ST1.id1 = ST2.id2) AS Id2,
	   (SELECT name	FROM SubTest2 AS ST2 WHERE ST1.id1 = ST2.id2) AS Name2
FROM SubTest1 AS ST1;

SELECT *,
	   (SELECT id2 FROM SubTest2 AS ST2	WHERE ST1.id1 = ST2.id2) AS Id2,
	   (SELECT name	FROM SubTest2 AS ST2 WHERE ST1.id1 = ST2.id2) AS Name2
FROM SubTest1 AS ST1
WHERE ST1.id1 = (SELECT id2 FROM SubTest2 AS ST2 
                  WHERE ST1.id1 = ST2.id2);

Показать статистику по количеству проданных единиц товара.

SELECT Products.ProdID, [Description], Qty, TotalPrice 
   	  FROM Products
INNER JOIN OrderDetails
      ON Products.ProdID = OrderDetails.ProdID

SELECT (SELECT  ProdID FROM Products 
	    WHERE Products.ProdID = OrderDetails.ProdID) AS ProdID,
	   (SELECT  [Description] FROM Products 
	    WHERE Products.ProdID = OrderDetails.ProdID) AS [Description], Qty, TotalPrice 
FROM OrderDetails

Вывести общую сумму продаж по сотрудникам

SELECT FName, LName, MName, SUM(TotalPrice) AS [Total Sold] 
		FROM Employees
	     JOIN Orders
			ON Employees.EmployeeID	= Orders.EmployeeID
	     JOIN OrderDetails
			ON Orders.OrderID = OrderDetails.OrderID
GROUP BY  Employees.FName,
		  Employees.LName,  
		  Employees.MName

Создание временной таблицы CREATE TABLE #TableName:

#TableName — локальная таблица, данной таблицей может пользоваться только текущий пользователь

##TableName — глобальная таблица, данной таблицей может пользоваться любой пользователь

CREATE TABLE #TmpTable
(FName varchar(50),
 LName varchar(50),
 MName varchar(50),
 TotalPrice money);
 GO

WITH … AS — Обобщенные табличные выражения — используются вместо временных таблиц, тем самым избегая хранения дублирующихся данных.

WITH Managers (FName, LName, MName, TotalPrice) AS	-- не алиас!
(
SELECT (
        SELECT FName FROM Employees 
		WHERE EmployeeID = (
		                    SELECT EmployeeID FROM Orders
							WHERE Orders.OrderID = OrderDetails.OrderID
							)
		),
		(
		 SELECT LName FROM Employees 
		 WHERE EmployeeID = (
		                     SELECT EmployeeID FROM Orders
							 WHERE Orders.OrderID = OrderDetails.OrderID
							 )
		),
		(
		 SELECT MName FROM Employees 
		 WHERE EmployeeID = (
		                     SELECT EmployeeID FROM Orders
							 WHERE Orders.OrderID = OrderDetails.OrderID
							 )
		),   
		TotalPrice 
FROM OrderDetails
)
         SELECT FName, LName, MName, SUM(TotalPrice) AS [Total Sold] 
         FROM Managers GROUP BY FName, LName, MName;
GO