Subqueries | www.nookery.ru

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

Подзапросы-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;

CREATE TABLE SubTest1

(id1 int,

name varchar(50));

CREATE TABLE SubTest2

(id2 int,

name varchar(50));

INSERT SubTest1

VALUES (1,'one'),

(2,'two'),

(3,'three'),

(4,'four'),

(5,'five'),

(9,'nine'),

(10,'ten');

INSERT SubTest2

VALUES (1,'one'),

(2,'two'),

(3,'three'),

(4,'four'),

(5,'five'),

(6,'six'),

(7,'seven'),

(8,'eight');

SELECT * FROM SubTest1;

SELECT * FROM SubTest2;

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

SELECT * FROM SubTest1
WHERE id1 IN	  
			(SELECT id2 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');

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 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);

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);

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;

1 2	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;

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 *,

(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 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 (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

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

CREATE TABLE #TmpTable

(FName varchar(50),

LName varchar(50),

MName varchar(50),

TotalPrice money);

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

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;

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

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

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

Добавить комментарий Отменить ответ