Oracle не равно пусто
Я ищу способ сравнить строку с нулевой строкой оптимальным способом.
Это игнорируется, если str1 имеет значение NULL.
Я знаю, что могу установить нулевую строку перед сравнением, чтобы она работала
И это работает, но я хотел посмотреть, есть ли лучший способ обрабатывать сравнения нулевых строк.
Может ли str1 быть нулевым? Что вы хотите, чтобы результат был, если обе строки равны нулю - следует ли их считать равными? В зависимости от вашего ответа, lnnvl или decode (но не так, как вы пытались) может быть самым простым/наиболее эффективным ответом. Оба являются собственностью Oracle Database (не доступны в SQL Server, MySQL и т. д.) — это нормально?
Обратите также внимание, что вы используете DECODE синтаксически неправильно - вы тестировали этот код перед его публикацией? Если да, то это привело к ошибке - возможно, об этом стоило упомянуть. Если вы не пробовали это перед публикацией - почему бы и нет?
4 ответа
Вы не сказали, может ли строка, которую вы должны сравнить ( str1 ), быть нулевой; ни каким должен быть результат сравнения, если какая-либо строка равна нулю.
Очень редко (если вообще когда-либо) имеет смысл считать ненулевую строку равной нулю. Иногда имеет смысл рассматривать две пустые строки как равные (если вы думаете о них как о «пустых строках» — по идее, Oracle не делает различий, предписанных стандартом SQL, между концепцией SQL SQL с одной стороны). с другой стороны, и концепция пустой строки).
Возвращает TRUE, если обе строки не равны нулю и равны; он возвращает FALSE, если обе строки не равны нулю и различны; и возвращает UNKNOWN, если хотя бы одна строка имеет значение null.
оператор LNNVL (который принимает условие в качестве аргумента) возвращает ЛОЖЬ, если его аргумент равен ИСТИНА, и возвращает ИСТИНА, если его аргумент либо ЛОЖЬ, либо НЕИЗВЕСТНО. Итак, условие
Возвращает ЛОЖЬ, когда строки не равны нулю и равны, и ИСТИНА во всех остальных случаях. Если вы должны рассматривать две пустые строки как «не равные», то это условие вам нужно в вашем коде.
Если, с другой стороны, вы должны рассматривать две пустые строки как равные, вы можете использовать
В приведенном выше случае DECODE возвращает 0, если str1 и str2 не равны нулю и равны, и также, когда они оба равны нулю. (Вот почему именно в такой ситуации часто используется DECODE). Он возвращает значение по умолчанию (которое я не указал, поэтому функция вернет значение по умолчанию «по умолчанию», равное NULL) в остальных случаях. DECODE вернет не-NULL, когда str1 и str2 "одинаковы" - либо как равные ненулевые строки, либо как оба null.
Конечно, условие DECODE также можно записать в виде
Чтобы сделать его более явным. Некоторые люди (не я!) могут предпочесть это именно так.
Что, если str2 буквально равно 'Empty' ? nvl(str1, 'Empty'); (это то, что вы подразумеваете под DECODE(str1, 'Empty') , я предполагаю ( decode() требуется как минимум три параметра, чтобы исходное выражение не сработало)) даст вам ложноотрицательный результат. Просто используйте IS NULL .
Нет, decode(str1, 'Empty') не даст ложноотрицательного результата. Это выдаст ошибку, так как это выражение неверно ( decode должно иметь не менее трех аргументов).
Специальное значение 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ов:
Пустой набор данных:
Nulls in SQL Functions
For information on null handling in SQL functions, see "Nulls in SQL Functions".
Функция NULL в MySQL
В системе MySQL есть ряд функций, позволяющих результативно работать с NULL. Это IFNULL, NULLIF и COALESCE.
- IFNULL может принять два параметра: возвращает первый аргумент, если он не является NULL, в обратном случае — возвращает второй аргумент.
- NULLIF также может принять два аргумента: если они равны, то функция возвращает NULL, в обратном случае — возвращает первый аргумент. Эта функция также будет эффективна, если в вашей таблице в столбце есть пустые строки со значением NULL.
- COALESCE может принимать список аргументов и возвращать первый аргумент не-NULL. Например, эту функцию можно применять для базы контактных данных с потенциальной возможностью в зависимости от важности информации в порядке Телефон — Электронная почта — N/A.
Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.
Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!
Скачивайте и используйте уже сегодня:
Топ-30 самых востребованных и высокооплачиваемых профессий 2022
Подборка 50+ ресурсов об IT-сфере
ТОП сервисов и приложений, на которые следует перейти уже сегодня
3,7 MB
Логические операции и NULL
Для логических операторов AND и OR есть свои особенности при работе со значением NULL. Краткое руководство рассмотрим на примере.
Как правило, НЕИЗВЕСТНО обрабатывается так же, как и состояние ЛОЖЬ. Если выбрать из таблицы строки и вычисление условия X=NULL в предложении WHERE дало результат НЕИЗВЕСТНО, то ни одной строки не будет получено. Но есть и различия: выражение НЕ(ЛОЖЬ) вернет истину, а НЕ(ИЗВЕСТНО) вернет НЕИЗВЕСТНО.
Чаще всего с неизвестным результатом работают как с ЛОЖЬЮ:
select 1 from dual where dummy = null; — запрос не вернёт записей
При отрицании неизвестности результатом будет НЕИЗВЕСТНО:
exec test_bool( not(null = null)); — UNKNOWN
exec test_bool( not(null != null) ); — UNKNOWN
exec test_bool( not(null = ‘a’) ); — UNKNOWN
exec test_bool( not(null != ‘a’) ); — UNKNOWN
exec test_bool(null or true); — TRUE
exec test_bool(null or false); — UNKNOWN
exec test_bool(null or null); — UNKNOWN
exec test_bool(null and true); — UNKNOWN
exec test_bool(null and false); — FALSE
exec test_bool(null and null); — UNKNOWN
Значение NULL в MySQL
Результат при сравнении NULLов, в зависимости от операции SQL, часто будет иметь значение NULL. Предположим, что А НЕДЕЙСТВИТЕЛЕН:
Арифметические операторы
- A + B = NULL
- A – B = NULL
- A * B = NULL
- A/B = NULL
Получите подборку бесплатно (pdf 2,5 mb)
Операторы сравнения
- A = B = NULL
- A! = B = NULL
- A> B = NULL
- A!
Эти случаи — лишь часть примеров операторов, возвращающих значение NULL при равенстве NULL одного из операндов. На практике встречаются куда более сложные запросы, чья обработка затруднена количеством значений NULL. Главное, нужно понимать и планировать итоги работы с базой данных, в которой вы разрешаете значение NULL.
Значение NULL и пустая строка в СУБД
Oracle отличается от стандартов ANSI SQL в определении NULLов: он проводит знак равенства между NULL и пустой строкой. Эта особенность программы рождает много споров, хотя Oracle и заявляет, что, возможно, в будущих релизах будет изменен подход в обработке пустой строки, как NULL. Но в реальности проведение таких изменений сомнительно, так как под эту СУБД написано неимоверное количество кода.
Мы вместе с экспертами по построению карьеры подготовили документы, которые помогут не ошибиться с выбором и определить, какая профессия в IT подходит именно вам.
Благодаря этим гайдам 76% наших студентов смогли найти востребованную профессию своей мечты!
Скоро мы уберем их из открытого доступа, успейте скачать бесплатно:
Женщины в IT: мифы и перспективы в карьере
Как прокачать свою технику речи
100 тыс. руб за 100 дней с новой профессией
Список из 6 востребованных профессий с заработком от 100 тыс. руб
Критические ошибки, которые могут разрушить карьеру
Собрали 7 типичных ошибок, четвертую должен знать каждый!
Гайд по профессиям в IT
5 профессий с данными о навыках и средней заработной плате
4,7 MB
exec test_bool( » is null ); — TRUE
Если попытаться найти причину, почему вообще пустую строку стали считать эквивалентной NULL, то ответ можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Табличные строки Oracle хранит в структуре, представляющей собой заголовок и следующими за ним столбцы с данными.
Каждый столбец, в свою очередь, состоит из 2-х полей: длина данных в столбце (1 или 3 байта) и сами данные. При нулевой длине varchar2 в поле с данными нечего вносить, так как оно не занимает ни байта. В поле же, где указывается длина, вносится специальное значение 0xFF, что и означает отсутствие данных.
NULL Oracle представляет аналогично, то есть отсутствует поле с данными, а в поле длины данных вносится 0xFF. Так как изначально разработчики Oracle не разделяли эти два состояния, то и сейчас принцип внесения данных не изменился.
Значение NULL
Понятие «пустая строка» допустимо толковать как абсолютное отсутствие значения, так как ее длина равна нулю. NULL же, в свою очередь, имеет длину неопределенного значения. Поэтому выражение length (») возвращает NULL, а не ожидаемый ноль.
Еще одна причина, по которой нельзя сравнивать NULL с пустой строкой: выражение val = » вернёт состояние НЕИЗВЕСТНО, так как, по сути, идентично val=NULL.
Неопределенная длина пустой строки:
select length(») from dual; — NULL
Сравнение с пустой строкой невозможно:
exec test_bool( ‘a’ != » ); — UNKNOWN
Критика такого подхода Oracle к значениям NULL и пустой строки, основывается на том, что не всегда пустая строка может означать неизвестность. Например, когда менеджер-продавец вносит данные в карточку клиента, то в поле «Контактный номер» он может указать конкретный номер; также он может указать, что номер неизвестен (NULL); но еще он может указать, что номера как такового нет (пустая строка).
С методом хранения пустых строк, предлагаемым Oracle, последний случай будет очень затруднительно осуществить. Если смотреть на этот довод критики через призму семантики, то звучит он очень убедительно. Но с другой стороны, каким образом менеджер сможет внести в поле «Контакты» пустую строку, и как в будущем он сможет отличить ее от «номер неизвестен» (NULL)?
Значение NULL и НЕ NULL
IS NULL и IS NOT NULL – специально созданные операторы, которые осуществляют сравнение имеющихся NULLов. IS NULL возвращает истину, если операнда является NULLом. Соответственно, если операнд не является NULLом, то значение будет ложным.
select case when null is null then ‘YES’ else ‘NO’ end from dual; — YES
select case when ‘a’ is null then ‘YES’ else ‘NO’ end from dual; — NO
IS NOT NULL имеет обратный принцип: значение будет истинным, если операнд не является NULLом, и ложным, если он таковым является.
select case when ‘a’ is NOT null then ‘YES’ else ‘NO’ end from dual; — YES
select case when null is NOT null then ‘YES’ else ‘NO’ end from dual; — NO
Учтите, что когда речь идет об отсутствующих значениях, есть особые случаи их сравнения:
- DECODE — принимает два NULLа за равные значения;
- составные индексы — в случае, когда у двух ключей есть пустые поля, но заполненные поля при этом равны между собой, то Oracle воспримет эти ключи, как равные.
Вот так проявляет себя DECODE:
select decode( null
, null, ‘EMPTY’ — это условие будет истинным
Пример значения NULL
Итак, что вы должны знать о значении NULL? Давайте разбираться.
Представьте себе письменный стол, на котором лежат канцелярские принадлежности: 6 шариковых ручек и 2 простых карандаша. Также известно, что в ящике стола должны быть фломастеры. Но вот сколько их и есть ли они вообще — данных нет. Если нам нужно составить таблицу инвентаризации с вводом значения NULL, то выглядеть она будет так:
InventoryID | Item | Количество |
1 | ручки | 6 |
2 | карандаши | 2 |
3 | фломастеры | NULL |
Как вы понимаете, принимать за «0» количество фломастеров в данном случае было бы неверным, так как подобная запись показывала бы, что фломастеров нет вообще. Но точные данные об их количестве отсутствуют, поэтому может оказаться, что несколько штук все же есть.
NULL в OLAP
Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID , у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPING ов, в этой заметке она точно лишняя.
В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
If a column in a row has no value, then the column is said to be null , or to contain null. Nulls can appear in columns of any data type that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.
Oracle Database treats a character value with a length of zero as null. However, do not use null to represent a numeric value of zero, because they are not equivalent.
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.
Nulls with Comparison Conditions
To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL . If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN . Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Refer to DECODE for syntax and additional information.
Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.
Nulls in Conditions
A condition that evaluates to UNKNOWN acts almost like FALSE . For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN . Thus, NOT FALSE evaluates to TRUE , but NOT UNKNOWN evaluates to UNKNOWN .
Table 2-20 shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.
Если вы далеки от работы с базами данных, для вас может быть открытием, что ноль – это не значение NULL, хотя, признаем, они созвучны. Кроме того, NULL не является значением пустой строки, хотя можно найти поле, содержащее данные любого типа.
NULL можно представить как значение для представления неизвестного фрагмента данных (обратите внимание: не нулевого, хотя поле при этом выглядит пустым). А еще он не равен ничему, даже другому NULL. И сегодня мы поговорим об этом загадочном (на первый взгляд) значении NULL более подробно.
Отличия между null и undefined
Можно сказать, что NULL – это такое значение, которое является определенным для отсутствующего объекта. UNDEFINED же означает именно неопределенность. Например:
var element;
// значение переменной element до её инициализации не определённо: undefined
element = document.getElementById(‘not-exists’);
// здесь при попытке получения несуществующего элемента, метод getElementById возвращает null
// переменная element теперь инициализирована значением null, её значение определено
Осуществляя проверку на NULL или UNDEFINED, нужно помнить о разнице в операторах равенства (==) и идентичности (===): с первым оператором производится преобразование типов.
typeof null // object (не «null» из соображений обратной совместимости)
typeof undefined // undefined
null === undefined // false
null == undefined // true
Это все то, что вы должны знать о значении NULL. Обрастая опытом и применяя некоторые уловки для избежания NullPointerException, вы научитесь делать безопасный код. Главным образом неразбериха возникает из-за того, что NULL может трактоваться как пустое значение или как неидентифицированное.
Я создал следующий dbfiddle, в котором вы можете видеть, что поле <> '' исключает случай, когда поле имеет значение null.
Я был удивлен, увидев это, поскольку в большинстве языков NULL и '' (пустая строка) обрабатываются по-разному.
Приведенный выше пример взят из базы данных PostgreSQL. Будет ли то же самое верно для базы данных Oracle? При каких обстоятельствах PostgreSQL и Oracle рассматривают NULL и '' (пустую строку) как одно и то же?
Никогда. Вы можете быть введены в заблуждение некоторыми программами (браузерами баз данных), которые отображают нули как пустые строки.
NULL и '' ARE по- разному обрабатываются в Postgres.
возвращается NULL если prd_chld_typ_cd есть NULL . Это типичное поведение для большинства операций, связанных с NULL . Вы легко обнаружите, что:
ведет себя точно так же, когда этот столбец NULL .
Для любого другого значения выражение возвращает значение true или false. Вы легко увидите это, если вернете значение:
Происходит то, что NULL и false обрабатываются в фильтре одинаково WHERE — оба отфильтровывают строку.
Напротив, NULL и '' являются синонимами в Oracle. Но оказывается, что
Оба ведут себя одинаково, когда prd_chld_typ_cd is NULL . Увы, но в Oracle всегда возвращаются оба значения, NULL потому что '' это эквивалентно NULL .
Я не знаю о postgres, но у оракула есть некоторые особенности в отношении пустых строк, о которых вам нужно знать, если вы планируете поддерживать его в своих приложениях: 1. в Oracle пустая строка ВСЕГДА считается NULL: если вы вставляете пустую строку строка, вы прочитаете NULL. В оракуле пустая строка просто не существует: это просто "сахар синтаксиса языка", если вы можете написать '' вместо NULL при работе со значениями varchar. 2. В Oracle это '' равно null будет оцениваться как TRUE. пустая строка является DE FACTO null 3. В оракуле любое сравнение, включающее значение NULL, ВСЕГДА оценивается как FALSE. это означает, что единственными операторами, которые вы можете применять к значениям NULL и которые могут возвращать TRUE, являются «является нулевым» и «не является нулевым».
Следствием вышеизложенного является то, что в Oracle ВСЕ следующие выражения будут оцениваться как FALSE, независимо от фактического значения myvar:
даже они всегда оцениваются как ложные
поэтому следующее обновление ничего не обновит:
Обновите mytab, установив myfield = 'X', где myfield = ''
как я уже сказал, вы можете использовать только операторы «является нулевым» и «не является нулевым». поэтому вы должны написать это как
обновить mytab установить myfield = 'X', где myfield имеет значение null.
является старой реализацией конструкции case sql. oracle обрабатывает NULL (а также пустые строки) как сопоставимые значения в этой функции. поэтому вы можете использовать NULL для и он будет соответствовать нулевым значениям (и пустым строкам). Это единственный случай, когда это происходит.
если вы попытаетесь сделать это с помощью стандартной конструкции SQL Case, это не сработает: oracle не реплицировал там ошибку.
P. S: извините, если ответ будет выглядеть странно отформатированным. Пишу с мобильного.
Операторы IN и NOT IN для значения NULL
Чтобы понять взаимодействие этих операторов с NULLом, рассмотрим пример.
Создадим таблицу Т, состоящую из одного числового столбца А и строками: 1, 2, 3 и NULL.
create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));
Затем выполним трассировку запроса (учтите, что для этого нужно обладать ролью PLUSTRACE).
От трассировки в листингах оставлена часть filter, чтобы показать преобразование указанных в запросе условий.
set autotrace on
Теперь, после подготовительных действий, попробуем выбрать те записи, которые будут соответствовать набору (1, 2, NULL).
select * from t where a in(1,2,null); — вернёт [1,2]
— filter(«A»=1 OR «A»=2 OR «A»=TO_NUMBER(NULL))
По какой-то причине строка с NULLом не выбрана. Возможно, это случилось потому, что вычисление предиката «А»=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО. Попробуем явно указать условие включения NULLов в результаты запросов:
select * from t where a in(1,2) or a is null; — вернёт [1,2,NULL]
— filter(«A» IS NULL OR «A»=1 OR «A»=2)
Попробуем с NOT IN:
select * from t where a not in(1,2,null); — no rows selected
— filter(«A»<>1 AND «A»<>2 AND «A»<>TO_NUMBER(NULL))
Ни одной записи так и не появилось.
Это объясняется тем, что трехзначная логика NOT IN не взаимодействует с NULLами: при попадании NULL в условия отбора данных можно не ждать.
Читайте также: