Использование выражений 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 плохой оптимизатор, которому нужна дополнительная поддержка, используйте избыточные предикаты.