Null (SQL)

У Вікіпедії є статті про інші значення цього терміна: NULL.

Null — спеціальне значення, яке використовується в SQL для позначення відсутності даних. NULL відповідає поняттю «порожнє поле», тобто «поле, яке не містить ніякого значення». Введено винахідником реляційної моделі даних Едгаром Коддом, SQL Null слугує для виконання вимоги, що всі дійсно реляційні системи керування базами даних (РСКБД) підтримують представлення «відсутньої і непридатної інформації». Кодд також ввів використання малої літери грецької омега (ω) як символу для представлення Null в теорії баз даних. NULL також є зарезервованим словом в SQL, яке вказує на спеціальну позначку Null.

Грецька літера омега (ω) в нижньому регістрі використовується для представлення Null в теорії баз даних

Null був причиною суперечок і дебатів через свою асоціацію з трійковою логікою, особливих вимог з його використання в інструкції SQL JOIN, і спеціальної обробки агрегатними функціями та операторами групування SQL. Хоча особливі функції та предикати правильно обробляють Null, опоненти відчувають, що підтримка цієї можливості вводить непотрібну складність і суперечливість в реляційну модель даних.

Історія

Null був введений Едгаром Коддом як засіб для представлення відсутніх даних в реляційній моделі незалежно від типу даних. Вподальшому він опублікував свою статтю в двох частинах в часописі ComputerWorld, в цій статті він презентував 12 правил за якими можна визначити чи є СКБД дійсно реляційною. Кодд також ввів тризначну логіку, яка має три значення істинності Істина (True), Хиба (False) та Невідомо (Unknown), яка дуже близька до концепції Null. Значення Невідомо генерується коли Null порівнюється з будь-яким значенням даних або з Null.

У своїй книзі «The Relational Model for Database Management, Version 2» Кодд, зазначив, що єдиний Null прийнятий в стандарті SQL недостатній, і має бути замінений двома різними Null значеннями для позначення причини відсутності. Ці дві Null-позначки зазвичай згадується як А-значення (A-Values) та І-значення (I-Values), що представляють Відсутній та прийнятний (Missing But Applicable) та Відсутній та неприйнятний (Missing But Inapplicable) відповідно. Рекомендація Кодда вимагала розширення логічної структури SQL для відповідності чотиризначній логічній системі. Через цю додаткову складність ідея не отримала широко розповсюдження.

Тризначна логіка

Через те, що Null не є членом будь-якого домену даних, ми його не сприймаємо як значення, радше як позначку відсутності даних. Через це порівняння з Null ніколи не дасть в результаті Істина або Хиба, результат буде завжди третім логічним значенням, Невідомо. Логічний результат наступного виразу, що порівнює 10 з Null, Невідомо:

SELECT 10 = NULL -- В результаті Невідомо 

Однак, певні вирази з Null можуть повертати значення якщо Null не впливає на вихідне значення виразу. Припустимо, що в наступному прикладі другий операнд обчислюється тільки якщо в цьому виникла потреба:

SELECT TRUE OR NULL -- В результаті Істина 

В цьому випадку факт того, що значення праворуч від OR невідоме не впливає на кіневий результат.

SQL реалізує три логічних значення, таким чином реалізації SQL мають забезпечувати тризначну логіку. Керівні правила тризначної логіки в SQL показані в таблиці нижче (p і q представляють логічні стани)"

p	q	p OR q	p AND q	p = q
True	True	True	True	True
True	False	True	False	False
True	Невідомий	True	Невідомий	Невідомий
False	True	True	False	False
False	False	False	False	True
False	Невідомий	Невідомий	False	Невідомий
Невідомий	True	True	Невідомий	Невідомий
Невідомий	False	Невідомий	False	Невідомий
Невідомий	Невідомий	Невідомий	Невідомий	Невідомий

p	NOT p
True	False
False	True
Невідомий	Невідомий

Базові оператори порівняння в SQL завжди повертають Невідомо коли щось порівнюється з Null, таким чином стандарт SQL забезбечує два спеціальних порівняльних пердиката для Null. IS NULL та IS NOT NULL предикати перевіряють коли дані Null чи ні.

Типізація даних

