Подзапрос SQL Server

В этом руководстве вы узнаете о подзапросах SQL Server и о том, как использовать подзапросы для запроса данных.

Введение в подзапрос SQL Server

Подзапрос — это запрос, вложенный в другой оператор, такой как SELECT, INSERT, UPDATE или DELETE.

Давайте рассмотрим следующий пример.

Рассмотрим таблицы заказов и клиентов из примера базы данных.

Следующий оператор показывает, как использовать подзапрос в предложении WHERE оператора SELECT для поиска заказов клиентов, находящихся в Нью-Йорке:

SELECT
    order_id,
    order_date,
    customer_id
FROM
    sales.orders
WHERE
    customer_id IN(
        SELECT
            customer_id
        FROM
            sales.customers
        WHERE
            city = 'New York'
    )
ORDER BY
    order_date DESC;

Вот результат:

В этом примере следующий оператор является подзапросом:

SELECT
    customer_id
FROM
    sales.customers
WHERE
    city = 'New York'

Обратите внимание, что вы всегда должны заключать запрос SELECT подзапроса в скобки().

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

SQL Server выполняет весь приведенный выше пример запроса следующим образом:

Сначала он выполняет подзапрос, чтобы получить список идентификационных номеров клиентов, находящихся в Нью-Йорке.

SELECT
    customer_id
FROM
    sales.customers
WHERE
    city = 'New York'

Во-вторых, SQL Server подставляет идентификационные номера клиентов, возвращаемые подзапросом в операторе IN, и выполняет внешний запрос для получения окончательного набора результатов.

Как видите, используя подзапрос, вы можете объединить два шага. Подзапрос устраняет необходимость выбора идентификационных номеров клиентов и их вставки во внешний запрос. Более того, сам запрос автоматически корректируется при изменении данных клиентов.

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

Подзапрос может быть вложен в другой подзапрос. SQL Server поддерживает до 32 уровней вложенности. Рассмотрим следующий пример:

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price >(
        SELECT
            AVG(list_price)
        FROM
            production.products
        WHERE
            brand_id IN(
                SELECT
                    brand_id
                FROM
                    production.brands
                WHERE
                    brand_name = 'Strider'
                OR brand_name = 'Trek'
            )
    )
ORDER BY
    list_price;

Сначала SQL Server выполняет следующий подзапрос, чтобы получить список идентификационных номеров брендов Strider и Trek:

SELECT
    brand_id
FROM
    production.brands
WHERE
    brand_name = 'Strider'
OR brand_name = 'Trek';

Во-вторых, SQL Server вычисляет среднюю цену всех продуктов, принадлежащих этим брендам.

SELECT
    AVG(list_price)
FROM
    production.products
WHERE
    brand_id IN(6,9)

В-третьих, SQL Server находит продукты, цена которых превышает среднюю цену всех продуктов под брендом Strider или Trek.

Типы подзапросов SQL Server

Подзапрос можно использовать во многих местах:

• Вместо выражения
• С ВНУТРЕННИМ или НЕ ВНУТРЕННИМ
• С ЛЮБЫМ или ВСЕМИ
• С СУЩЕСТВУЕТ или НЕ СУЩЕСТВУЕТ
• В операторе UPDATE, DELETE или INSERT
• В предложении FROM

Подзапрос SQL Server используется вместо выражения

Если подзапрос возвращает одно значение, его можно использовать везде, где используется выражение.

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

SELECT
    order_id,
    order_date,
   (
        SELECT
            MAX(list_price)
        FROM
            sales.order_items i
        WHERE
            i.order_id = o.order_id
    ) AS max_list_price
FROM
    sales.orders o
order by order_date desc;

Подзапрос SQL Server используется с оператором IN

Подзапрос, используемый с оператором IN, возвращает набор из нуля или более значений. После того, как подзапрос возвращает значения, внешний запрос использует их.

Следующий запрос находит названия всех горных и шоссейных велосипедов, которые продаются в веломагазинах.

SELECT
    product_id,
    product_name
FROM
    production.products
WHERE
    category_id IN(
        SELECT
            category_id
        FROM
            production.categories
        WHERE
            category_name = 'Mountain Bikes'
        OR category_name = 'Road Bikes'
    );

Этот запрос оценивается в два этапа:

