Подзапросы и операции над множествами

Курс MS SQL с нуля · Урок 6 из 12 · Часть 2. Соединения и агрегация

Подзапрос — это SELECT внутри SELECT. Урок разводит три их рода, воспитывает вкус к EXISTS и знакомит с операциями над множествами — последней ступенью уровня «читать».

Перед уроком база обязана быть чистой: если проходили уроки изменения данных, восстановите TechStore скриптами урока 2.

Содержание

1. Что вы освоите
- Перейти к разделу
2. Три рода подзапросов
- Перейти к разделу
3. EXISTS и мина NOT IN
- Перейти к разделу
4. Множества: UNION, EXCEPT, INTERSECT
- Перейти к разделу
5. Задачи и эталонные решения
- Перейти к разделу
6. Типичные ошибки урока
- Перейти к разделу
7. Чек-лист практики и источники
- Перейти к разделу

1. Что вы освоите

К концу урока вы:

различаете три рода подзапросов и ставите их на свои места;
выбираете EXISTS для проверки существования и знаете почему;
объясняете, как NULL в подзапросе обесценивает NOT IN целиком;
агрегируете многие-строки до соединения, а не после;
склеиваете и вычитаете множества через UNION ALL, UNION, EXCEPT.

↑ К оглавлению

2. Три рода подзапросов

Род подзапроса	Где стоит	Контракт
Скалярный	везде, где выражение	ровно одно значение: две строки — Msg 512, ноль — NULL
Список	в правой части IN	один столбец любой длины
Производная таблица	во FROM с алиасом	обычная таблица в границах оператора

Чистый код

SELECT p.ProductID, p.Name, p.Price
FROM dbo.Products AS p
WHERE p.IsActive = 1
  AND p.Price BETWEEN (SELECT AVG(a.Price) FROM dbo.Products AS a WHERE a.IsActive = 1)
                  AND (SELECT 2 * AVG(a.Price) FROM dbo.Products AS a WHERE a.IsActive = 1);

SELECT o.OrderID, lines.order_amount
FROM dbo.Orders AS o
JOIN (SELECT oi.OrderID, SUM(oi.Quantity * oi.UnitPrice) AS order_amount
      FROM dbo.OrderItems AS oi GROUP BY oi.OrderID) AS lines
     ON lines.OrderID = o.OrderID;

Построчный разбор

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

SELECT p.ProductID, p.Name, p.Price
-- → товары из середины ценового спектра активных
FROM dbo.Products AS p
WHERE p.IsActive = 1
  AND p.Price BETWEEN (SELECT AVG(a.Price) FROM dbo.Products AS a WHERE a.IsActive = 1)
-- → скалярный подзапрос: агрегат гарантирует ровно одно значение
                  AND (SELECT 2 * AVG(a.Price) FROM dbo.Products AS a WHERE a.IsActive = 1);
-- → верхняя граница — средняя цена, помноженная на два

SELECT o.OrderID, lines.order_amount
-- → производная таблица: суммы позиций сгруппированы до соединения
FROM dbo.Orders AS o
JOIN (SELECT oi.OrderID, SUM(oi.Quantity * oi.UnitPrice) AS order_amount
      FROM dbo.OrderItems AS oi GROUP BY oi.OrderID) AS lines
-- → соединение уже один-к-одному; производной обязателен алиас
     ON lines.OrderID = o.OrderID;

Имя обязательно: производной таблице нужен алиас (AS lines), а все её вычисляемые столбцы — собственные имена; без этого сервер отвечает Msg 102 о синтаксисе рядом с закрывающей скобкой.

↑ К оглавлению

3. EXISTS и мина NOT IN

EXISTS проверяет непустоту связанного подзапроса — «есть ли хоть одна». Список столбцов внутри безразличен, принято писать SELECT 1:

Чистый код

SELECT c.CustomerID, c.FullName
FROM dbo.Customers AS c
WHERE EXISTS (
    SELECT 1
    FROM dbo.Orders AS o
    WHERE o.CustomerID = c.CustomerID
      AND o.Status = N'Cancelled'
);

Построчный разбор

SELECT c.CustomerID, c.FullName
-- → клиент с отменами интересует один раз
FROM dbo.Customers AS c
WHERE EXISTS (
-- → EXISTS проверяет непустоту: хватит первой встречной строки
    SELECT 1
    FROM dbo.Orders AS o
    WHERE o.CustomerID = c.CustomerID
-- → корреляция: подзапрос смотрит на внешнюю строку через алиас c
      AND o.Status = N'Cancelled'
);

NOT IN и NULL: обнуление целиком: если список подзапроса способен дать NULL, условие NOT IN не пропускает ничего: сервер не может утверждать, что значения нет среди неизвестного. В курсе отрицание пишется через NOT EXISTS — он к NULL устойчив; это и есть ответ «почему запрос вдруг пуст» при изменении данных.

Опыт урока

задача 6.3 делается руками

Решается «товары без продаж» дважды: через NOT IN и через NOT EXISTS. Затем в список NOT IN принудительно добавляется NULL (UNION ALL SELECT NULL) — и выборка обнуляется, тогда как NOT EXISTS работает. Это лучшее место увидеть мину раз и навсегда.

↑ К оглавлению

4. Множества: UNION, EXCEPT, INTERSECT

Операции над множествами собирают результаты запросов столбцами в столбцы:

Операция	Смысл	Дубли
UNION ALL	склейка как есть	сохраняются: быстро
UNION	склейка множеств	убираются: сортировка ценой времени
EXCEPT	из первого вычесть второе	по множествам
INTERSECT	пересечение	по множествам