Null в SQL нетипізований, тобто він не розпізнається як ціле, символ чи будь-який інший тип даних. Через це, часом дуже важливо явно приводити Null до певного типу даних. Наприклад, якщо перевантажені функції підтримуються РСКБД, SQL може бути неспроможним автоматично вибрати правильну функцію без знання типів даних всіх параметрів, включно з тими де передається Null.

Мова маніпулювання даними

Тризначна логіка SQL зустрічається в мові маніпулювання даними в предикатах порівняння в DML виразах і запитах. Пункт WHERE вимагає від DML виразу діяти тільки для рядків на яких предикат повертає Істина. Рядки на яких предикат повертає Хиба або Невідомо не піддаються дії DML виразів INSERT, UPDATE чи DELETE, а також відкидаються DML запитом SELECT. Розуміння Невідомо і Хиба як одного і того самого результату є помилкою, яка часто зустрічається при роботі з Null. Наступний приклад показує таку помилку:

SELECT *  FROM t  WHERE i = NULL; 

Наведений запит завжди буде повертати нуль рядків через те, що порівняння стовпця i з Null завжди дає Невідомо, навіть для рядків де i дорівнює Null, що змушує SELECT відкинути всі рядки. (Однак, на практиці, деякий SQL інструментрарій повертає рядки при використанні порівняння з Null.)

CASE вирази

SQL вирази CASE діють за тими ж правилами обробки Null, що і DML пункт WHERE. Через те, що цей вираз можна трактувати як послідовність умов на рівність, простий вираз CASE не може прямо перевірити на присутність Null. Перевірка на Null в простому виразі CASE завжди повертає Невідомо, як в наступному прикладі:

SELECT CASE i WHEN NULL THEN 'Is Null' -- Цей варіант ніколи не спрацює  WHEN 0 THEN 'Is Zero' -- Спрацює коли i = 0  WHEN 1 THEN 'Is One' -- Спрацює коли i = 1  END FROM t; 

Завдяки тому, що i = NULL обчислюється як Невідомо, неважливо що міститься в стовпці i (навіть якщо там Null), рядок 'Is Null' ніколи не буде повернутий.

Пошуковий вираз CASE також повертає перше значення для якого предикат порівняння повертає Істина, включно з порівняннями, що використовують IS NULL та IS NOT NULL. Наступний приклад показує як використовувати пошуковий вираз CASE для правильної перевірки на Null:

SELECT CASE WHEN i IS NULL THEN 'Null Result' -- Спрацює коли i буде NULL  WHEN i = 0 THEN 'Zero' -- Спрацює коли i = 0  WHEN i = 1 THEN 'One' -- Спрацює коли i = 1  END FROM t; 

В пошуковому виразі CASE, рядок 'Null Result' буде повернуто для всіх рядків де i буде Null.

Check constraint

Первинне місце де тризначна логіка SQL перетинається з SQL мовою опису даних (DDL) це в формі . Перевірка на обмеження розміщена на ствопці діє за дещо різними правила порівняно з DML пунктом WHERE. Якщо DML пункт WHERE має отримати Істина для рядка, тоді як перевірка на обмеження має отримати не Хиба. Це означає, що перевірка на обмеження буде успішно пройдена якщо результат буде Істина або Невідомо. Таблиця з перевіркою на обмеження в наступному прикладі забороняє вставляння будь-яких цілих значень в стовпець i, але вона дозволяє вставляти Null завдяки тому, що результат перевірки буде Невідомо.

CREATE TABLE t (  i INTEGER,  CONSTRAINT ck_i CHECK ( i < 0 AND i = 0 AND i > 0 ) ); 

Для заборони вставлення значень Null, можна застосувати NOT NULL, як показано в наступному прикладі. Обмеження NOT NULL семантично тотожно перевірці на обмеження з предикатомIS NOT NULL.

CREATE TABLE t ( i INTEGER NOT NULL ); 

Процедурні розширення

SQL/PSM (SQL Persistent Stored Modules) визначає процедурні розширення для SQL, такі як вираз IF. Однак, головні постачальники SQL продуктів мають свої історично включені процедурні розширення. Процедурні розширення для організації циклів і порівнянь діють із Null порівняннями схожим чином з DML виразами і запитами. Наступний фрагмент коду, в форматі стандарту ISO SQL, використовує Null тризначну логіку в виразі IF.