1. Во-первых, внутренний запрос возвращает список идентификационных номеров категорий, которые соответствуют названию «Горные велосипеды» и коду «Шоссейные велосипеды».
2. Во-вторых, эти значения подставляются во внешний запрос, который находит названия продуктов, идентификационный номер категории которых совпадает с одним из значений в списке.

Подзапрос SQL Server используется с оператором ANY

Подзапрос вводится с помощью оператора ANY и имеет следующий синтаксис:

scalar_expression comparison_operator ANY(subquery)

Предполагая, что подзапрос возвращает список значений v1, v2, … vn. Оператор ANY возвращает TRUE, если одно из значений пары сравнения(scalar_expression, vi) оценивается как TRUE; в противном случае он возвращает FALSE.

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

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price >= ANY(
        SELECT
            AVG(list_price)
        FROM
            production.products
        GROUP BY
            brand_id
    )

Для каждого бренда подзапрос находит максимальную цену по прейскуранту. Внешний запрос использует эти максимальные цены и определяет, какая цена по прейскуранту отдельного продукта больше или равна максимальной цене по прейскуранту любого бренда.

Подзапрос SQL Server используется с оператором ALL

Оператор ALL имеет тот же синтаксис, что и оператор ANY :

scalar_expression comparison_operator ALL(subquery)

Оператор ALL возвращает значение TRUE, если все пары сравнения(scalar_expression, vi) дают значение TRUE; в противном случае он возвращает значение FALSE.

Следующий запрос находит продукты, цена по прейскуранту которых больше или равна средней цене по прейскуранту, возвращаемой подзапросом:

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price >= ALL(
        SELECT
            AVG(list_price)
        FROM
            production.products
        GROUP BY
            brand_id
    )

Подзапрос SQL Server используется с EXISTS или NOT EXISTS

Ниже показан синтаксис подзапроса, введенного с помощью оператора EXISTS :

WHERE [NOT] EXISTS(subquery)

Оператор EXISTS возвращает TRUE, если подзапрос возвращает результаты; в противном случае он возвращает FALSE.

Оператор NOT EXISTS отрицает оператор EXISTS.

Следующий запрос находит клиентов, купивших продукцию в 2017 году:

SELECT
    customer_id,
    first_name,
    last_name,
    city
FROM
    sales.customers c
WHERE
    EXISTS(
        SELECT
            customer_id
        FROM
            sales.orders o
        WHERE
            o.customer_id = c.customer_id
        AND YEAR(order_date) = 2017
    )
ORDER BY
    first_name,
    last_name;

Если вы используете NOT EXISTS вместо EXISTS, вы можете найти клиентов, которые не купили никаких продуктов в 2017 году.

SELECT
    customer_id,
    first_name,
    last_name,
    city
FROM
    sales.customers c
WHERE
    NOT EXISTS(
        SELECT
            customer_id
        FROM
            sales.orders o
        WHERE
            o.customer_id = c.customer_id
        AND YEAR(order_date) = 2017
    )
ORDER BY
    first_name,
    last_name;

Подзапрос SQL Server в предложении FROM

Предположим, что вы хотите найти среднее значение суммы заказов всех продавцов. Для этого вы можете сначала найти количество заказов по персоналу:

SELECT 
   staff_id, 
   COUNT(order_id) order_count
FROM 
   sales.orders
GROUP BY 
   staff_id;

Затем вы можете применить функцию AVG() к этому набору результатов. Поскольку запрос возвращает набор результатов, который выглядит как виртуальная таблица, вы можете поместить весь запрос в предложение FROM другого запроса следующим образом:

SELECT 
   AVG(order_count) average_order_count_by_staff
FROM
(
    SELECT 
 staff_id, 
        COUNT(order_id) order_count
    FROM 
 sales.orders
    GROUP BY 
 staff_id
) t;

Запрос, который вы помещаете в предложение FROM, должен иметь псевдоним таблицы. В этом примере мы использовали t в качестве псевдонима таблицы для подзапроса. Чтобы получить окончательный результат, SQL Server выполняет следующие шаги:

• Выполните подзапрос в предложении FROM.
• Используйте результат подзапроса и выполните внешний запрос.

В этом руководстве вы узнали о концепции подзапросов SQL Server и о том, как использовать различные типы подзапросов для запроса данных.