Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 09 December 2021 09:39:42


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Особенности использования функций 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

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.