IF i = NULL THEN  SELECT 'Результат Істина' ELSEIF NOT(i = NULL) THEN  SELECT 'Результат Хиба' ELSE  SELECT 'Результат Невідомо'; 

Вираз IF виконує дію тільки якщо порівняння обчислюється як Істина. Для виразів яки обчислюються як Хиба або Невідомо, IF передає керування в пункт ELSEIF і потім в пункт ELSE. Результатом попереднього коду буде завжди повідомлення «Результат Невідомо» завдяки тому, що порівняння з Null завжди повертає Невідомо.

Об'єднання

Приклад зовнішнього об'єднання в запиті з Null заповнювачами в результатному наборі. Null позначки представлені словом NULL на місці даних в виборці. Вибірка з Microsoft SQL Server, показана в SQL Server Management Studio.

Зовнішнє об'єднання в SQL, включно з лівим зовнішнім об'єднанням, правим зовнішнім об'єднанням та повним зовнішнім об'єднанням, автоматично підставляє Null як заповнювач для відсутніх значень у відповідних таблицях. Наприклад, для лівих зовнішніх об'єднань Null генерується на місцях з таблиць, що знаходяться праворуч від оператора LEFT OUTER JOIN. Наступний простий приклад використовує дві таблиці для демонстрації підстановки Null замінника при лівому зовнішньому об'єднанні.

Перша таблиця (Employee) містить ID службовців та імена, тоді як друга таблиця (PhoneNumber) містить відповідно ID службовців та телефонні номери.

Employee ID LastName FirstName 1 Сквира Степан 2 Ворохта Василь 3 Толока Тетяна 4 Балабух Борислав

PhoneNumber ID Number 1 555-2323 3 555-9876

Наступний SQL запит виконує ліве зовнішнє об'єднання на цих двох таблицях.

SELECT e.ID, e.LastName, e.FirstName, pn.Number FROM Employee e LEFT OUTER JOIN PhoneNumber pn ON e.ID = pn.ID; 

Результатний набір цього запиту показує як SQL використовує Null як замінник для значень відсутніх в таблиці PhoneNumber.

Query Result

ID	LastName	FirstName	Number
1	Сквира	Степан	555-2323
2	Ворохта	Василь	NULL
3	Толока	Тетяна	555-9876
4	Балабух	Борислав	NULL

Внутрішнє об'єднання, яке також присутнє в стандарті SQL, не продукує Null замість відсутніх значень в відповідних таблицях.

Треба бути обережним використовуючи стовпці з можливими значеннями Null в критеріях об'єднань SQL. Через те, що Null не дорівнює іншому Null, Null в стовпці однієї таблиці не буде об'єдуватись з Null в парній таблиці шляхом використання стандартних операторів для перевірки рівності. SQL функція COALESCE або вирази CASE можуть бути використані для такої перевірки в критеріях об'єднання, IS NULL і IS NOT NULL предикати також можуть бути використані в критеріях об'єднання.

Наступний вираз перевіряє на рівність значення A і B і трактує Null-и як рівні значення. Оператор IFNULL необхідний з огляду на те, що A = B повертає Null якщо хоча б одне значення є Null і NULL OR FALSE є Null.

IFNULL( A = B, FALSE ) OR ( A IS NULL AND B IS NULL ) 

Математичні операції та об'єднання рядків

Через те, що Null не тип даних, а просто позначка невідомого значення, використання математичних операторів з Null повертає невідоме значення, яке представляється за допомогою Null. В наступному прикладі, добуток 10 та Null повертає Null:

10 * NULL -- Результат NULL 

Це може призвести до неочікуваних результатів. Наприклад, при спробі розділити Null на нуль, платформа може повернути Null замість генерування помилки ділення на нуль ("data exception - division by zero").. Хоч поведінка в цій ситуації не визначена стандартом ISO SQL багато СКБД трактують цю операцію схожим чином. Наприклад, Oracle, PostgreSQL, MySQL Server і Microsoft SQL Server повернуть Null як результат наступного виразу:

NULL / 0 

Операція конкатенації рядків, яка часто зустрічається в SQL, також продукує Null коли один з операндів Null. Наступний приклад показує, як результат Null буде повернений при використанні Null у SQL-операції з'єднання стрічок з використанням оператора ||.

'Fish ' || NULL || 'Chips' -- Результат NULL 

