По материалам статьи Craig Freedman: Scalar Subqueries

Скалярный подзапрос — это подзапрос, который возвращает одну строку. Для некоторых запросов сразу видно ,что они скалярные. 

Пример:

create table T1 (a int, b int)
create table T2 (a int, b int)
select *
from T1
where T1.a > (select max(T2.a) from T2 where T2.b < T1.b)

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

  ^{|--Filter(WHERE:([T1].[a]>[Expr1008]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[b]))
            |--Table Scan(OBJECT:([T1]))
            |--Stream Aggregate(DEFINE:([Expr1008]=MAX([T2].[a])))
                 |--Table Scan(OBJECT:([T2]), WHERE:([T2].[b]<[T1].[b]))}

Как и следовало ожидать, этот план работает путем просмотра всех строк T1 и потом в соединении Nested Loops Join будет выполняться оценка результата подзапроса, по одному разу для каждой его строки. Поскольку подзапрос всегда возвращает только одну строку, количество строк, подаваемых на вход соединению, будет точно равно количеству строк T1. Далее, будет применен фильтр, который в предложении WHERE оценивает строки исходного запроса ([Expr1008] - результат подзапроса) и определяет, следует ли возвращать строку.

Утверждение (Assert)

А что произойдет, если подзапрос не гарантирует возврат единственной строки в том контексте, в котором мы ожидаем, что он вернёт одну строку? Например, давайте уберём агрегат из показанного выше запроса:

select * 
from T1
where T1.a = (select T2.a from T2 where T2.b = T1.b)

Без агрегата больше нет гарантии, что подзапрос каждый раз будет возвращать только одну строку. Если подзапрос возвращает больше одной строки для строки T1 нашего соединения, мы должны увидеть ошибку:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Как же SQL Server обнаруживает эту ошибку? Вот план запроса:

  ^{|--Filter(WHERE:([T1].[a]=[Expr1010]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[b]))
            |--Table Scan(OBJECT:([T1]))
            |--Assert(WHERE:(CASE WHEN [Expr1009]>(1) THEN (0) ELSE NULL END))
                 |--Stream Aggregate(DEFINE:([Expr1009]=Count(*), [Expr1010]=ANY([T2].[a])))
                      |--Table Scan(OBJECT:([T2]), WHERE:([T2].[b]=[T1].[b]))}

По сути, это тот же план запроса, что и в первом примере, за исключением того, что оптимизатор добавляет count(*), чтобы подзапрос гарантированно возвращал одну строку. Оператор Assert обеспечивает эту гарантию. Если обнаруживается, что подзапрос вернул больше одной строки (т. е. если [Expr1009] > 1), возникает указанная выше ошибка. Обратите внимание, что мы используем оператор Assert для проверки и других условий, например, для ограничений (проверка значения, ссылочная целостность и т. д.), для контроля максимального уровня рекурсии в CTE, для предупреждения о дублях при вставке ключа в индексы, когда это делается с IGNORE_DUP_KEY, и для многих других случаев.

Специальный агрегат ANY нужен только для внутреннего использования, и он, как подсказывает его название, возвращает любую строку. Поскольку если просмотр T2 возвращает больше одной строки мы выдаем ошибку, агрегат ANY реально не будет использован. Также легко можно было бы использовать агрегаты MIN или MAX и получить тот же результат. Но тут некая агрегация необходима, поскольку при агрегации потока ожидается, что каждый столбец на выходе будет агрегирован, либо указан в предложении group by (которого в данном примере нет). По той же причине не компилируется следующий запрос:

select count(*), T2.a from T2

Msg 8120, Level 16, State 1, Line 1
Column 'T2.a' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Производительность

Оператор Assert сам по себе не требует больших затрат, но он ограничивает набор преобразований, доступных оптимизатору. В частности, оптимизатор должен использовать соединение Nested Loops и не может изменять порядок соединения. Во многих случаях создание уникального индекса или переписывание запроса избавляет от оператора Assert и делает план запроса более удачным. 

Пример:

create unique clustered index T2b on T2(b)
select *
from T1
where T1.a = (select T2.a from T2 where T2.b = T1.b)

Благодаря уникальному индексу T2b оптимизатор уверен, что каждая итерация подзапроса вернёт ровно одну строку. Новый план будет такой:

  ^{|--Nested Loops(Inner Join, OUTER REFERENCES:([T1].[a], [T1].[b]))
       |--Table Scan(OBJECT:([T1]))
       |--Clustered Index Seek(OBJECT:([T2].[T2b]), SEEK:([T2].[b]=[T1].[b]),  WHERE:([T1].[a]=[T2].[a]) ORDERED FORWARD)}

Мало того, что план стал проще, благодаря устранению операторов Stream Aggregate и Assert, но теперь это стало обычным соединением, и мы можем изменить порядок и тип соединения. 

Пример:

select *
from T1
where T1.a = (select T2.a from T2 where T2.b = T1.b)
option(merge join)

  ^{|--Merge Join(Inner Join, MERGE:([T2].[b], [T2].[a])=([T1].[b], [T1].[a]), RESIDUAL:([T2].[a]=[T1].[a] AND [T1].[b]=[T2].[b]))
       |--Clustered Index Scan(OBJECT:([T2].[T2b]), ORDERED FORWARD)
       |--Sort(ORDER BY:([T1].[b] ASC, [T1].[a] ASC))
            |--Table Scan(OBJECT:([T1]))}

Попробуйте применить эту подсказку без уникального индекса, и вы получите ошибку:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

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

Если вы не можете или не хотите создавать индекс, можно переписать этот запрос иначе:

drop index T2.T2b
select *
from T1
where T1.a in (select T2.a from T2 where T2.b = T1.b)

Все, что было изменено, это замена скалярного подзапроса на подзапрос с IN. Это позволяет избавиться от ограничений из-за одной строки. Точно также можно использовать в подзапросе EXISTS:

select *
from T1
where exists (select T2.a from T2 where T2.a = T1.a and T2.b = T1.b)

Оба примера эквивалентны. Они отличаются от исходного запроса только тем, что не завершаться ошибкой, если подзапрос вернёт больше одной строки. Вот план запроса для любого из этих примеров:

  ^{|--Nested Loops(Left Semi Join, WHERE:([T2].[b]=[T1].[b] AND [T1].[a]=[T2].[a]))
       |--Table Scan(OBJECT:([T1]))
       |--Table Scan(OBJECT:([T2]))}

Обратите внимание, что внутреннее соединение теперь полу-соединение, поскольку каждая строка из T1 может соответствовать не одной, а нескольким строкам в T2. Опять же, мы можем использовать подсказку оптимизатору, чтобы заказать другой тип соединения:

select *
from T1
where T1.a in (select T2.a from T2 where T2.b = T1.b)
option(hash join)

  ^{|--Hash Match(Left Semi Join, HASH:([T1].[b], [T1].[a])=([T2].[b], [T2].[a]), RESIDUAL:([T2].[b]=[T1].[b] AND [T1].[a]=[T2].[a]))
       |--Table Scan(OBJECT:([T1]))
       |--Table Scan(OBJECT:([T2]))}

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