Что такое null в sql
В SQL NULL не является значением — это состояние , указывающее, что значение элемента неизвестно или не существует. Это не ноль, не пустота, не « пустая строка », и оно не ведет себя как какое-то из этих значений. Некоторые вопросы SQL являются более запутанными, чем NULL , и его работа станет не сложна для понимания, как только вы запомните следующее простое определение: NULL — значит неизвестно .
Позвольте мне повторить, что:
NULL означает НЕИЗВЕСТНО
Держите эту строку в уме во время чтения оставшейся части статьи, и большинство, на первый взгляд, нелогичных результатов, где вы получаете NULL , на практике объяснят сами себя.
| Firebird Documentation Index → NULL в СУБД Firebird → Что такое NULL? |
Использование значения NULL в условиях поиска
позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно).
Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором:
Консоль
Выполнить
Характерной ошибкой является написание предиката в виде:
Этому предикату не соответствует ни одной строки, поэтому результирующий набор записей будет пуст, даже если имеются изделия с неизвестной ценой. Это происходит потому, что сравнение с NULL -значением согласно предикату сравнения оценивается как UNKNOWN . А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE . Это же справедливо и для предиката в предложении HAVING .
Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE (см. пункт 5.10). Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900».
Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение (см. пункт 5.6):
Консоль
Выполнить
Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL -значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE (см. пункт 5.10):
Консоль
Выполнить
Однако ничего не изменилось. Почему? Потому что использованный оператор CASE эквивалентен следующему:
А здесь мы получаем сравнение с NULL -значением, и в результате — UNKNOWN , что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание:
SQL NULL
Если поле в таблице является необязательным, то можно вставить новую запись или обновить запись без добавления значения в это поле. Затем поле будет сохранено с значением NULL.
Примечание: Значение NULL отличается от нулевого значения или поля, содержащего пробелы. Поле с значением NULL — это поле, которое было оставлено пустым во время создания записи!
Как проверить наличие NULL значений?
Невозможно проверить наличие нулевых значений с помощью операторов сравнения, таких как =, .
Вместо этого нам придется использовать оператор IS NULL и IS NOT NULL.
Синтаксис IS NULL
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
Синтаксис IS NOT NULL
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Демо база данных
Ниже приведен выбор из таблицы «Customers» в образце базы данных Northwind:
IS NULL
Оператор IS NULL используется для проверки пустых значений (NULL).
В следующем SQL файле перечислены все клиенты с значением NULL в поле «Address»:
Пример
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Совет: Всегда используйте значение NULL для поиска значений NULL.
IS NOT NULL
Оператор IS NOT NULL используется для проверки непустых значений (NOT NULL).
В следующем SQL файле перечислены все клиенты со значением в поле «Address»:
Пример
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.
Неполные данные и null
Что может означать тот факт, что у студента null в столбце GroupId?
- Значение неизвестно (нет информации, из какой группы студент)
- Значение неверно (студент учится в какой-то группе, но эта группа не представлена в БД)
- Значение еще/уже не существует (студент был зачислен, но еще не распределен в группу или уже отчислен)
- Значение не имеет смысла (студент из другого университета, который пришел с какими-то целями в ИТМО)
- Значение недоступно (недостаточно прав узнать группу)
На основе этих предположений можно сделать вывод, что значение null сильно зависит от контекста (какую предметную область мы моделируем итд.).
Вполне возможно, что возникнет необходимость различать разные виды того, что значение в том или ином смысле отсутствует.
Можно ли обойтись без null?
Как представить кортеж с неопределенными частями в нашем случае?
- Разбить на 2 группы и сделать необязательную связь 1:1. В таком случае, в дополнительной таблице будет запись (StudentId, GroupId) тогда и только тогда, когда у студента определена группа
Где еще появляется null
- Результаты внешних соединений
- Результаты множественных операций
Оказывается, что в некоторых случаях без null не обойтись и надо уметь с ним работать.
Тернарная логика с использованием null
С точки зрения SQL, результат логического выражения может быть true, false или unknown.
С другой стороны есть тип boolean, и у него есть 3 значения: true, false и null
То есть формально unknown — это результат вычисления, а null — это конкретное значение, которое может быть записано в БД. На практике unknown представляется значением null, и это различие не будет иметь большого значения.
Конъюнкция
| [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]unknown[/math] | [math]unknown[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]false[/math] | [math]false[/math] |
Дизъюнкция
| [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]true[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]unknown[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]false[/math] |
Отрицание
| [math][/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]false[/math] | [math]unknown[/math] | [math]true[/math] |
Сравнение
Равенство
| [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]unknown[/math] | [math]unknown[/math] | [math]unknown[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]unknown[/math] | [math]true[/math] |
is
| [math][/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]false[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]true[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]false[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]true[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]false[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]true[/math] | [math]false[/math] |
Проблемы при работе с null
При работе с null в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.
Вывод логических выражений
В новой тернарной логике работают не все правила преобразований, присущие двоичной. Например, нельзя полагать, что [math](A\ \vee\ \neg\ A)[/math] всегда истинно, потому что теперь может получиться unknown.
Поэтому при каждом преобразовании троичного логического выражения, лучше сверяться с таблицами истинности.
Скалярные операции, порождающие null
Следующие операции с null порождают null, и иногда это может сбивать с толку начинающих разработчиков.
- [math]=[/math] , [math]\lt \gt [/math] , [math]\lt [/math] , [math]\lt =[/math] , [math]\gt [/math] , [math]\gt =[/math]
- [math]+[/math] , [math]−[/math] , [math]*[/math] , [math]/[/math]
- [math]\|[/math]
- [math]in[/math]
Рассмотрим несколько примеров.
select (1 + null) from Students;
Не смотря на то, что этот запрос не несет большого смысла, на его примере можно убедиться, что в арифметических операциях null «заразен».
select StudentId from Students where GroupId = null;
Это частая ошибка, сравнение с null дает unknown, а значит запрос вернет пустую таблицу.
Говоря об операции сравнения, стоит отметить, что она не транзитивна и не рефлексивна.
- [math]x\ =\ x[/math] — true или null
- [math]x\ \lt \gt \ x[/math] — true или null
- [math]x\ or\ x[/math] — true или null
- [math]x\ or\ not\ x[/math] — true или null
- [math]x\ and\ not\ x[/math] — false или null
Дубликаты и null
Так как null ≠ null, сравнения кортежей, содержащих null не обладают интуитивными свойствами, например:
- [math]R \cup R[/math] — не всегда [math]R[/math]
- [math]R \cap R[/math] — не всегда [math]R[/math]
- [math]R \bowtie R[/math] — не всегда [math]R[/math]
Неинтуитивность null
Рассмотрим запрос, для нахождения студентов не из группы ‘M34391’.
select * from Students where GroupId <> 'M34391'
Корректность запроса зависит от смысла null. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится.
Следующий запрос, хоть и выглядит странно, предполагает просто поиск всевозможных студентов
select * from Students where GroupId <> 'M34391' union select * from Students where GroupId = 'M34391'
Но из-за наличия null, этот запрос не отработает так, как предполагалось. Если GroupId студента null, то сравнение не вернет true, а значит в результате это учтено не будет.
Подробнее у работе функции where будет рассказано в следующем разделе.
Работа с null в SQL
Несмотря на множество проблем, описанных выше, в SQL существуют механизмы, позволяющие корректно обработать null.
Проверки значений
Для сравнения с null используется is null (или is not null ). Получить всех студентов с null в поле GroupId можно следующим образом:
select StudentId from Students where GroupId is null;
В общем виде синтаксис проверки значений выглядит следующим образом: значение is [ not ] < null |true|false|unknown >, например:
- x is not true
- x or x is not null
Так же в SQL существует функция coalesce(v1, v2, . ), которая принимает произвольное число аргументов и возвращает первый не не null. Если все аргументы null, то возвращает null.
Ключи и null
Можно использовать null:
- Альтернативные ключи
- Внешние ключи
- Простые
- Составные, отсутствующие целиком
Первичные ключи не могут содержать null.
Предикаты
DML
where и having считают истинным предикат только если он вернул true Зная этот факт можно, например убедиться, что false and unknown дает false. Следующий запрос вернет 1:
select 1 where not (0 = 1 and 0 = null)
DDL
C точки зрения check constraint-ов не подходит только false. Unknown превращается в true
Различимость
Два null не равны и не различимы. Это важно для distinct и group by
Например, кортежи (1, null) и (1, null) склеятся в случае distinct и не породят разные группы в случае group by , т.к. не различимыТипы столбцов
В SQL столбцы могут быть nullable (по умолчанию) и не nullable
birthday date birthday date not null
Перед созданием nullable столбца, рекомендуется дополнительно обдумать, какой конкретно смысл вкладывается в null в данном случае, не скажется ли это негативно на остальных запросах, в случае, если начать его использовать. Если есть возможность, во избежание дополнительных проблем, описанных выше, лучше объявлять столбцы not null.
Прочее
- exists
- возвращает true или ‘false’
- если внутри получились только строки, состоящие из null, то вернет так же false
- пропускают null, т.е. не учитывают его при подсчете
- при отсутствии аргументов, отличных от null, возвращают null
- исключением является count (*), что просто считает количество строк
- Помимо указаний порядка сортировки (asc или desc), можно указывать, куда ставить null-ы — в начало или в конец. Например order by year nulls first. По умолчанию null-ы складываются либо в начало, либо в конец, это нужно уточнять в документации к конкретной СУБД.