Це не вірно для всіх реалізацій баз даних. Наприклад, в Oracle NULL та порожній рядок трактуються однаково, таким чином 'Fish ' || NULL || 'Chips' повертає 'Fish Chips'.

Агрегатні функції

SQL визначає агрегатні функції для спрощення агрегатних обчислень на сервері. Майже всі агрегатні функції виконують крок з виключення Null, таким чином Null значення не включаються в результат обчислень. Таке неявне виключання Null може відбитися на результаті обчислень агрегатної функції.

Наступна таблиця видає різні результати для кожного стовпця при застосуванні агрегатної функції AVG:

Table

i	j
150	150
200	200
350	350
NULL	0

Агрегатна функція AVG застосована до стовпця i повертає 233 і 175 коли ми застосовуємо її до стовпця j. Крок з виключення Null призводить до різниці в результаті. Єдина агрегатна функція яка не виключає Null це функція COUNT(*).

Групування та сортування

Через те, що SQL:2003 визначає позначки Null як нерівні одна одній, додаткове визначення було необхідне для групування Null разом при виконанні певних операцій. SQL визначає «будь-які два значення, які рівні між собою або будь-які два Null»", як «нерізні». Це визначення «нерізності» для групування та сортування позначок Nulls коли пункт GROUP BY (або інші ключові слова, що виконують групування) використовуються.

Інші SQL оператори, вирази та ключові слова використовують «нерізні» в їхньому трактуванні позначок Null. Вірно для наступних:

• PARTITION BY разом із ROW_NUMBER
• UNION, INTERSECT та EXCEPT оператори, що трактують NULL як рівні для порівняння/виключеня рядків
• DISTINCT в запиті SELECT
• Стовпці в яких не дозволено зберігати більше одного Null, наприклад стовпці з обмеженням на унікальність

Стандарт SQL не визначає явно порядок сортування за умовчанням для позначок Null. Замість цього, Null можуть бути відсортовані перед або після всіх даних, використовуючи NULLS FIRST або NULLS LAST пункт виразу ORDER BY. Хоча й не всі постачальники СКБД підтримують цю функціональність. Постачальники, які не реалізують цю функціональність можуть визначити різні трактування для сортування позначок Null в СКБД.

Функції обробки Null

SQL визначає дві функції обробки Null: COALESCE і NULLIF. Обидві функції є скороченнями для пошукових виразів CASE.

COALESCE

Функція COALESCE приймає список параметрів і повертає перше не Null значення зі списку:

COALESCE(value1, value2, value3, ...) 

COALESCE визначена як умовне позначення для наступного SQL CASE виразу:

CASE WHEN value1 IS NOT NULL THEN value1  WHEN value2 IS NOT NULL THEN value2  WHEN value3 IS NOT NULL THEN value3  ...  END 

Деякі SQL СКБД реалізують специфічні функції подібні до COALESCE. Наприклад реалізують функцію ISNULL або інші схожі функції, які функціонально подібні до COALESCE.

NULLIF або NVL

Функція NULLIF приймає два параметри. Якщо перший параметр дорівнює другому NULLIF повертає Null. Інакше, повертається значення першого параметра.

NULLIF(value1, value2) 

Тобто, NULLIF є скороченням для наступного виразу CASE:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END 

Вираз COALESCE може бути приведений до еквівалентного виразу NVL у такий спосіб:

COALESCE ( val1, ... , val{n} ) 

переходить в:

NVL( val1 , NVL( val2 , NVL( val3 , ... , NVL ( val{n-1} , val{n} ) ... ))) 

Суперечка

Загальні помилки

Неправильне розуміння як Null працює стає причиною великої кількості помилок в SQL коді, як в ISO SQL виразах, так і в специфічних діалектах SQL реальних систем баз даних. Ці помилки зазвичай виникають в результаті плутання між Null і або 0 (нуль), або порожнім рядком (рядком зі значенням нульової довжини, представленим в SQL як ''). ISO SQL стандарт визначає Null як відмінний від порожнього рядка та числового значчення 0. В той час як Null позначає відсутність значення, порожній рядок або цифра нуль представляють реальні значення.

Наприклад, пункт WHERE або умовний вираз порівнюють значення в стовпці із константою. Часто невірно припускається, що відсутнє значення буде «менше ніж» або «не дорівнюватиме» константі, але, насправді, вираз повертає Невідомо. Як в прикладі нижче:

