How I Learned to Stop Worrying and Love NULL in SQL
You should not try to prevent NULL values — instead, write your query in a way to overcome its limitations.
2 years ago • 6 min read
The NULL value is a data type that represents an unknown value. It is not equivalent to empty string or zero. Suppose you have an employee table containing columns such as EmployeeId , Name , ContactNumber and an alternate contact number. This table has a few mandatory value columns like EmployeeId , Name , and ContactNumber . However, an alternate contact number is not required and therefore has an unknown value. Therefore a NULL value in this table represents missing or inadequate information. Here are other meanings NULL can have:
- Value Unknown
- Value not available
- Attribute not applicable
In this post we will consider how NULL is used in creating tables, querying, string operations, and functions. Screenshots in this post come from the Arctype SQL Client.
Allowing NULL in CREATE TABLE
To a table structure, we need to define whether the respective column allows NULL or not. For example, look at the following customer’s table. The columns such as CustomerID , FirstName , LastName do not allow NULL values, whereas the Suffix , CompanyName , and SalesPerson columns can store NULL values.
Let’s insert a few records into this table using the following script.
Using NULL in the WHERE Clause
Now, suppose you want to fetch records for those customers who do not have an email address. The following query works fine, but it will not give us a row:
In the above select statement expression defines “Where the email address equals an UNKNOWN value”. In the SQL standard, we cannot compare a value to NULL. Instead, you refer to the value as IS NULL for this purpose. Note: There is a space between IS and NULL. If you remove space, it becomes a function ISNULL().
By using IS NULL instead of equals you can query for NULL values.
Integer, Decimal, and String Operations with NULL
Similarly, suppose you declared a variable but did not initialize its value. If you try to perform an arithmetic operation, it also returns NULL because SQL cannot determine the correct value for the variable, and it considers an UNKNOWN value.
Multiplying an integer by NULL returns NULL Multiplying a decimal by NULL returns NULL
NULL also plays an important role in string concatenation. Suppose you required the customer’s full name in a single column, and you concatenate them using the pipe sign(||) .
Setting a string to NULL and then concatenating it returns NULL
Look at the result set — the query returns NULL in the concatenated string if any part of the string has NULL. For example, the person in Row 1 does not have a middle name. Its concatenated string is NULL as well, because SQL cannot validate the string value contains NULL.
There are many SQL functions available to overcome these NULL value issues in string concatenations. We’ll look at them later in this article.
The NULL value in SQL Aggregates
Suppose you use aggregate functions such as SUM, AVG, or MIN, MAX for NULL values. What do you think the expected outcome would be?
In aggregate functions NULL is ignored.
Look at the above figure: it calculated values for all aggregated functions. SQL ignores the NULLs in aggregate functions except for COUNT() and GROUP BY(). You get an error message if we try to use the aggregate function on all NULL values.
Aggregating over all NULL values results in an error.
ORDER BY and GROUP BY with NULL
SQL considers the NULL values as the UNKNOWN values. Therefore, if we use ORDER By and GROUP by clause with NULL value columns, it treats them equally and sorts, group them. For example, in our customer table, we have NULLs in the MilddleName column. If we sort data using this column, it lists the NULL values at the end, as shown below.
NULL values appear last in ORDER BY
Before we use GROUP BY, let’s insert one more record in the table. It has NULL values in most of the columns, as shown below.
Now, use the GROUP BY clause to group records based on their suffix.
GROUP BY does treat all NULL values equally.
As shown above, SQL treats these NULL values equally and groups them. You get two customer counts for records that do not have any suffix specified in the customers table.
Useful Functions for Working with NULL
We explored how SQL treats NULL values in different operations. In this section, we will explore a few valuable functions to avoid getting undesirable values due to NULL.
Using NULLIF in Postgres and MySQL
The NULLIF() function compares two input values.
● If both values are equal, it returns NULL.
● In case of mismatch, it returns the first value as an output.
For example, look at the output of the following NULLIF() functions.
NULLIF returns NULL if two values are equal NULLIF returns the first value if the values are not equal. NULLIF returns the first string in a string compare.
COALESCE function
The COALESCE() function accepts multiple input values and returns the first non-NULL value. We can specify the various data types in a single COALESCE() function and return the high precedence data type.
COALESCE returns the first non NULL data type in a list.
Summary
The NULL value type is required in a relational database to represent an unknown or missing value. You need to use the appropriate SQL function to avoid getting undesired output for operations such as data concatenation, comparison, ORDER BY, or GROUP BY. You should not try to prevent NULL values — instead, write your query in a way to overcome its limitations. This way you will learn to love NULL.
java-interview
SQL, Structured query language («язык структурированных запросов») — формальный непроцедурный язык программирования, применяемый для создания, модификации и управления данными в произвольной реляционной базе данных, управляемой соответствующей системой управления базами данных (СУБД).
Какие существуют операторы SQL?
операторы определения данных (Data Definition Language, DDL):
- CREATE создает объект БД (базу, таблицу, представление, пользователя и т. д.),
- ALTER изменяет объект,
- DROP удаляет объект;
операторы манипуляции данными (Data Manipulation Language, DML):
- SELECT выбирает данные, удовлетворяющие заданным условиям,
- INSERT добавляет новые данные,
- UPDATE изменяет существующие данные,
- DELETE удаляет данные;
операторы определения доступа к данным (Data Control Language, DCL):
- GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом,
- REVOKE отзывает ранее выданные разрешения,
- DENY задает запрет, имеющий приоритет над разрешением;
операторы управления транзакциями (Transaction Control Language, TCL):
- COMMIT применяет транзакцию,
- ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции,
- SAVEPOINT разбивает транзакцию на более мелкие.
Что означает NULL в SQL?
NULL — специальное значение (псевдозначение), которое может быть записано в поле таблицы базы данных. NULL соответствует понятию «пустое поле», то есть «поле, не содержащее никакого значения».
NULL означает отсутствие, неизвестность информации. Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE , ни пустой строке, ни 0 . При сравнении NULL с любым значением будет получен результат NULL , а не FALSE и не 0 . Более того, NULL не равно NULL !
Что такое «временная таблица»? Для чего она используется?
Временная таблица — это объект базы данных, который хранится и управляется системой базы данных на временной основе. Они могут быть локальными или глобальными. Используется для сохранения результатов вызова хранимой процедуры, уменьшение числа строк при соединениях, агрегирование данных из различных источников или как замена курсоров и параметризованных представлений.
Что такое «представление» (view) и для чего оно применяется?
Представление, View — виртуальная таблица, представляющая данные одной или более таблиц альтернативным образом.
В действительности представление – всего лишь результат выполнения оператора SELECT , который хранится в структуре памяти, напоминающей SQL таблицу. Они работают в запросах и операторах DML точно также как и основные таблицы, но не содержат никаких собственных данных. Представления значительно расширяют возможности управления данными. Это способ дать публичный доступ к некоторой (но не всей) информации в таблице.
Каков общий синтаксис оператора SELECT ?
SELECT — оператор DML SQL, возвращающий набор данных (выборку) из базы данных, удовлетворяющих заданному условию. Имеет следующую структуру:
Что такое JOIN ?
JOIN — оператор языка SQL, который является реализацией операции соединения реляционной алгебры. Предназначен для обеспечения выборки данных из двух таблиц и включения этих данных в один результирующий набор.
Особенностями операции соединения являются следующее:
- в схему таблицы-результата входят столбцы обеих исходных таблиц (таблиц-операндов), то есть схема результата является «сцеплением» схем операндов;
- каждая строка таблицы-результата является «сцеплением» строки из одной таблицы-операнда со строкой второй таблицы-операнда;
- при необходимости соединения не двух, а нескольких таблиц, операция соединения применяется несколько раз (последовательно).
Какие существуют типы JOIN ?
(INNER) JOIN Результатом объединения таблиц являются записи, общие для левой и правой таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
LEFT (OUTER) JOIN Производит выбор всех записей первой таблицы и соответствующих им записей второй таблицы. Если записи во второй таблице не найдены, то вместо них подставляется пустой результат ( NULL ). Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
RIGHT (OUTER) JOIN LEFT JOIN с операндами, расставленными в обратном порядке. Порядок таблиц для оператора важен, поскольку оператор не является симметричным.
FULL (OUTER) JOIN Результатом объединения таблиц являются все записи, которые присутствуют в таблицах. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
CROSS JOIN (декартово произведение) При выборе каждая строка одной таблицы объединяется с каждой строкой второй таблицы, давая тем самым все возможные сочетания строк двух таблиц. Порядок таблиц для оператора не важен, поскольку оператор является симметричным.
Что лучше использовать JOIN или подзапросы?
Обычно лучше использовать JOIN , поскольку в большинстве случаев он более понятен и лучше оптимизируется СУБД (но 100% этого гарантировать нельзя). Так же JOIN имеет заметное преимущество над подзапросами в случае, когда список выбора SELECT содержит столбцы более чем из одной таблицы.
Подзапросы лучше использовать в случаях, когда нужно вычислять агрегатные значения и использовать их для сравнений во внешних запросах.
Для чего используется оператор HAVING ?
HAVING используется для фильтрации результата GROUP BY по заданным логическим условиям.
В чем различие между операторами HAVING и WHERE ?
HAVING используется как WHERE , но в другой части SQL-выражения и, соответственно, на другой стадии формирования ответа.
Для чего используется оператор ORDER BY ?
ORDER BY упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого. Возможно определять возрастание ASC или убывание DESC для каждого столбца. По умолчанию установлено — возрастание.
Для чего используется оператор GROUP BY ?
GROUP BY используется для агрегации записей результата по заданным признакам-атрибутам.
Как GROUP BY обрабатывает значение NULL ?
При использовании GROUP BY все значения NULL считаются равными.
В чем разница между операторами GROUP BY и DISTINCT ?
DISTINCT указывает, что для вычислений используются только уникальные значения столбца. NULL считается как отдельное значение. GROUP BY создает отдельную группу для всех возможных значений (включая значение NULL ).
Если нужно удалить только дубликаты лучше использовать DISTINCT , GROUP BY лучше использовать для определения групп записей, к которым могут применяться агрегатные функции.
Перечислите основные агрегатные функции.
Агрегатных функции — функции, которые берут группы значений и сводят их к одиночному значению.
SQL предоставляет несколько агрегатных функций:
COUNT — производит подсчет записей, удовлетворяющих условию запроса; SUM — вычисляет арифметическую сумму всех значений колонки; AVG — вычисляет среднее арифметическое всех значений; MAX — определяет наибольшее из всех выбранных значений; MIN — определяет наименьшее из всех выбранных значений.
В чем разница между COUNT(*) и COUNT() ?
COUNT (*) подсчитывает количество записей в таблице, не игнорируя значение NULL, поскольку эта функция оперирует записями, а не столбцами.
COUNT (
Что делает оператор EXISTS ?
EXISTS берет подзапрос, как аргумент, и оценивает его как TRUE , если подзапрос возвращает какие-либо записи и FALSE , если нет.
Для чего используются операторы IN , BETWEEN , LIKE ?
IN — определяет набор значений.
BETWEEN определяет диапазон значений. В отличие от IN , BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.
LIKE применим только к полям типа CHAR или VARCHAR , с которыми он используется чтобы находить подстроки. В качестве условия используются символы шаблонизации (wildcards) — специальные символы, которые могут соответствовать чему-нибудь:
_ замещает любой одиночный символ. Например, ‘b_t’ будет соответствовать словам ‘bat’ или ‘bit’ , но не будет соответствовать ‘brat’ .
% замещает последовательность любого числа символов. Например ‘%p%t’ будет соответствовать словам ‘put’ , ‘posit’ , или ‘opt’ , но не ‘spite’ .
Для чего применяется ключевое слово UNION ?
В языке SQL ключевое слово UNION применяется для объединения результатов двух SQL-запросов в единую таблицу, состоящую из схожих записей. Оба запроса должны возвращать одинаковое число столбцов и совместимые типы данных в соответствующих столбцах. Необходимо отметить, что UNION сам по себе не гарантирует порядок записей. Записи из второго запроса могут оказаться в начале, в конце или вообще перемешаться с записями из первого запроса. В случаях, когда требуется определенный порядок, необходимо использовать ORDER BY .
Какие ограничения на целостность данных существуют в SQL?
PRIMARY KEY — набор полей (1 или более), значения которых образуют уникальную комбинацию и используются для однозначной идентификации записи в таблице. Для таблицы может быть создано только одно такое ограничение. Данное ограничение используется для обеспечения целостности сущности, которая описана таблицей.
CHECK используется для ограничения множества значений, которые могут быть помещены в данный столбец. Это ограничение используется для обеспечения целостности предметной области, которую описывают таблицы в базе.
UNIQUE обеспечивает отсутствие дубликатов в столбце или наборе столбцов.
FOREIGN KEY защищает от действий, которые могут нарушить связи между таблицами. FOREIGN KEY в одной таблице указывает на PRIMARY KEY в другой. Поэтому данное ограничение нацелено на то, чтобы не было записей FOREIGN KEY , которым не отвечают записи PRIMARY KEY .
Какие отличия между ограничениями PRIMARY и UNIQUE ?
По умолчанию ограничение PRIMARY создает кластерный индекс на столбце, а UNIQUE — некластерный. Другим отличием является то, что PRIMARY не разрешает NULL записей, в то время как UNIQUE разрешает одну (а в некоторых СУБД несколько) NULL запись.
Может ли значение в столбце, на который наложено ограничение FOREIGN KEY , равняться NULL ?
Может, если на данный столбец не наложено ограничение NOT NULL .
Как создать индекс?
Индекс можно создать либо с помощью выражения CREATE INDEX :
либо указав ограничение целостности в виде уникального UNIQUE или первичного PRIMARY ключа в операторе создания таблицы CREATE TABLE .
Что делает оператор MERGE ?
MERGE позволяет осуществить слияние данных одной таблицы с данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется UPDATE , а если нет — INSERT . При этом изменять поля таблицы в секции UPDATE , по которым идет связывание двух таблиц, нельзя.
В чем отличие между операторами DELETE и TRUNCATE ?
DELETE — оператор DML, удаляет записи из таблицы, которые удовлетворяют критерию WHERE при этом задействуются триггеры, ограничения и т.д.
TRUNCATE — DDL оператор (удаляет таблицу и создает ее заново. Причем если на эту таблицу есть ссылки FOREGIN KEY или таблица используется в репликации, то пересоздать такую таблицу не получится).
Что такое «хранимая процедура»?
Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.
Хранимые процедуры позволяют повысить производительность, расширяют возможности программирования и поддерживают функции безопасности данных. В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным) и в дальнейшем её обработка осуществляется быстрее.
Что такое «триггер»?
Триггер (trigger) — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением, удалением или изменением данных в заданной таблице реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически и все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.
Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись. Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.
Что такое «курсор»?
Курсор — это объект базы данных, который позволяет приложениям работать с записями «по одной», а не сразу с множеством, как это делается в обычных SQL командах.
Порядок работы с курсором такой:
- Определить курсор ( DECLARE )
- Открыть курсор ( OPEN )
- Получить запись из курсора ( FETCH )
- Обработать запись…
- Закрыть курсор ( CLOSE )
- Удалить ссылку курсора ( DEALLOCATE ). Когда удаляется последняя ссылка курсора, SQL освобождает структуры данных, составляющие курсор.
Опишите разницу типов данных DATETIME и TIMESTAMP .
DATETIME предназначен для хранения целого числа: YYYYMMDDHHMMSS . И это время не зависит от временной зоны настроенной на сервере. Размер: 8 байт
TIMESTAMP хранит значение равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича. При получении из базы отображается с учётом часового пояса. Размер: 4 байта
Для каких числовых типов недопустимо использовать операции сложения/вычитания?
В качестве операндов операций сложения и вычитания нельзя использовать числовой тип BIT .
Напишите запрос…
Требуется написать запрос, который вернет максимальное значение id и значение created для этого id :
Напишите SQL-запрос, возвращающий все пары (download_count, user_count) , удовлетворяющие следующему условию: user_count — общее ненулевое число пользователей, сделавших ровно download_count скачиваний 19 ноября 2010 года :
Основные положения
Специальное значение NULL означает отсутствие данных, констатацию того факта, что значение неизвестно. По умолчанию это значение могут принимать столбцы и переменные любых типов, если только на них не наложено ограничение NOT NULL . Также, СУБД автоматически добавляет ограничение NOT NULL к столбцам, включенным в первичный ключ таблицы.
Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: = , < , > , like … Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ . Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО .
Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.
Для удобства сделаем процедуру, печатающую состояние булевого параметра:
и включим опцию печати сообщений на консоль:
Привычные операторы сравнения пасуют перед NULLом:
Сравнение с NULLом
Существуют специальные операторы IS NULL и IS NOT NULL , которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.
Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:
Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE , которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.
DECODE идёт против системы:
Пример с составными индексами находится в параграфе про индексы.
Логические операции и NULL
Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ . Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО , то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО . Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.
В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :
Отрицание неизвестности даёт неизвестность:
Оператор OR :
Оператор AND :
Операторы IN и NOT IN
Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу 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 попал в условия отбора, данных не ждите.
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 в оракле неразличимы.
Математика NULLа
Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.
Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.
Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:
С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.
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ов:
Пустой набор данных:
NULL в OLAP
Очень коротко ещё об одной особенности, связанной с агрегатами. В многомерных кубах NULL в результах запроса может означать как отсутствие данных, так и признак группировки по измерению. Самое противное, что на глаз эти две его ипостаси никак не различишь. К счастью, есть специальные функции GROUPING и GROUPING_ID , у которых глаз острее. GROUPING(столбец) вернёт единицу, если NULL в столбце измерения означает признак группировки по этому столбцу и ноль, если там содержится конкретное значение (в частности, NULL). Функция GROUPING_ID — это битовый вектор из GROUPING ов, в этой заметке она точно лишняя.
В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.
Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:
NULL-значения в базе данных
NULL означает отсутствие, неизвестность информации. Значение NULL не является значением в полном смысле слова: по определению оно означает отсутствие значения и не принадлежит ни одному типу данных. Поэтому NULL не равно ни логическому значению FALSE, ни пустой строке, ни нулю. При сравнении NULL с любым значением будет получен результат NULL, а не FALSE и не 0. Более того, NULL не равно NULL! Довольно часто программисты избегают null-значений и для нулевых полей в базе выставляют значения по умолчанию типа 0, » (пустая строка). Во-первых, это неправильно с точки зрения семантики: 0 все-таки означает наличие информации. Во-вторых, использование подобных дефолтных значений может привести к ошибкам.
Допустим, есть табличка product, в которой хранятся модели ноутбуков. У каждой модели есть производитель, описание и цена. Цена может принимать null, т.к. в момент добавления нового товара она может быть неизвестна. Допустим, необходимо вычислить среднюю стоимость для ноутбуков hp. Когда все цены известны, проблем не возникает.
1) В первом эксперименте поле price будет иметь значение по умолчанию » (пустая строка). Цена модели № 4 неизвестна (ее значение 0 или »). В этом случае средняя цена вычисляется не верно, т.к. в расчете учитывается и ноутбук, не имеющий цены.
avg = (400+300+500+0) / 4 = 300
2) Во втором эксперименте поле price имеет значение null. В этом случае база игнорирует null-значения при группировке. Среднее значение высчитывается без учета модели № 4, у которой неизвестна цена.
avg = (400+300+500) / 3 = 400
Таким образом, используйте null-значения там, где они действительно нужны по смыслу.