DECODE и немного о NULL

 

Oracle/PLSQL функция DECODE имеет функциональные возможности оператора IF-THEN-ELSE.

Синтаксис Oracle/PLSQL функции DECODE:

DECODE( expression , search , result [, search , result]… [, default] )

expression — выражение для сравнения.

search — значение, которое сравнивается с expression.

result — значение, возвращаемое, если выражение expression совпало с искомым search.

default — необязательный. Если совпадений не найдено, функция DECODE вернет значение по умолчанию. Если значение по умолчанию не указано, то функция DECODE вернет NULL (если соответствий не найдено).

 

Использование DECODE

Условие	Использование DECODE	Альтернативный вариант
A<B	Decode(Sign(A-B), -1, 1, 0)
A<=B	Decode(Sign(A-B), 1, 0, 1)
A>B	Decode(Sign(A-B), 1, 1, 0)
A>=B	Decode(Sign(A-B), -1, 0, 1)
A=B	Decode(A, B, 1, 0)
A between B and C	Decode(Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1)
A is null	Decode(A, null, 1, 0)
A is not null	Decode(A, null, 0, 1)
A in (B1,B2, … Bn)	Decode(A, B1, 1, B2, 1, … Bn, 1, 0)
nor LogA	Decode(LogA, 0, 1, 0)	(1 - Sign(LogA))
LogA and LogB		LogA * LogB
LogA or LogB		LogA + LogB
LogA xor LogB	Decode(Sign(LogA), Sign(LogB), 0, 1)	Mod(Sign(LogA, Sign(LogB), 2)

Ограничение на количество аргументов, которые можно указать в одном операторе DECODE - 255. Это включая expression, search и result аргументы.

 

DECODE - единственная функция, которая считает два NULLа эквивалентными друг другу.

Псевдокод:

    select decode(column, null, 1, 0) from table    

Результат при column is null : 1

 

Немного о NULLКогда спорят о NULL, хочется подбросить ссылку на философские трактаты о пустоте и великом "ничто" и посоветовать познать дзен😁

Помедитируем и посмотрим внимательно на системные функции IS NULL и IS NOT NULL. Оператор IS как бы намекает, что не следует взвешивать, измерять, сравнивать, дробить на части любыми арифметическими и логическими операциями. Объект всего лишь является (IS) полным или пустым.

Если относиться к NULL к полному отсутствию чего-либо - пустоте, то легко выстроить логику любых операций NULL.

1. NULL не равно ни логическому значению FALSE, ни пустой строке, ни нулю, ничему вообще.

2. При сравнении NULL с любым значением будет получен результат NULL, а не FALSE  и не 0.

3. Более того, NULL != NULL.

4. NULL может присваиваться любому формату данных.

5. NULL может передаваться в процедуры и функции.

6. Любая операция сравнения с NULL или NULL=NULL дает результат UNKNOWN.

7. Агрегатные и аналитические функции игнорируют NULL в пользу допустимых значений массива, AVG  вообще не учтет строки. Только СOUNT(null всех значений) = 0.

8. Существуют специальные системные функции Is Null, Is Not Null, возвращающие TRUE/FALSE.

9. Существуют специальные преобразующие функции nvl, nvl2, nullif, coalesce.

10. NULL и пустая строка (varchar2 '') эквивалентны.

Примеры троичной логики Oracle: TRUE, FALSE и UNKNOWN.

 

Condition	Value of A	Evaluation
a IS NULL	10	FALSE
a IS NOT NULL	10	TRUE
a IS NULL	NULL	TRUE
a IS NOT NULL	NULL	FALSE
a = NULL	10	UNKNOWN
a != NULL	10	UNKNOWN
a = NULL	NULL	UNKNOWN
a != NULL	NULL	UNKNOWN
a = 10	NULL	UNKNOWN
a != 10	NULL	UNKNOWN

 

И не забываем кастовать при create table as select..., union, а лучше при любом DML.

 

CAST(NULL AS NUMBER) 
CAST(NULL AS VARCHAR2(10))  

<.. skip ..>

CAST(NULL AS TIMESTAMP)

 

Работает для всех форматов, кроме  BLOB и  CLOB  - для них  EMPTY_BLOB, EMPTY_CLOB . 

 

IN и EXISTS

 

Проверим IN и NULL

 

select 'true' from dual where null in (null);

select 'true' from dual where (1,null) in ((1,null));

select 'true' from dual where 1 in (1,null);

select 'true' from dual where (1,null) in ((1,2),(2,3));

 

Результат - ни одной строки.

 

 

Вернет строки запрос:

select 'true' from dual where 1 in (1,2) or 1 is null;

 

Проверим NOT IN и NULL

select 'true' from dual where null not in (null);

select 'true' from dual where (1,null) not in ((1,null));

select 'true' from dual where 1 not in (1,null)

select 'true' from dual where (1,null) not in ((1,2),(2,3));  

 

Результат тот же - ни одной строки.

 

Функциональность IN эквивалентна использованию предложения = ANY: 

 

select 'true' from dual where null = ANY (null);

select 'true' from dual where (null,null) = ANY ((null,null));

select 'true' from dual where (1,null) = ANY ((1,null));

 

Результат ожидаемо тот же - ни одной строки.

 

При использовании EXISTS SQL подсчитывает строки и игнорирует значение(я) в подзапросе - даже если возвращается NULL.

 

select 'true' from dual where exists (select null from dual);

select 'true' from dual where exists (select 0 from dual where null is null);

 

Результат - true.

 

IN и EXISTS логически одинаковы. Предложение IN сравнивает значения, возвращаемые подзапросом, и отфильтровывает строки во внешнем запросе; предложение EXISTS сравнивает значения и отфильтровывает строки внутри подзапроса. В случае NULL-значений результирующий набор строк одинаков - ни одной строки. 

 

select table_name from all_tables where table_name in (select null from all_objects where object_type = 'VIEW');

 

select table_name from all_tables where exists (select null from all_objects where object_type = 'VIEW' and object_name = table_name);

В случае NOT IN - результат тот же, но NOT EXISTS - результат будет обратным. 

 

select table_name from all_tables where table_name in (select null from all_objects where object_type = 'VIEW');

 

Результат NOT IN - ни одной строки. 

 

select table_name from all_tables where not exists (select null from all_objects where object_type = 'VIEW' and object_name = table_name);

 

Результат NOT EXISTS - все строки, значения которых есть в all_tables, потому что подзапрос не вернул ни одной строки (искали таблицы во вьюшках) и ничего не сравнивалось, а в случае IN были строки, только их значение было NULL.

 

Кроме DECODE существует еще одно исключение - это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.

 

Справочно, алгeбра выражений AND, OR и NOT

 

Значения	Результат
Алгебра выражения AND
TRUE AND TRUE	TRUE
FALSE AND TRUE	FALSE
TRUE AND FALSE	FALSE
FALSE AND FALSE	FALSE
NULL AND TRUE	NULL
TRUE AND NULL	NULL
FALSE AND NULL	FALSE
NULL AND FALSE	FALSE
NULL AND NULL	NULL
Алгебра выражения OR
TRUE OR TRUE	TRUE
FALSE OR TRUE	TRUE
TRUE OR FALSE	TRUE
FALSE OR FALSE	FALSE
NULL OR TRUE	TRUE
TRUE OR NULL	TRUE
FALSE OR NULL	NULL
NULL OR FALSE	NULL
NULL OR NULL	NULL
Алгебра выражения NOT
NOT TRUE	FALSE
NOT FALSE	TRUE
NOT NULL	NULL

Источники:

DECODE - Oracle Docs

Nulls - Oracle Docs

Заметка про NULL 

Как NULL-значения влияют на оценку предикатов IN и EXISTS