число столбцов одинаково, типы совместимы, имена берутся из первого SELECT;
ORDER BY допускается один раз — в самом конце всей конструкции;
для склейки заведомо непересекающихся множеств берётся ALL.

Чистый код

SELECT o.CustomerID FROM dbo.Orders AS o
EXCEPT
SELECT o.CustomerID FROM dbo.Orders AS o WHERE o.Status = N'Delivered';
GO

Построчный разбор
Ниже приведён тот же фрагмент с пояснением после каждой смысловой строки. Строки, которые начинаются с двух дефисов и стрелки вправо, содержат комментарий преподавателя. Эти строки не являются обязательной частью программы.
```
SELECT o.CustomerID FROM dbo.Orders AS o
EXCEPT
SELECT o.CustomerID FROM dbo.Orders AS o WHERE o.Status = N'Delivered';
-- → клиенты с заказами, но без единственного Delivered: разность множеств
GO
```
SQL

↑ К оглавлению

5. Задачи и эталонные решения

Шесть задач на контексте TechStore.

товары дороже средней цены по активным — один запрос со скалярным подзапросом;
клиенты с хоть одним отменённым заказом — EXISTS, имена без повторов;
товары без продаж дважды: NOT IN против NOT EXISTS, затем опыт с NULL;
дата и сумма каждого заказа: Orders и производная таблица из OrderItems, контроль равенства с задачей 4.6;
справочник городов из двух непересекающихся плеч — UNION без дублей, затем UNION ALL и объяснение разницы чисел;
есть заказы, но нет Delivered — через EXCEPT и встречно через NOT EXISTS.

Эталоны урока

открывать после самостоятельной попытки

Чистый код

SELECT c.CustomerID, c.FullName, c.City
FROM dbo.Customers AS c
WHERE EXISTS (
    SELECT 1 FROM dbo.Orders AS o
    WHERE o.CustomerID = c.CustomerID AND o.Status = N'Cancelled'
);
GO

SELECT p.ProductID, p.Name
FROM dbo.Products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM dbo.OrderItems AS oi WHERE oi.ProductID = p.ProductID
);
GO

SELECT o.OrderID, o.OrderDate, lines.order_amount
FROM dbo.Orders AS o
JOIN (SELECT oi.OrderID, SUM(oi.Quantity * oi.UnitPrice) AS order_amount
      FROM dbo.OrderItems AS oi GROUP BY oi.OrderID) AS lines
     ON lines.OrderID = o.OrderID
ORDER BY o.OrderID;
GO

Построчный разбор

SELECT c.CustomerID, c.FullName, c.City
-- → задача 6.2: клиенты хотя бы с одной отменой
FROM dbo.Customers AS c
WHERE EXISTS (
    SELECT 1 FROM dbo.Orders AS o
    WHERE o.CustomerID = c.CustomerID AND o.Status = N'Cancelled'
);
-- → корреляция через алиасы обеих сторон
GO

SELECT p.ProductID, p.Name
-- → задача 6.3: товары, ни разу не проданные
FROM dbo.Products AS p
WHERE NOT EXISTS (
-- → отрицание через NOT EXISTS: мины NOT IN не происходит
    SELECT 1 FROM dbo.OrderItems AS oi WHERE oi.ProductID = p.ProductID
);
GO

SELECT o.OrderID, o.OrderDate, lines.order_amount
-- → задача 6.4: заказы с суммой позиций
FROM dbo.Orders AS o
JOIN (SELECT oi.OrderID, SUM(oi.Quantity * oi.UnitPrice) AS order_amount
      FROM dbo.OrderItems AS oi GROUP BY oi.OrderID) AS lines
-- → агрегация до соединения, у производной есть алиас
     ON lines.OrderID = o.OrderID
ORDER BY o.OrderID;
GO

↑ К оглавлению

6. Типичные ошибки урока

Subquery returned more than 1 value (Msg 512) — скалярный подзапрос без агрегата или с неполным фильтром; лечится агрегатом либо TOP (1) с осознанным ORDER BY.
NOT IN с NULL — пустой результат целиком; см. опыт задачи 6.3; в бою это почти всегда баг данных, а не «вдруг пусто».
Корреляция вслепую — подзапрос ссылается на внешнюю таблицу без алиаса и коррелируется не с той строкой; алиасы — у всех участников.
UNION там, где нужен ALL — лишняя сортировка на миллионах строк наглядна во времени; для непересекающихся источников — ALL.
ORDER BY внутри ветки — синтаксическая ошибка; сортировка пишется один раз внизу.
Производная без алиаса — Msg 102 рядом со скобкой; имя придумать, вычисляемые столбцы поименовать.

↑ К оглавлению

7. Чек-лист практики и источники

Чек-лист урока 6

отметки делаются по факту

три рода подзапросов различаются по имени;
скалярный подзапрос защищён агрегатом или TOP;
отрицание пишется через NOT EXISTS;
мина NOT IN с NULL увидена вживую на своём опыте;
агрегирование до соединения — привычка;
UNION и UNION ALL выбираются осознанно.

Источники для углубления:

Итог: порог чтения пройден: выборка любой вложенности собирается контролируемо. Часть 3 начинает запись: урок 7 — INSERT, UPDATE, DELETE с безопасными паттернами.

Проверено: 23.10.2026

↑ К оглавлению

Конспект:

Курс MS SQL с нуля

Четверг, 22 октября 2026

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