Null (SQL)

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

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

Null був введений Едгаром Коддом як засіб для представлення відсутніх даних в реляційній моделі незалежно від типу даних. В подальшому він опублікував свою статтю в двох частинах в часописі ComputerWorld^[1][2], в цій статті він презентував 12 правил за якими можна визначити чи є СКБД дійсно реляційною.^[3] Кодд також ввів тризначну логіку, яка має три значення істинності Істина (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) відповідно.^[4] Рекомендація Кодда вимагала розширення логічної структури SQL для відповідності чотиризначній логічній системі. Через цю додаткову складність ідея не отримала широко розповсюдження.

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

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

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

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

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

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

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

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

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

SELECT * 
FROM t 
WHERE i = NULL;

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

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.

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

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 завжди повертає Невідомо.

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

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

Наступний 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.

Внутрішнє об'єднання, яке також присутнє в стандарті 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.^[9] В наступному прикладі, добуток 10 та Null повертає Null:

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

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

NULL / 0

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

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

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

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

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

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

Через те, що SQL:2003 визначає позначки Null як нерівні одна одній, додаткове визначення було необхідне для групування Null разом при виконанні певних операцій. SQL визначає «будь-які два значення, які рівні між собою або будь-які два Null»", як «нерізні».^[12] Це визначення «нерізності» для групування та сортування позначок 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 в СКБД.^[8]

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

Функція 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 приймає два параметри. Якщо перший параметр дорівнює другому 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 призводить фальшивої дихотомії.

Це незавершена стаття про бази даних. Ви можете допомогти проєкту, виправивши або дописавши її.