Oracle sql не равно null
This Oracle tutorial explains how to use the Oracle IS NOT NULL condition with syntax and examples.
Операции над множествами
Иногда бывает необходимо, чтобы запрос предусматривал объединение результатов из нескольких SQL-операторов. Такой запрос называется составным. Операции над множествами облегчают написание и выполнение составных запросов. Наиболее важные из этих операций перечислены ниже.
NULL в OLAP
Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID , у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPING ов, в этой заметке она точно лишняя.
В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
Summary: in this tutorial, you will learn how to use the Oracle IS NULL and IS NOT NULL operators to check if a value in a column or an expression is NULL or not.
Операции сравнения
Операции сравнения позволяют сравнивать одно определенное значение столбца с несколькими другими значениями. Ниже перечислены основные операции сравнения.
- BETWEEN . Позволяет проверять, находится ли значение между двумя другими значениями.
- IN . Позволяет проверять, присутствует ли значение в списке значений.
- LIKE . Позволяет проверять, соответствует ли значение определенному образцу, как показано ниже:
Example - With UPDATE Statement
Here is an example of how to use the Oracle IS NOT NULL condition in an UPDATE statement:
This Oracle IS NOT NULL example will update records in the customers table where the customer_name does not contain a null value.
Параметры или аргументы
Introduction to the Oracle IS NULL operator
In the database world, NULL is special. It is a marker for missing information or the information is not applicable.
NULL is special in the sense that it is not a value like a number, character string, or datetime, therefore, you cannot compare it with any other values like zero (0) or an empty string (”). Generally speaking, NULL is even not equal to NULL.
Let’s see the orders table from the sample database.
The salesman_id column stores the salesman id of the salesman who is in charge of the sales order.
The following SELECT statement attempts to return all sales orders which do not have a responsible salesman:
It returns an empty row.
The query uses the comparison operator ( = ) to compare the values from the salesman_id column with NULL , which is not correct.
To check if a value is NULL or not, you should use the IS NULL operator as follows:
The IS NULL operator returns true if the expression or column is NULL . Otherwise, it returns false.
The following query returns all sales orders that do not have a responsible salesman:
Here is the partial output of the query:
Syntax
The syntax for the IS NOT NULL condition in Oracle/PLSQL is:
Parameters or Arguments
- If expression is NOT a NULL value, the condition evaluates to TRUE.
- If expression is a NULL value, the condition evaluates to FALSE.
Описание
Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия IS NOT NULL в SQL:
Пример - использование IS NOT NULL с оператором DELETE
Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.
Специальное значение NULL означает отсутствие данных, констатацию того факта, что значение неизвестно. По умолчанию это значение могут принимать столбцы и переменные любых типов, если только на них не наложено ограничение NOT NULL . Также, СУБД автоматически добавляет ограничение NOT NULL к столбцам, включенным в первичный ключ таблицы.
Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: = , < , >, like … Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ . Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО .
Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.
Для удобства сделаем процедуру, печатающую состояние булевого параметра:
Привычные операторы сравнения пасуют перед NULLом:
Существуют специальные операторы IS NULL и IS NOT NULL , которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.
Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:
Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE , которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.
DECODE идёт против системы:
Пример с составными индексами находится в параграфе про индексы.
Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ . Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО , то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО . Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.
В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :
Отрицание неизвестности даёт неизвестность:
Оператор OR :
Оператор AND :
Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL
Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.
Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :
Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО . Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:
Попробуем теперь с NOT IN :
Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :
Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.
Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.
NULL и пустая строка эквивалентны:
непременный атрибут жёсткого спора:
Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF , обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF . Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.
Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО , так как, по сути, эквивалентно val = NULL .
Длина пустой строки не определена:
Сравнение с пустой строкой невозможно:
Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…
Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом '' (пустая строка), но не позволяют сохранить элемент с индексом NULL:
Использовать такие финты ушами на практике не стоит. Во избежание проблем лучше усвоить правило из доки: пустая строка и NULL в оракле неразличимы.
Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.
Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.
Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM , которой необходимо просуммировать ряд (1, 3, null, 2) . Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
1 + 3 = 4; 4 + null = null; null + 2 = null .
Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…
Таблица с данными. Используется ниже много раз:
Пустые значения игнорируются агрегатами:
Функция подсчёта количества строк COUNT , если используется в виде COUNT(*) или COUNT(константа) , будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение) , то пустые значения будут игнорироваться.
с константой:
С выражением:
Также, следует быть осторожным с функциями вроде AVG . Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3 , а не (1+3+2)/4 . Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL :
Агрегатные функции возвращают состояние НЕИЗВЕСТНО , если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение) . Они в перечисленных выше случаях вернут ноль.
Набор данных только из NULLов:
Пустой набор данных:
Example - With DELETE Statement
Here is an example of how to use the Oracle IS NOT NULL condition in a DELETE statement:
This Oracle IS NOT NULL example will delete all records from the customers table where the status does not contain a null value.
Description
The Oracle IS NOT NULL condition is used to test for a NOT NULL value. You can use the Oracle IS NOT NULL condition in either a SQL statement or in a block of PLSQL code.
Пример - использование IS NOT NULL с оператором UPDATE
Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.
В этом примере у нас есть таблица customer со следующими данными:
Введите следующий запрос UPDATE:
Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:
Вот результаты, которые вы должны получить:
Пример - использование IS NOT NULL с оператором SELECT
При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.
В этом примере у нас есть таблица products со следующими данными:
product_id | product_name | category_id |
---|---|---|
1 | Pear | 50 |
2 | Banana | 50 |
3 | Orange | 50 |
4 | Apple | 50 |
5 | Bread | 75 |
6 | Sliced Ham | 25 |
7 | Kleenex | NULL |
Введите следующий SQL оператор:
Будет выбрано 6 записей. Вот результаты, которые вы должны получить:
product_id | product_name | category_id |
---|---|---|
1 | Pear | 50 |
2 | Banana | 50 |
3 | Orange | 50 |
4 | Apple | 50 |
5 | Bread | 75 |
6 | Sliced Ham | 25 |
В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.
Oracle IS NOT NULL operator
To negate the IS NULL operator, you use the IS NOT NULL operator as follows:
The operator IS NOT NULL returns true if the expression or value in the column is not null. Otherwise, it returns false.
For example, the following example returns all sales orders which have a responsible salesman:
This picture illustrates the partial output:
In this tutorial, you have learned how to use the Oracle IS NULL and IS NOT NULL to check if an expression or value in a column is null or not.
В SQL поддерживается ряд операций для решения различных задач, наподобие сравнения значений столбцов и проведения логических вычислений. В этом статье блога будут кратко описаны наиболее важные из этих операций — операции сравнения, логические операции и операции над
Логические операции
Логические операции, также называемые булевскими, позволяют логическим образом сравнивать два или более значений. Главные логические операции выглядят так: AND (И), OR (ИЛИ), NOT (НЕ), GE (БОЛЬШЕ ИЛИ РАВНО) и LE (МЕНЬШЕ ИЛИ РАВНО). Ниже приведен пример, иллюстрирующий применение некоторых из этих операций:
В случае использования сразу нескольких операций внутри одного оператора необходимо помнить об их старшинстве. Oracle всегда вычисляет арифметические операции вроде умножения, деления, сложения и вычитания перед вычислением условий. Ниже показан порядок старшинства операций в Oracle, причем самая важная операция идет первой:
Example - With INSERT Statement
Here is an example of how to use the Oracle IS NOT NULL condition in an INSERT statement:
This Oracle IS NOT NULL example will insert records into the suppliers table where the account_no does not contain a null value in the customers table.
Example - Using PLSQL Code
You can use the Oracle IS NOT NULL condition in PLSQL to check if a value is not null.
If Lvalue does not contain a null value, the "IF" expression will evaluate to TRUE.
В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.
Example - With SELECT Statement
Here is an example of how to use the Oracle IS NOT NULL condition in a SELECT statement:
This Oracle IS NOT NULL example will return all records from the customers table where the customer_name does not contain a null value.
Читайте также: