На главную страницу
Особенности использования функций COALESCE(), ISNULL(), NVL()
decipherinfosys (оригинал:
COALESCE(), ISNULL(), NVL())
Перевод Моисеенко С.И.
Функции ISNULL (MS SQL Server), NVL (Oracle) используются, чтобы заменить NULL-значения
некоторым другим значением, когда требуется как-то иначе обработать NULL-значение.
Функция COALESCE() - обобщенная форма функции NVL() или ISNULL() и входит в стандарт
ANSI, в то время как остальные представляют собой функции, реализованные в Oracle/SQL
Server. Эта функция имеется во всех ведущих РСУБД (Oracle, SQL Server и DB2). Основное
различие между COALESCE и ее конкурентами состоит в том, что COALESCE возвращает
первое не NULL-значение, и может иметь более 2-х выражений или значений в качестве
аргументов, в то время как ISNULL или NVL принимают только два аргумента. Первый
аргумент - это выражение или название столбца, а второй аргумент - выражение или
константа, которой мы хотим заменить первый аргумент, если он NULL. Давайте сначала
создадим таблицу и наполним ее некоторыми данными (в данном примере используется
синтаксис Oracle):
Использование функций Coalesce(), ISNULL() и NVL():
CREATE TABLE TEST
(
TEST_ID NUMBER(9),
TEST_COL1 NUMBER(9),
TEST_COL2 NUMBER(9),
TEST_NAME VARCHAR(30)
)
/
INSERT INTO TEST VALUES(1,1000,1101,'Oracle');
INSERT INTO TEST VALUES(2,2000,NULL,'SQLServer');
INSERT INTO TEST VALUES(3,NULL,3000,'DB2?);
INSERT INTO TEST VALUES(4,NULL,4000,'Sybase');
INSERT INTO TEST VALUES(5,NULL,NULL,'Informix');
INSERT INTO TEST VALUES(6,NULL,NULL,'MYSQL');
COMMIT;
Ниже - результат выполнения оператора select, использующего NVL. Как упоминалось
ранее, функция принимает только 2 аргумента. Если вы попытаетесь указать более 2-х
аргументов, Oracle вернет ORA-00909 (ошибка - неверное число аргументов).
SQL> SELECT TEST_NAME,NVL(TEST_COL1,TEST_COL2) FROM TEST;
TEST_NAME NVL(TEST_COL1,TEST_COL2)
---------- --------
Oracle 1000
SQLServer 2000
DB2 3000
Sybase 4000
Informix
MYSQL
Если Вы взгляните на вышеупомянутый результирующий набор, то заметите, что везде,
где значение TEST_COL1 есть NULL, запрос возвратил значение TEST_COL2. Для Informix
и MYSQL оба столбца имеют значения NULL, поэтому было возвращено NULL-значение.
Теперь давайте выполним подобный оператор с использованием COALESCE. С помощью COALESCE
мы хотим получить такие данные: если значение TEST_COL1 есть NULL, то должно быть
возвращено значение столбца TEST_COL2, а если значение TEST_COL2 также является
NULL, то возвращаться должна константа 9999.
SQL> SELECT TEST_NAME,COALESCE(TEST_COL1,TEST_COL2,9999) FROM TEST;
TEST_NAME COALESCE(TEST_COL1,TEST_COL2,9999)
---------- ------------
Oracle 1000
SQLServer 2000
DB2 3000
Sybase 4000
Informix 9999
MYSQL 9999
В вышеприведенном коде SQL мы использовали три аргумента в функции COALESCE, и она
возвратила первое не NULL значение, при условии, что хотя бы одно выражение или
столбец содержит не NULL-значение. Если все значения будут NULL, то и окончательным
результатом будет NULL. Вышеприведенный оператор SQL эквивалентен следующему выражению
CASE.
SELECT TEST_NAME,
CASE WHEN TEST_COL1 IS NOT NULL THEN TEST_COL1
ELSE COALESCE (TEST_COL2,9999)
END as first_Non_Null
FROM TEST
После выполнения этот запрос также возвратит то же самое значение, что и оператор
с COALESCE. Последний скрывает сложность выражения CASE, когда имеется более 3-х
аргументов. Следует помнить один момент, а именно, когда используется COALESCE,
все выражения должны иметь один и тот же тип данных или же они должны быть неявно
конвертируемыми к одному и тому же типу данных, в противном случае возникает ошибка.
Пожалуйста, не забывайте что, если Вы используете функции на индексируемых столбцах
в критериях отбора или в условиях соединения, то индекс использоваться
не будет. Есть способы заставить его работать (использование
FBI в Oracle,
вычисляемые столбцы в SQL Server или столбцы генерации выражения
в DB2 LUW), если Вам действительно необходимо это сделать
Другие различия, о которых Вы должны знать:
- Другая важная вещь, когда Вы используете ISNULL(), заключается в том, что эта
функция оценивает первое значение, и значение второго параметра автоматически ограничивается
этой длиной; COALESCE() не имеет этого ограничения. Вот пример (синтаксис T-SQL):
declare @t varchar(1)
set @t = NULL
select isnull (@t, 'ABCD')
select coalesce (@t, 'ABCD')
Функция ISNULL() возвращает 'A', в то время как coalesce вернет 'ABCD'. Нужно иметь
это в виду, иначе вы получите неожиданные результаты.
- И точно так же, как в посте
UNION/UNION ALL, в котором мы рассуждали о неявных преобразованиях типа
данных, приводящих к проблемам, неявные преобразования типа данных могут создать
головную боль и здесь. В случае функции COALESCE(), если значения имеют различные
типы данных, Вы можете получить как ошибки, так и неверные результаты. Пример:
select coalesce('test', 100)
В результате получаем ошибку: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'test' to data type int.
(преобразование значение varchar 'test' к тапу данных int вызывает ошибку)
Аналогично:
select coalesce(12345678910, current_timestamp)
даст:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(ошибка арифметического переполнения при преобразовании выражения к типу данных
datetime)
Такой оператор:
select coalesce(100, current_timestamp)
вернет неверные результаты в результате неявного преобразования типа. Вы получите:
"1900-04-11 00:00:00.000" вместо 100.
Итак, вы должны знать, что при использовании COALESCE(), все значения/выражения
должны быть одного и того же типа или должны быть неявно преобразовываемыми к одному
и тому же типу данных.
- Кроме того, если Вы имеете оператор select в качестве аргумента этих функций,
то возможны проблемы с производительностью, о которых вы должны знать, - имеется
ветка обсуждения в одной из групп, где вы можете больше об этом
прочитать.
02-08-2007
На главную страницу