SELECT * FROM sometable WHERE num <> 1; -- Рядки де num є NULL не будуть повернуті,   -- протилежно до очікувань багатьох користувачів. 

Схожим чином, Null значення часто плутають з порожніми рядками. Припустимо існування функції LENGTH, яка повертає кількість знаків в рядку. Коли Null передається в цю функцію, вона повертає Null. Це також може призвести до неочікуванних результатів, якщо користувач не дуже добре розбирається в тризначній логіці. Наприклад:

SELECT *  FROM sometable WHERE LENGTH(string) < 20; -- Рядки де NULL не будуть повернуті. 

Це ускладнюється фактом, що деякі утіліти показують NULL як порожній рядок, і порожній рядок може бути невірно збережений як NULL.

Критика

Реалізація Null в ISO SQL є предметом критики, суперечок і закликів до змін. Існують різні думки з цього приводу:

• Думка 1: З точки зору теорії реляційних баз даних, значення Null не потрібне, а його використання — наслідок помилки проектування бази даних. В БД розробленій в повній відповідності з критеріями нормалізації, не може бути стовпців без значень, тобто, не потрібна і спеціальна позначка для таких стовпців. Хоча на практиці, з міркувань ефективності, виявляється корисним знехтувати деякими правилами нормалізації, тоді, як один з видів плати за таке нехтування, з'являються відсутні значення, для позначення яких і був введений Null
• Думка 2: Null необхідний і обов'язковий для будь-якої БД, що претендує на реляційність. Зокрема без нього неможливо коректно побудувати зовнішнє об'єднання (OUTER JOIN) рядків з двох таблиць. Саме цієї точки зору притримувався Кодд, явно помістивши його як третього з 12 правил для реляційних СКБД. Саме цей принцип закріплений в останніх стандартах мови SQL.

Закон виключеного третього

SQL дозволяє три логічні вибори: істина, хиба і невідомо, що означає, що SQL неминуче ігнорує закон виключеного третього. Коротко закон стверджує, для отримання протилежного результату до будь-якого логічного результату достатньо застосувати оператор логічного «заперечення». Однак, його неможливо застосувати до SQL Null. Керуючись законом виключеного третього, наступний логічний вираз може бути спрощений:

SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ); 

Закон виключеного третього дозволяє спрощення предикату в пункті WHERE, отримавши в результаті наступне:

SELECT * FROM stuff; 

Це не спрацює в SQL, через те, що x стовпець може містити Null, що призведе до повернення додаткових рядків.

Насправді:

SELECT * FROM stuff; -- (через тризначну логіку) тотожно з: SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL; 

Тобто, для вірного спрощення першого виразу в SQL необхідно повертати всі рядки де х не Null.

SELECT * FROM stuff WHERE x IS NOT NULL; 

Ігнорування закону виключеного третього вводить додаткову складність в логіку SQL, спроби застосувати це правило в тризначній логіці SQL призводить фальшивої дихотомії.

Примітки

1. Codd E.F. Is Your Database Really Relational? // . — .
2. Codd E.F. Does Your DBMS Run By The Rules? // . — .
3. Elmasri, R., & Navathe, S. (1994). Fundamentals of Database Systems. 2nd ed. Redwood City, CA: The Benjamin/Cummings Publishing Co. pp. 283 – 285
4. Codd, E.F. (1990). The Relational Model for Database Management (вид. Version 2). Addison-Wesley. ISBN .
5. ISO/IEC (2003). . ISO/IEC. Section 4.4.2: The null value. Архів оригіналу за 7 серпня 2008. Процитовано 10 травня 2022.
6. Coles, Michael (27 червня 2005). . SQL Server Central. Red Gate Software. Архів оригіналу за 25 липня 2008. Процитовано 14 липня 2010.
7. ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 8.7: null predicate.
8. Coles, Michael (26 лютого 2007). . SQL Server Central. Red Gate Software. Архів оригіналу за 25 квітня 2007. Процитовано 15 липня 2010.
9. ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 6.2.6: numeric value expressions..
10. ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 6.2.8: string value expression..
11. ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 4.15.4: Aggregate functions..
12. ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 3.1.6.8: Definitions: distinct.
13. ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 6.11: case expression..