По материалам статьи Craig Freedman: Subqueries in CASE Expressions
В этой статье будет рассмотрено, как SQL Server обрабатывает подзапросы в выражении CASE. Кроме того, будут рассмотрены несколько экзотических возможностей соединений.
Скалярные выражения
Для простых случаев использования выражений CASE (без подзапросов), возможна оценка только выражения CASE, состоящего из нескольких скалярных выражений:
create table T1 (a int, b int, c int)
select
case
when T1.a > 0 then
T1.b
else
T1.c
end
from T1|
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [T1].[a]>(0) THEN [T1].[b] ELSE [T1].[c] END)) |--Table Scan(OBJECT:([T1])) |
Этот план запроса подразумевает просмотр таблицы T1 и оценку выражения CASE для каждой её строки. Оператор Compute Scalar вычисляет значение выражения CASE, включая оценку условия и принятие решения, будет ли выполняться оценка в предложении THEN или ELSE.
Если в выражение CASE поместить подзапросы, всё становится немного сложнее и существенно интересней.
Предложение WHEN
Давайте сначала добавим к предложению WHEN простой подзапрос:
create table T2 (a int, b int)
select
case
when exists (select * from T2 where T2.a = T1.a) then
T1.b
else
T1.c
end
from T1|
|--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1010] THEN [T1].[b] ELSE [T1].[c] END)) |--Nested Loops(Left Semi Join, OUTER REFERENCES:([T1].[a]), DEFINE:([Expr1010] = [PROBE VALUE])) |--Table Scan(OBJECT:([T1])) |--Table Scan(OBJECT:([T2]), WHERE:([T2].[a]=[T1].[a])) |
Как и для других EXISTS подзапросов, этот план использует левое полусоединение, позволяющее проверить, имеется ли для каждой строки в T1 соответствующая строка в T2. Однако, нормальное полусоединение (или анти-полусоединение) возвращает только парные строки (или непарные). В этом случае, должно быть возвращено хоть что-то (T1.b или T1.c) для каждой строки в T1. Мы не можем просто отказаться от строки T1 только потому, что для неё нет соответствующей строки в T2.
Решением стал специальный тип полусоединения со столбцом пробной таблицы. Это полусоединение возвращает все внешние соответствующие или не соответствующие строки, и устанавливает столбец пробной таблицы (в нашем случае это [Expr1010]) в истину или ложь, что указывает, была ли найдена соответствующая строка T1. После этого, выполняется оценка выражения CASE, для чего используется столбец пробной таблицы, с помощью которого определяется, какое значение будет возвращено.
Предложение THEN
Давайте теперь попробуем добавить к предложению THEN простой подзапрос:
create table T3 (a int unique clustered, b int)
insert T1 values(0, 0, 0)
insert T1 values(1, 1, 1)
select
case
when T1.a > 0 then
(select T3.b from T3 where T3.a = T1.b)
else
T1.c
end
from T1Я добавил к T3 ограничение уникальности, позволяющее гарантировать, что скалярный подзапрос возвратит только одну строку. Без ограничения, план запроса был бы более сложен, поскольку оптимизатору нужно было бы гарантировать, что подзапрос действительно возвратит только одну строку, и ему пришлось бы выдавать ошибку, если бы вернулось больше одной строки.
Я также добавил в T1 ещё две строки, причём, условие в предложение WHEN выдаст ложь для первой строки и истину для второй строки. Таким образом, первая строка у нас будет подходить для ELSE, а вторая для THEN. Обратите внимание, что значение подзапроса в THEN будет использоваться, только если предложение WHEN будет истинно.
Ниже показан профиль статистики для плана исполнения этого запроса:
|
Rows Executes 0 0 |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [T1].[a]>(0) THEN [T3].[b] ELSE [T1].[c] END)) 2 1 |--Nested Loops(Left Outer Join, PASSTHRU: (IsFalseOrNull [T1].[a]>(0)), OUTER REFERENCES:([T1].[b])) 2 1 |--Table Scan(OBJECT:([T1])) 0 1 |--Clustered Index Seek(OBJECT:([T3].[UQ__T3__412EB0B6]), SEEK:([T3].[a]=[T1].[b]) ORDERED FORWARD) |
Этот план запроса использует специальный тип соединения вложенных циклов, в котором задействуется предикат PASSTHRU. Соединение оценивает предикат PASSTHRU для каждой внешней строки. Если предикат PASSTHRU оценивается как истина, соединение немедленно возвращает строку, подобную полусоединению или внешнему соединению. Если же предикат PASSTHRU оценивается как ложь, соединение выполняется обычным образом, т.е. выполняется попытка соединения внешней строки с внутренней строкой.
В показанном выше примере, предикат PASSTHRU выражения CASE является инверсией (обратите внимание на функцию IsFalseOrNull) предложения WHEN. Если предложение WHEN оценивается как истина, предикат PASSTHRU оценивается как ложь, происходит соединение, и поиск по внутренней части соединения выполняет оценку подзапроса THEN. Если предложение WHEN оценивается как ложь, предикаты PASSTHRU оценивается как истина, соединение пропускается, а поиск или подзапрос THEN не выполняется.
Обратите внимание, что просмотр T1 возвращает 2 строки, хотя поиск в T3 выполняется только один раз. Так происходит потому, что в нашем примере предложение WHEN истинно только для одной из двух строк. Предиката PASSTHRU является единственным механизмом, когда число строк на внешней стороне соединения вложенных циклов не соответствует в точности числу строк на внутренней стороне.
Также обратите внимание, что после того, как будет использовано внешнее соединение, невозможно гарантировать, что подзапрос в THEN вернёт хоть что-нибудь (в действительности гарантируется только то, что благодаря ограничению уникальности будет возвращено не более одной строки). Если подзапрос не возвращает строк, внешнее соединение просто возвратит NULL для T3.b. Если бы использовалось внутреннее соединение, отказаться от строки T1 было бы неправильно. Предостережение: я прогонял эти примеры на SQL Server 2005. Если Вы будете выполнять этот пример на SQL Server 2000, предикат PASSTHRU будет виден, но в плане исполнения запроса он появится как регулярный предикат предложения WHERE. К сожалению, для SQL Server 2000 не существует простого пути различения регулярных предикатов и предиката PASSTHRU.
Предложение ELSE и несколько предложений WHEN
Подзапрос в предложении ELSE работает точно так же, как и подзапрос в предложении THEN. Для оценки условия подзапроса будет использован предикат PASSTHRU.
Точно так же выражение CASE с несколькими предложениями WHEN с подзапросами в каждом предложении THEN будет работать аналогичным образом. Отличие только в том, что предикатов PASSTHRU будет больше.
Например:
create table T4 (a int unique clustered, b int)
create table T5 (a int unique clustered, b int)
select
case
when T1.a > 0 then
(select T3.b from T3 where T3.a = T1.a)
when T1.b > 0 then
(select T4.b from T4 where T4.a = T1.b)
else
(select T5.b from T5 where T5.a = T1.c)
end
from T1|
|--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [T1].[a]>(0) THEN [T3].[b] ELSE CASE WHEN [T1].[b]>(0) THEN [T4].[b] ELSE [T5].[b] END END)) |--Nested Loops(Left Outer Join, PASSTHRU:([T1].[a]>(0) OR [T1].[b]>(0)), OUTER REFERENCES:([T1].[c])) |--Nested Loops(Left Outer Join, PASSTHRU:([T1].[a]>(0) OR IsFalseOrNull [T1].[b]>(0)), OUTER REFERENCES:([T1].[b])) | |--Nested Loops(Left Outer Join, PASSTHRU: (IsFalseOrNull [T1].[a]>(0)), OUTER REFERENCES:([T1].[a])) | | |--Table Scan(OBJECT:([T1])) | | |--Clustered Index Seek(OBJECT:([T3].[UQ__T3__164452B1]), SEEK:([T3].[a]=[T1].[a]) ORDERED FORWARD) | |--Clustered Index Seek(OBJECT:([T4].[UQ__T4__182C9B23]), SEEK:([T4].[a]=[T1].[b]) ORDERED FORWARD) |--Clustered Index Seek(OBJECT:([T5].[UQ__T5__1A14E395]), SEEK:([T5].[a]=[T1].[c]) ORDERED FORWARD) |
В этом плане запроса три соединения вложенных циклов с предикатами PASSTHRU. Для каждой строки T1, только один из трех предикатов PASSTHRU оценивается как истина, и только один из трех подзапросов будет выполнен. Обратите внимание, что пока второе предложение WHEN соответствует "T1.b > 0", это значит, что первое предложение WHEN, где "T1.a > 0" оказалось ложным. Это также относится и к предложению ELSE. Таким образом, предикаты PASSTHRU для второго и третьего подзапроса включают проверку "T1.a > 0 OR…".
Столбец пробной таблицы в качестве предиката PASSTHRU
Наконец, давайте рассмотрим запрос с подзапросами в предложениях WHEN и в предложениях THEN. Также, для разнообразия, давайте переместим выражение CASE из списка SELECT в предложение WHERE.
select *
from T1
where 0 =
case
when exists (select * from T2 where T2.a = T1.a) then
(select T3.b from T3 where T3.a = T1.b)
else
T1.c
end|
|--Filter(WHERE:((0)=CASE WHEN [Expr1013] THEN [T3].[b] ELSE [T1].[c] END)) |--Nested Loops(Left Outer Join, PASSTHRU:(IsFalseOrNull [Expr1013]), OUTER REFERENCES:([T1].[b])) |--Nested Loops(Left Semi Join, OUTER REFERENCES:([T1].[a]), DEFINE:([Expr1013] = [PROBE VALUE])) | |--Table Scan(OBJECT:([T1])) | |--Table Scan(OBJECT:([T2]), WHERE:([T2].[a]=[T1].[a])) |--Clustered Index Seek(OBJECT:([T3].[UQ__T3__164452B1]), SEEK:([T3].[a]=[T1].[b]) ORDERED FORWARD) |
В этом плане исполнения запроса имеется левое полусоединение со столбцом пробной таблицы, позволяющее оценить подзапрос в предложении WHEN, и соединение вложенных циклов с предикатом PASSTHRU для столбца пробной таблицы, позволяющее решить, выполнять ли оценку подзапроса в предложении THEN. Поскольку выражение CASE было перемещено в предложение WHERE, для оценки выходных значений из списка SELECT вместо оператора Compute Scalar используется оператор Filter, с помощью которого определяется, какие строки будут возвращены. Все остальное работает точно так же.
Далее…
В следующей статье, я рассмотрю несколько других типов подзапросов.
akryukov
Горшочек не вари.
Почему вы думаете, что копипастить мануал к MSSQL - хорошая идея? Может быть на msdn оно уже переведено?
mssqlhelp Автор
Это не документация, а блог одного из разработчиков MS SQL Server. Перевод этой статьи был мной сделан в 2006 году и размещён на SQL.RU
Сейчас SQL.RU не стало, и я переношу сюда те статью, которые до сих пор актуальны. К сожалению, поработав в не мало числе не самых захудалых контор, я вижу, что уровень понимания этой темы у разработчиков крайне низкий. Может быть Ваша идея с копипастом мануалов не такая уж и трешовая ;)
Dayl
del
Уже нашел всю нужную информацию.