Производная таблица создается из столбцов и строк, полученных в результате запроса.
Для её создания необходимо заключить запрос в скобки и задать для результата запроса псевдоним.
Затем эту производную таблицу можно использовать в операторе JOIN как обычную.
Вывести названия компаний, заказавших ‘Chocolade’ и ‘Tofu’
SELECT DISTINCT c.CompanyName FROM dbo.Customers c
INNER JOIN
(SELECT CustomerID FROM dbo.Orders o
INNER JOIN dbo.[Order Details] od ON o.OrderID=od.OrderID
INNER JOIN dbo.Products p ON p.ProductID=od.ProductID
WHERE p.ProductName='Chocolade') AS choc
ON c.CustomerID=choc.CustomerID
INNER JOIN
(SELECT CustomerID FROM dbo.Orders o
INNER JOIN dbo.[Order Details] od ON o.OrderID=od.OrderID
INNER JOIN dbo.Products p ON p.ProductID=od.ProductID
WHERE p.ProductName='Tofu') AS T
ON c.CustomerID=T.CustomerID
Понятие подзапроса
Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT.
В таком случае приходят на помощь законченные операторы SELECT, внедренные в тело другого оператора SELECT.
Внутренний подзапрос представляет собой также оператор SELECT, а кодирование его предложений подчиняется тем же правилам, что и основного оператора SELECT.
Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >, <=, >=, <>) в списке полей вывода оператора SELECT, в предложения WHERE и HAVING внешнего оператора SELECT – они получают название подзапросов или вложенных запросов.
Подзапрос – это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором.
Текст подзапроса должен быть заключен в скобки.
К подзапросам применяются следующие правила и ограничения:
- фраза ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе;
- список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений – за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS;
- по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы);
- если подзапрос является одним из двух операндов, участвующих в операции сравнения, то запрос должен указываться в правой части этой операции.
Можно выделить типы подзапросов:
1. Скалярный подзапрос возвращает единственное значение. В принципе, он может использоваться везде, где требуется указать единственное значение.
2. Табличный подзапрос возвращает множество значений, т.е. значения одного или нескольких столбцов таблицы, размещенные в более чем одной строке. Он возможен везде, где допускается наличие таблицы.
Определить Номер компании, сделавшей самый последний заказ
SELECT CustomerID FROM Orders
WHERE OrderDate=(SELECT MAX(OrderDate) FROM Orders)
Указать для каждого заказа номер, стоимость, и отклонение стоимости данного заказа от максимальной стоимости заказа
SELECT OrderID, SUM(UnitPrice*Quantity*(1-Discount)) as Summ,
SUM(UnitPrice*Quantity*(1-Discount))-
(SELECT TOP 1 SUM(UnitPrice*Quantity*(1-Discount)) FROM [Order Details]
GROUP BY OrderID
ORDER BY SUM(UnitPrice*Quantity*(1-Discount))DESC)
FROM [Order Details]
GROUP BY OrderID
Подзапросы, возвращающие множество значений
Во многих случаях значение, подлежащее сравнению в предложениях WHERE или HAVING, представляет собой не одно, а несколько значений.
Вложенные подзапросы генерируют непоименованное промежуточное отношение, временную таблицу. Оно может использоваться только в том месте, где появляется в подзапросе. К такому отношению невозможно обратиться по имени из какого-либо другого места запроса. Применяемые к подзапросу операции основаны на тех операциях, которые, в свою очередь, применяются к множеству, а именно:
- { WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
- { WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос);
- {WHERE | HAVING } [ NOT ] EXISTS (подзапрос);