Использование выражений CASE

Выражение CASE — это именно выражение, а не управляющий оператор, поскольку оно возвращает величину определенного типа данных. Поскольку язык SQL является декларативным, в нем нет порядка выполнения операторов, которым можно было бы управлять, как это делается оператором CASE в других языках. Число новичков, которые не понимают разницы между выражением и оператором, просто пугает. Сама эта идея пришла из языка программирования ADA. Вот как выглядит формальный BNF-син-таксис для оператора CASE:

Спецификация case >::= <простой case> | <case с поиском>

<простой case>::=

CASE <операнд>

<простая конструкция when>...

[<конструкция else>]

END

<case с поиском>::=

CASE

конструкция when с поиском>...

[<конструкция else>]

END

<простая конструкция when>:*= WHEN <операнд> THEN <результат>

<конструкция when с поиском>: = WHEN <условие поиска> THEN <результат>

<конструкция else >::= ELSE <результат>

<операнд>.:= <значение>

<результат>::= <результирующее выражение> | NULL

<результирующее выражение>::= <значение>

Выражение CASE с поиском
Выражение CASE с поиском, возможно, одна из наиболее часто используемых версий CASE. В ней конструкции WHEN и THEN выполняются слева направо. Первая конструкция WHEN, в которой проверяется истинность условия, возвращает величину, заданную в конструкции THEN, причем вы вольны вставлять выражения CASE друг в друга. Если в выражении CASE не содержится конструкции ELSE, СУБД по умолчанию вставит конструкцию “ELSE NULL”. Чтобы возвратить NULL в конструкции THEN, используйте выражение CAST (NULL AS <тип данных>). Я всегда рекомендую явно определять конструкцию ELSE. Вы сможете изменить ее позднее, когда найдете что-нибудь определенное для вывода.

Простое выражение CASE
Простое выражение CASE определяется как выражение CASE с поиском, в котором все конструкции WHEN являются проверками равенства операнду CASE. Например:

CASE iso_sex_code

WHEN 0 THEN 'Unknown'

WHEN 1 THEN 'Male'

WHEN 2 THEN 'Female'

WHEN 9 THEN N/A'

ELSE NULL

END

можно записать таю

CASE

WHEN iso_sex_code = 0 THEN 'Unknown'

WHEN iso_sex_code = 1 THEN 'Male'

WHEN iso_sex_code = 2 THEN 'Female'

WHEN iso_sex_code = 9 THEN 'N/A'

ELSE NULL

END

Однако в этом определении есть одна тонкость. Выражение:

CASE foo

WHEN 1 THEN 'bar'

WHEN NULL THEN 'no bar'

END

преобразуется к виду:

CASE

WHEN foo = 1 THEN 'bar'

WHEN foo = NULL THEN 'no bar' -- ошибка

ELSE NULL

END

Второй конструкции WHEN всегда сопоставлено значение UNKNOWN. По возможности используйте простое выражение CASE.

Другие виды выражения CASE
В стандартном SQL определены другие функции с использованием выражения CASE, которые делают язык немного более компактным и простым в использовании. Например, функция COALESCE() для одного или двух выражений может быть определена следующим образом:
1. COALESCE (<value ехр ^#1>) эквивалентно (<value exp ^#1>).
2. COALESCE (<value exp ^#1>, <value exp ^#2>) эквивалентно:

CASE

WHEN <value exp #1> IS NOT NULL

THEN <value exp #1>

ELSE <value exp #2>

END

Мы можем ввести рекурсивное определение для n выражений, где n больше либо равно 3, следующим образом: COALESCE (<value exp ^#1>, <value exp ^#2>,..., n) эквивалентно:

CASE

WHEN <value exp #1> IS NOT NULL

THEN <value exp #1>

ELSE COALESCE (<value exp #2>,..., n)

END

Аналогично, NULLIF (<value exp ^#1>, <value exp ^#2>) эквивалентно:

CASE

WHEN <value exp #1> = <value exp #2>

THEN NULL

ELSE <value exp #1>

END

Используйте наиболее компактную форму подобных CASE-выражеиий и не пытайтесь подробно расписать их компоненты.

Избегайте лишних выражений

Обоснование
Современные ядра SQL, как правило, замечательно сообразительны. Но это не всегда было так, поэтому старые SQL-программисты иногда добавляют в конструкцию WHERE лишние предикаты. Например, если в таблице Foobar ни один из столбцов не может принимать значение NULL, то в выражении:

SELECT a, b, с

FROM Foobar

WHERE a = b

AND b = с

AND с = а;

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

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