Форум программистов, компьютерный форум, киберфорум
Наши страницы

Базы данных

Войти
Регистрация
Восстановить пароль
 
Рейтинг: Рейтинг темы: голосов - 515, средняя оценка - 4.64
БурундукЪ
9554 / 2553 / 67
Регистрация: 17.02.2009
Сообщений: 10,364
#1

Особенности использования функций COALESCE(), ISNULL(), NVL() - Базы данных

09.11.2009, 00:20. Просмотров 69135. Ответов 0
Метки нет (Все метки)

Функции 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():

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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.

SQL
1
2
3
4
5
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):

T-SQL
1
2
3
4
declare @t varchar(1)
set @t = NULL
select isnull (@t, 'ABCD')
select coalesce (@t, 'ABCD')
Функция ISNULL() возвращает 'A', в то время как coalesce вернет 'ABCD'. Нужно иметь это в виду, иначе вы получите неожиданные результаты.

- И точно так же, как в посте UNION/UNION ALL, в котором мы рассуждали о неявных преобразованиях типа данных, приводящих к проблемам, неявные преобразования типа данных могут создать головную боль и здесь. В случае функции COALESCE(), если значения имеют различные типы данных, Вы можете получить как ошибки, так и неверные результаты. Пример:

T-SQL
1
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 вызывает ошибку)

Аналогично:

T-SQL
1
select coalesce(12345678910, current_timestamp)
даст:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
(ошибка арифметического переполнения при преобразовании выражения к типу данных datetime)

Такой оператор:

T-SQL
1
select coalesce(100, current_timestamp)
вернет неверные результаты в результате неявного преобразования типа. Вы получите: "1900-04-11 00:00:00.000" вместо 100.

Итак, вы должны знать, что при использовании COALESCE(), все значения/выражения должны быть одного и того же типа или должны быть неявно преобразовываемыми к одному и тому же типу данных.

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

Перевод Моисеенко С.И.
11
Надоела реклама? Зарегистрируйтесь и она исчезнет полностью.
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
09.11.2009, 00:20
Я подобрал для вас темы с готовыми решениями и ответами на вопрос Особенности использования функций COALESCE(), ISNULL(), NVL() (Базы данных):

COALESCE - SQL Server
ВЫшлите плс пример использования этой инструкции. Заранее благодарен.

Не работает ISNULL - SQL Server
Стоит MS Windows Server 2008 R2. Объединяются несколько таблиц с помощью LEFT OUTHER JOIN. При этом могут появиться значения NULL. ...

Применить ISNULL к запросу - SQL Server
Добрый день. У меня есть запрос, который иногда выдаёт пустое значение. Собсно, хочу применить ISNULL, но пока не соображу как... ...

ms sql сервер функция isnull - SQL Server
здравствуйте форумчане. есть запрос SELECT lekarstva.idlek, lekarstva.idvid,lekarstva.edizm,((select sum...

NVL+амперсанд - Oracle
Здравствуйте. Подскажите пожалуйста, как синтаксически правильно прописать: nvl(closed_code, 'OPEN')= 'OPEN' чтобы "closed_code"...

Выполнение рекурсивного запроса без использования рекурсивных функций - Oracle
Всем привет. Имеется следующее задание: Для каждого сотрудника выбрать всех его начальников по иерархии. Вывести поля: код...

0
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
09.11.2009, 00:20
Привет! Вот еще темы с ответами:

Как в VB записывается конструкция isnull(a,b) - MS Access
Ну в принципе вопрос понятен? если нет поясняю! В транзакт-sql есть такая функция, isnull(a,b) т.е если а нулл то вернет b иначе а. Ну...

Особенности использования операторов "LIKE" и "=" - MS Access
У меня есть поле, в котором есть NULL значения. Если я использую = в условии отбора, то Access ругается о несоответствии типов. ...

ISNULL против <=>NULL - MySQL
Есть специфический оператор который возвращает только 0 или 1, не NULL mysql&gt; SELECT 1&lt;=&gt;NULL;//0 mysql&gt; SELECT 0&lt;=&gt;NULL;//0 ...

Особенности использования const_cast - C++
Почему не работает код: 1. const int* const pInt = new int(0); const_cast&lt;int*&gt;(pInt) = new int(4); 2. void...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
1
Закрытая тема Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Рейтинг@Mail.ru