Вопрос 8: Работа с подзапросами. Объединение результатов запросов. Использование подзапроса как производной (динамической) таблицы. Использование подзапроса как выражения. Использование коррелированных запросов. Запрос, имитирующий соединение (JOIN). Запрос, имитирующий предложение HAVING. Использование коррелированных запросов в предложениях EXISTS и NOT EXISTS.


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

  • обычный запрос SELECT, включающий обычные компоненты списка выборки;
  • обычное предложение FROM, включающее одно или несколько имен таблиц или представлений.
  • Необязательное предложение WHERE.
  • Необязательное предложение GROUP BY.
  • Необязательное предложение HAVING.

Запрос SELECT вложенного запроса всегда заключен в скобки. Он не может включать предложения COMPUTE или FOR BROWSE и может включать предложение ORDER BY только вместе с предложением TOP.

Каждый вложенный запрос, в свою очередь, может содержать один или более вложенных запросов. В инструкцию можно вложить любое количество вложенных запросов. Следующий запрос осуществляет поиск сотрудников, занимающих должность менеджера по продажам.

SELECT LastName, FirstName
FROM Person.Person
WHERE BusinessEntityID IN
    (SELECT BusinessEntityID
     FROM HumanResources.Employee
     WHERE BusinessEntityID IN
        (SELECT BusinessEntityID
         FROM Sales.SalesPerson)
    );
GO

Результат

LastName FirstName
Jiang Stephen
Abbas Syed
Alberts Amy
Ansman-Wolfe Pamela
Campbell David
Carson Jillian
Ito Shu
Mitchell Linda
Reiter Tsvi
Saraiva Jos
Vargas Garrett
Varkey Chudukatil Ranjit
Valdez Rachel
Tsoflias Lynn
Pak Jae
Blythe Michael
Mensa-Annan Tete

Самый глубоко вложенный запрос возвращает идентификаторы указанных сотрудников. Запрос уровнем выше оперирует с полученными идентификаторами и возвращает контактные идентификаторы сотрудников. Наконец, во внешнем запросе по полученным контактным идентификаторам извлекаются имена сотрудников.

Использование подзапроса как выражения

Поиск товаров, цена которых выше средней:

SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)

Объединение результатов запросов

UNION сцепляет результаты двух запросов в один результирующий набор. Вы указываете, будет ли результирующий набор включать повторяющиеся строки:

  • UNION ALL — повторяющиеся строки включаются.
  • UNION — повторяющиеся строки исключаются

При выполнении следующего примера в результирующий набор включается содержимое столбцов ProductModelID и Name таблиц ProductModel и Gloves:

SELECT ProductModelID, Name  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves  
ORDER BY Name;  
GO

Создание таблицы с использованием UNION

При выполнении следующего примера предложение INTO во второй инструкции SELECT указывает, что в таблице с именем ProductResults содержится итоговый результирующий набор объединения выбранных столбцов таблиц ProductModel и Gloves. Таблица Gloves была создана в результате выполнения первой инструкции SELECT.

-- Create Gloves table.  
SELECT ProductModelID, Name  
INTO dbo.Gloves  
FROM Production.ProductModel  
WHERE ProductModelID IN (3, 4);  
GO  

-- Uses AdventureWorks  

SELECT ProductModelID, Name  
INTO dbo.ProductResults  
FROM Production.ProductModel  
WHERE ProductModelID NOT IN (3, 4)  
UNION  
SELECT ProductModelID, Name  
FROM dbo.Gloves;  
GO

Коррелирующие подзапросы

Подзапросы бывают коррелирующими и некоррелирующими. Некоррилирующий запрос выполняется один раз для всего внешнего запроса. Но также существуют коррелирующие подзапросы (correlated subquery), результаты которых зависят от строк, которые выбираются в основном запросе. Например, выберем все заказы из таблицы Orders, добавив к ним информацию о товаре:

SELECT  CreatedAt, 
        Price, 
        (SELECT ProductName FROM Products 
        WHERE Products.Id = Orders.ProductId) AS Product
FROM Orders

Здесь для каждой строки из таблицы Orders будет выполняться подзапрос, результат которого зависит от столбца ProductId. И каждый подзапрос может возвращать различные данные.

Запрос, имитирующий JOIN

В примере используются вложенный запрос SELECT и соединение SELECT, которые возвращают один и тот же результирующий набор:

/* SELECT statement built using a subquery. */
SELECT Name
FROM Production.Product
WHERE ListPrice =
    (SELECT ListPrice
     FROM Production.Product
     WHERE Name = 'Chainring Bolts' );

/* SELECT statement built using a join that returns
   the same result set. */
SELECT Prd1. Name
FROM Production.Product AS Prd1
     JOIN Production.Product AS Prd2
       ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2. Name = 'Chainring Bolts';

Запрос, имитирующий HAVING (если я правильно понял)

В следующем примере удваивается значение столбца ListPrice таблицы Production.Product. Вложенный запрос в предложении WHERE ссылается на таблицу Purchasing.ProductVendor для ограничения количества обновляемых строк таблицы Product только теми, у которых идентификатор BusinessEntity равен 1540.

UPDATE Production.Product
SET ListPrice = ListPrice * 2
WHERE ProductID IN
    (SELECT ProductID 
     FROM Purchasing.ProductVendor
     WHERE BusinessEntityID = 1540);
GO

(версия из лабы, по-моему, более правильная (Миша))

Запрос выводит id родителей и количество детей у этих родителей, если количество детей больше 4.

Запрос с having

select adult_member_no, COUNT(member_no)
from juvenile
group by adult_member_no
having COUNT(member_no) > 4;

Запрос без having

with adultInfo as (
select adult_member_no, COUNT (member_no) as No_Of_Children
from juvenile
group by adult_member_no
)

select *
from adultInfo
where No_Of_Children > 4

results matching ""

    No results matching ""