nullable sql что это
Работа с NULL-значениями
NULL-значение
Достаточно часто встречаются такие случаи, когда в таблице имеются записи с не заданными значениями какого-либо из полей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL позволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL — это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-либо значения для данной строки (записи). Дело обстоит не так в случае простого приписывания полю значения «нуль» или «пробел», которые база данных трактует как любое другое значение. Поскольку NULL не является значением как таковым, он не имеет типа данных. NULL может размещаться в поле любого типа. Тем не менее, NULL, как NULL-значение, часто используется в SQL.
Предположим, появился покупатель, которому еще не назначен продавец. Чтобы констатировать этот факт, нужно ввести значение NULL в поле snum, а реальное значение включить туда позже, когда данному покупателю будет назначен продавец.
IS NULL
Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с любым значением, даже с NULL-значением, результат просто неизвестен. Булево значение «неизвестно» ведет себя также, как «ложь» — строка, на которой предикат принимает значение «неизвестно», не включается в результат запроса – при одном важном исключении: NOT от лжи есть истина (NOT (false)=true), тогда как NOT от неизвестного значения есть также неизвестное значение. Следовательно, такое выражение как «city = NULL» или «city IN (NULL)» является неизвестным независимо от значения city.
Часто необходимо различать false и unknown – строки, содержащие значения столбца, не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения.
SQL IS NULL. Пример.
Вывести все поля из талицы Customers, значения поля city которых равны NULL:
SELECT * FROM Customers WHERE city IS NULL
В данном случае выходных данных не будет, поскольку в поле city нет NULL-значений.
IS NOT NULL
Условие IS NOT NULL используется в запросах для выборки записей со значениями не равных значению NULL
SQL IS NOT NULL. Пример.
Вывести все поля из талицы Customers, значения поля city которых НЕ равны NULL:
SELECT * FROM Customers WHERE city IS NOT NULL
NULL (SQL)
NULL в Системах управления базами данных (СУБД) — специальное значение (псевдозначение), которое может быть записано в поле таблицы базы данных (БД). NULL соответствует понятию «пустое поле», то есть «поле, не содержащее никакого значения». Введено для того, чтобы различать в полях БД пустые (визуально не отображаемые) значения (например, строку нулевой длины) и отсутствующие значения (когда в поле не записано вообще никакого значения, даже пустого).
NULL означает отсутствие, неизвестность информации. Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни нулю. При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0. Более того, NULL не равно NULL!
Содержание
Необходимость NULL в реляционных БД
Использование NULL в БД
В БД, поддерживающих понятие NULL, для поля таблицы при описании определяется, может ли оно быть пустым. Если да, то в это поле можно не записывать никакого значения, и это поле будет иметь значение NULL. Также можно и явно записать в такое поле значение NULL.
Как правило, СУБД не разрешает значение NULL для полей, являющихся частью первичного ключа таблицы. В полях внешних ключей, напротив, NULL допускается. Наличие NULL в поле внешнего ключа может трактоваться как признак отсутствия связанной записи, и для такого внешнего ключа не требуется исполнение правил ссылочной целостности, обязательных для любого другого значения внешнего ключа.
Операции с NULL
Поскольку NULL не является, в общем смысле, значением, использование его в арифметических, строковых, логических и других операциях, строго говоря, некорректно. Тем не менее, большинство СУБД поддерживают такие операции, но вводят для них специальные правила:
Кроме того, могут существовать специальные системные функции для удобного преобразования NULL к определённым значениям, например, в Oracle имеется системная функция NVL, которая возвращает значение своего параметра, если он не NULL, или значение по умолчанию, если операнд — NULL. В стандарте SQL-92 определены две функции: NULLIF и COALESCE, поэтому их использование является более предпочтительным (если конкретная СУБД их реализует).
Значения NULL в языке SQL: отсутствующие данные
Антон Меринов
Автор статьи. Интересы, навыки: Профессиональное администрирование СУБД Oracle Database, веб-разработка, IT-World. Подробнее.
Поскольку база данных обычно представляет собой модель реального мира, отдельные элементы данных в ней неминуемо будут отсутствовать, будут неизвестными или неприменимыми. Например, в столбце QUOTA таблицы SALESREPS содержатся плановые объемы продаж для каждого служащего. Однако для одного из новых служащих план еще не был утвержден; соответствующая информация в базе данных отсутствует. Конечно, в столбец QUOTA для нового служащего можно ввести значение 0, однако это исказит ситуацию. План служащего не равен нулю, а просто «пока неизвестен».
Аналогично столбец MANAGER в таблице SALESREPS для каждого служащего содержит идентификатор его менеджера. Однако Сэм Кларк (Sam Clark), вицепрезидент по торговым операциям, не подчиняется никому из отдела сбыта. К нему этот столбец не относится. Можно ввести в столбец MANAGER для вицепрезидента число 0 или 9999, но ни одно из них не будет настоящим идентификатором начальника Сэма Кларка. Для этой строки не подходит ни одно число.
Рис. 1. Значения null в таблице salesreps
Независимо от академических споров, значения null стали частью стандарта ANSI/ISO и реализованы почти во всех коммерческих СУБД. Они играют важную практическую роль в производстве SQL-баз данных. О специальных правилах обработки значений NULL (и отличиях, существующих при обработке таких значений в различных СУБД) рассказывается позже в моем блоге.
Значения NULL в SQL
Дата публикации: 2017-12-08
От автора: SQL NULL — это термин, используемый для представления отсутствующего значения. Значение NULL в SQL таблице — это значение в поле, которое отображается как пустое.
Поле со значением NULL — это поле без значения. Важно понимать, что значение NULL отличается от нулевого значения или поля, содержащего пробелы.
Синтаксис
Основной синтаксис NULL при создании таблицы.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Здесь NOT NULL означает, что для столбца должны всегда явно указываться соответствующие значения соответствующего типа данных. Для двух столбцов мы не использовали NOT NULL — это означает, что они могут содержать значение NULL.
Поле со значением NULL является полем, которое не было заполнено при создании записи.
Пример
Значение NULL может привести к проблемам при выборе данных. При сравнении неизвестного значения с любым другим значением, результат всегда неизвестен и не включается в результаты. Вы должны использовать операторы IS NULL или NOT NULL для проверки значения NULL.
Рассмотрим таблицу CUSTOMERS, содержащую следующие записи.
Теперь давайте используем оператор IS NOT NULL.
Хорошо ли вы помните nullable value types? Заглядываем «под капот»
В последнее время модной темой стали nullable reference types. Однако старые добрые nullable value types никуда не делись и всё так же активно используются. Хорошо ли вы помните нюансы работы с ними? Предлагаю освежить или проверить свои знания, ознакомившись с этой статьёй. Примеры кода на C# и IL, обращения к спецификации CLI и коду CoreCLR прилагаются. Начать предлагаю с интересной задачки.
Примечание. Если вас интересуют nullable reference types, можете познакомиться с несколькими статьями моих коллег: «Nullable Reference типы в C# 8.0 и статический анализ», «Nullable Reference не защищают, и вот доказательства».
Посмотрите на пример кода ниже, и ответьте, что будет выведено в консоль. И, что не менее важно, почему. Только давайте сразу договоримся, что вы ответите, как есть: без подсказок компиляторов, документации, вычитывания литературы или чего-то подобного. 🙂
Что ж, давайте немного порассуждаем. Возьмём несколько основных направлений мысли, которые, как мне кажется, могут возникнуть.
1. Исходим из того, что int? — ссылочный тип.
Давайте рассуждать так, что int? — это ссылочный тип. В таком случае в а будет записано значение null, оно же будет записано и в aObj после присвоения. В b будет записана ссылка на какой-то объект. Она также будет записана и в bObj после присвоения. В итоге, Object.ReferenceEquals примет в качестве аргументов значение null и ненулевую ссылку на объект, так что…
Всё очевидно, ответ — False!
2. Исходим из того, что int? — значимый тип.
А может быть вы сомневаетесь, что int? — ссылочный тип? И вы уверены в этом, несмотря на выражение int? a = null? Что ж, давайте зайдём с другой стороны и будем отталкиваться от того, что int? — значимый тип.
В таком случае выражение int? a = null выглядит немного странно, но предположим, что это опять в C# сахара сверху насыпали. Получается, что a хранит какой-то объект. b тоже хранит какой-то объект. При инициализации переменных aObj и bObj будет произведена упаковка объектов, хранимых в a и b, в результате чего в aObj и в bObj будут записаны разные ссылки. Получается, что Object.ReferenceEquals в качестве аргументов принимает ссылки на разные объекты, следовательно…
Всё очевидно, ответ — False!
Всё очевидно, ответ — False!
Для тех, кто отталкивался от значимых типов, — если у вас вдруг закрались какие-то сомнения про сравнение ссылок, то можно посмотреть документацию по Object.ReferenceEquals на docs.microsoft.com. В частности, там тоже затрагивают тему значимых типов и упаковки/распаковки. Правда, там описывается кейс, когда экземпляры значимых типов передаются непосредственно в метод, мы же упаковку вынесли отдельно, но суть та же.
When comparing value types. If objA and objB are value types, they are boxed before they are passed to the ReferenceEquals method. This means that if both objA and objB represent the same instance of a value type, the ReferenceEquals method nevertheless returns false, as the following example shows.
Казалось бы, здесь статью можно и закончить, вот только… правильный ответ — True.
Что ж, давайте разбираться.
Разбираемся
Есть два пути — простой и интересный.
Простой путь
Интересный путь
На этой тропинке нам будет недостаточно документации. Она описывает поведение, но не отвечает на вопрос ‘почему’?
Что такое на самом деле int? и null в соответствующем контексте? Почему это работает так? В IL коде используются разные команды или нет? Отличается поведение на уровне CLR? Ещё какая-то магия?
Начнём с разбора сущности int?, чтобы вспомнить основы, и постепенно дойдём до разбора первоначального кейса. Так как C# — язык достаточно «приторный», периодически будем обращаться к IL коду, чтобы смотреть в суть вещей (да, документация по C# — не наш путь сегодня).
int?, Nullable
Здесь рассмотрим основы nullable value types в принципе (что из себя представляет, во что компилируются в IL и т.п.). Ответ на вопрос из задания рассмотрен в следующем разделе.
Рассмотрим фрагмент кода.
Несмотря на то, что на C# инициализация этих переменных выглядит по-разному, для всех них будет сгенерирован один и тот же IL код.
Как видно, в C# всё от души сдобрено синтаксическим сахаром, чтобы нам с вами жилось лучше, по факту же:
С инициализацией по умолчанию мы разобрались — соответствующий IL код мы видели выше. Что же происходит здесь, когда мы хотим проинициализировать aVal значением 62?
Взглянем на IL код:
Опять же, ничего сложного — на evaluation stack загружается адрес aVal, а также значение 62, после чего вызывается конструктор с сигнатурой Nullable (T). То есть два следующих выражения будут полностью идентичны:
В этом же можно убедиться, опять взглянув на IL код:
А что же касается проверок? Например, что на самом деле представляет из себя код следующего вида?
Правильно, для понимания вновь обратимся к соответствующему IL коду.
Для удобства работы тип Nullable определяет:
Упаковка Nullable
Напомню, что при упаковке объекта значимого типа в куче будет создан новый объект. Это поведение наглядно иллюстрирует следующий фрагмент кода:
Результатом сравнения ссылок ожидаемо будет false, так как произошло 2 операции упаковки и создание двух объектов, ссылки на которые были записаны в obj1 и obj2.
Результат всё также ожидаем — false.
А теперь вместо 62 прописываем дефолтное значение.
Иии… результатом неожиданно становится true. Казалось бы, имеем всё те же 2 операции упаковки, создание двух объектов и ссылки на два разных объекта, но результат-то — true!
Ага, наверняка опять дело в сахаре, и что-то поменялось на уровне IL кода! Давайте посмотрим.
Как мы видим, везде упаковка происходит идентичным образом — значения локальных переменных загружается на evaluation stack (инструкция ldloc), после чего происходит сама упаковка за счёт вызова команды box, для которой указывается, какой, собственно, тип будем упаковывать.
Обращаемся к спецификации Common Language Infrastructure, смотрим описание команды box и находим интересное примечание касаемо nullable типов:
If typeTok is a value type, the box instruction converts val to its boxed form.… If it is a nullable type, this is done by inspecting val’s HasValue property; if it is false, a null reference is pushed onto the stack; otherwise, the result of boxing val’s Value property is pushed onto the stack.
Отсюда следует несколько выводов, расставляющих точки над ‘i’:
Состояние экземпляра перед упаковкой:
Состояние экземпляра перед упаковкой:
В исходном примере, который был в самом начале статьи, происходит точно то же самое:
Ради интереса заглянем в исходный код CoreCLR из упомянутого ранее репозитория dotnet/runtime. Нас интересует файл object.cpp, конкретно — метод Nullable::Box, который и содержит нужную нам логику:
Здесь всё то, о чём мы говорили выше. Если не храним значение — возвращаем NULL:
Иначе производим упаковку:
Заключение
Ради интереса предлагаю показать пример из начала статьи своим коллегам и друзьям. Смогут ли они дать верный ответ и обосновать его? Если нет, приглашайте их познакомиться со статьёй. Если же смогут — что ж, моё уважение!
Надеюсь, это было небольшое, но увлекательное приключение. 🙂
P.S. У кого-то мог возникнуть вопрос: а с чего вообще началось погружение в эту тему? Мы делали новое диагностическое правило в PVS-Studio на тему того, что Object.ReferenceEquals работает с аргументами, один из которых представлен значимым типом. Вдруг оказалось, что с Nullable есть неожиданный момент в поведении при упаковке. Посмотрели IL код — box как box. Посмотрели спецификацию CLI — ага, вот оно! Показалось, что это достаточно интересный кейс, про который стоит рассказать — раз! — и статья перед вами.
Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод: Sergey Vasiliev. Check how you remember nullable value types. Let’s peek under the hood.