Null (SQL)

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

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