Значение NULL - это тип данных, представляющий неизвестное значение. Это не эквивалентно пустой строке или нулю. Предположим, у вас есть таблица сотрудников, содержащая такие столбцы, как EmployeeId, Name, ContactNumber и альтернативный контактный номер. В этой таблице есть несколько обязательных столбцов значений, таких как EmployeeId, Name и ContactNumber. Однако альтернативный контактный номер не требуется, поэтому его значение неизвестно. Следовательно, значение NULL в этой таблице представляет собой недостающую или неадекватную информацию. Вот другие значения NULL:

  • Значение неизвестно
  • Значение недоступно
  • Атрибут не применим

В этом посте мы рассмотрим, как NULL используется при создании таблиц, запросах, строковых операциях и функциях. Скриншоты в этом посте взяты из Arctype SQL Client.

Разрешение NULL в CREATE TABLE

Для структуры таблицы нам нужно определить, разрешает ли соответствующий столбец NULL или нет. Например, взгляните на следующую таблицу клиентов. Такие столбцы, как CustomerID, FirstName, LastName не допускают значений NULL, тогда как столбцы Suffix, CompanyName и SalesPerson могут хранить значения NULL.

CREATE  TABLE Customers(
	CustomerID SERIAL  PRIMARY  KEY,
	FirstName varchar(50) NOT  NULL,
	MiddleName varchar(50) NULL,
	LastName varchar(50) NOT  NULL,
	Suffix varchar(10) NULL,
	CompanyName varchar(128) NULL,
	SalesPerson varchar(256) NULL,
	EmailAddress varchar(50) NULL
)

Давайте вставим несколько записей в эту таблицу, используя следующий скрипт.

INSERT INTO Customers 
	(FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress)
VALUES
	('John',NULL,'Peter',NULL,NULL,NULL,NULL),
	('Raj','M','Mohan','Mr','ABC','KRS','[email protected]'),
	('Krishna',NULL,'Kumar','MS','XYZ',NULL,'[email protected]')

Использование NULL в предложении WHERE

Теперь предположим, что вы хотите получить записи для тех клиентов, у которых нет адреса электронной почты. Следующий запрос работает нормально, но не выдаст нам строку:

Select * FROM Customers WHERE Emailaddress=NULL

В приведенном выше выражении оператора select определяется «Где адрес электронной почты равен НЕИЗВЕСТНОМУ значению». В стандарте SQL мы не можем сравнивать значение с NULL. Вместо этого для этой цели вы указываете значение IS NULL. Примечание. Между IS и NULL стоит пробел. Если вы удалите пробел, он станет функцией ISNULL ().

Целочисленные, десятичные и строковые операции с NULL

Точно так же предположим, что вы объявили переменную, но не инициализировали ее значение. Если вы попытаетесь выполнить арифметическую операцию, он также вернет NULL, потому что SQL не может определить правильное значение для переменной и считает НЕИЗВЕСТНОЕ значение.

SELECT 10 * NULL

SELECT 10.0 * NULL

NULL также играет важную роль в конкатенации строк. Предположим, вам требуется полное имя клиента в одном столбце, и вы объединяете их с помощью вертикальной черты (||).

SELECT Suffix,  FirstName, MiddleName, LastName, Suffix, 
(Suffix || ' ' || FirstName || ' ' || MiddleName || LastName ) AS CustomerFullName  FROM Customers

Посмотрите на набор результатов - запрос возвращает NULL в объединенной строке, если какая-либо часть строки имеет NULL. Например, у человека в строке 1 нет отчества. Его объединенная строка также имеет значение NULL, потому что SQL не может проверить, что значение строки содержит NULL.

Существует множество функций SQL, позволяющих преодолеть эти проблемы с нулевым значением при конкатенации строк. Мы рассмотрим их позже в этой статье.

Значение NULL в агрегатах SQL

Предположим, вы используете агрегатные функции, такие как SUM, AVG или MIN, MAX для значений NULL. Как вы думаете, каким будет ожидаемый результат?

SELECT Sum(values) AS sum
    ,avg(values) as Avg
    ,Min(Values) as MinValue
    ,Max(Values) as MaxValue
  FROM (VALUES (1), (2), (3),(4), (NULL)) AS a (values);

Посмотрите на приведенный выше рисунок: он рассчитал значения для всех агрегированных функций. SQL игнорирует значения NULL в агрегатных функциях за исключением COUNT () и GROUP BY (). Вы получите сообщение об ошибке, если попытаемся использовать агрегатную функцию для всех значений NULL.

SELECT 
    Sum(values) AS sum
    ,avg(values) as Avg
    ,Min(Values) as MinValue
    ,Max(Values) as MaxValue
           FROM (VALUES (NULL), (NULL), (NULL),(NULL), (NULL)) AS a (values);

ORDER BY и GROUP BY с NULL

SQL считает значения NULL НЕИЗВЕСТНЫМИ значениями. Следовательно, если мы используем предложения ORDER By и GROUP by со столбцами со значениями NULL, он обрабатывает их одинаково и сортирует, группирует их. Например, в нашей таблице клиентов у нас есть NULL в столбце MilddleName. Если мы сортируем данные с использованием этого столбца, в конце будут перечислены значения NULL, как показано ниже.

SELECT Suffix,  FirstName, MiddleName, LastName, Suffix, 
(Suffix || ' ' || FirstName || ' ' || MiddleName || LastName )
 AS CustomerFullName
 FROM Customers
 Order BY MiddleName

Прежде чем использовать GROUP BY, давайте вставим еще одну запись в таблицу. Он имеет значения NULL в большинстве столбцов, как показано ниже.

INSERT INTO Customers (FirstName,MiddleName,LastName,Suffix,CompanyName,
SalesPerson,EmailAddress)
 values('Sant',NULL,'Joseph',NULL,NULL,NULL,NULL);

Теперь используйте предложение GROUP BY для группировки записей на основе их суффикса.

SELECT count(*) as Customercount , suffix
    FROM Customers
    Group BY Suffix

Как показано выше, SQL одинаково обрабатывает эти значения NULL и группирует их. Вы получаете два количества клиентов для записей, для которых не указан суффикс в таблице клиентов.

Полезные функции для работы с NULL

Мы изучили, как SQL обрабатывает значения NULL в различных операциях. В этом разделе мы рассмотрим несколько ценных функций, чтобы избежать получения нежелательных значений из-за NULL.

Использование NULLIF в Postgres и MySQL

Функция NULLIF () сравнивает два входных значения.
● Если оба значения равны, она возвращает NULL.
● В случае несовпадения она возвращает первое значение в качестве выходных данных.
Например, посмотрите на вывод следующих функций NULLIF ().

SELECT   NULLIF (1, 1);

SELECT   NULLIF (100,0);

SELECT   NULLIF ('A', 'Z');

КОЛЕСЦ функция

Функция COALESCE () принимает несколько входных значений и возвращает первое значение, отличное от NULL. Мы можем указать различные типы данных в одной функции COALESCE () и вернуть тип данных с высоким приоритетом.

SELECT COALESCE (NULL, 2, 5) AS NULLRESPONSE;

SELECT coalesce(null, null, 8, 2, 3, null, 4);

Резюме

Тип значения NULL требуется в реляционной базе данных для представления неизвестного или отсутствующего значения. Вам необходимо использовать соответствующую функцию SQL, чтобы избежать нежелательного вывода для таких операций, как конкатенация данных, сравнение, ORDER BY или GROUP BY. Вы не должны пытаться предотвратить значения NULL - вместо этого напишите свой запрос таким образом, чтобы преодолеть его ограничения. Так вы научитесь любить NULL.