Null — спеціальне значення, яке використовується в SQL для позначення відсутності даних. NULL відповідає поняттю «порожнє поле», тобто «поле, яке не містить ніякого значення». Введено винахідником реляційної моделі даних Едгаром Коддом, SQL Null слугує для виконання вимоги, що всі дійсно реляційні системи керування базами даних (РСКБД) підтримують представлення «відсутньої і непридатної інформації». Кодд також ввів використання малої літери грецької омега (ω) як символу для представлення Null в теорії баз даних. NULL також є зарезервованим словом в SQL, яке вказує на спеціальну позначку Null.
Null був причиною суперечок і дебатів через свою асоціацію з трійковою логікою, особливих вимог з його використання в інструкції SQL JOIN, і спеціальної обробки агрегатними функціями та операторами групування SQL. Хоча особливі функції та предикати правильно обробляють Null, опоненти відчувають, що підтримка цієї можливості вводить непотрібну складність і суперечливість в реляційну модель даних.
Історія
Null був введений Едгаром Коддом як засіб для представлення відсутніх даних в реляційній моделі незалежно від типу даних. В подальшому він опублікував свою статтю в двох частинах в часописі ComputerWorld, в цій статті він презентував 12 правил за якими можна визначити чи є СКБД дійсно реляційною.Elmasri, R., & Navathe, S. (1994). Fundamentals of Database Systems. 2nd ed. Redwood City, CA: The Benjamin/Cummings Publishing Co. pp. 283 – 285 Кодд також ввів тризначну логіку, яка має три значення істинності Істина (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 завжди повертає Невідомо.
Об'єднання
Зовнішнє об'єднання в 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