Null (SQL)
![](http://178.128.105.246/cars-http-upload.wikimedia.org/wikipedia/commons/3/3a/Db_null.png)
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]
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 чи ні.[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 завжди повертає Невідомо.
![](http://178.128.105.246/cars-http-upload.wikimedia.org/wikipedia/uk/thumb/8/87/Sql_query1.png/270px-Sql_query1.png)
NULL
на місці даних в виборці. Вибірка з Microsoft SQL Server, показана в SQL Server Management Studio.Зовнішнє об'єднання в 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.
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.[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
:
i | j |
---|---|
150 | 150 |
200 | 200 |
350 | 350 |
NULL | 0 |
Агрегатна функція 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 призводить фальшивої дихотомії.
- ↑ Codd E.F. Is Your Database Really Relational? // ComputerWorld. — .
- ↑ Codd E.F. Does Your DBMS Run By The Rules? // ComputerWorld. — .
- ↑ Elmasri, R., & Navathe, S. (1994). Fundamentals of Database Systems. 2nd ed. Redwood City, CA: The Benjamin/Cummings Publishing Co. pp. 283 – 285
- ↑ Codd, E.F. (1990). The Relational Model for Database Management (вид. Version 2). Addison-Wesley. ISBN 0-201-14192-2.
- ↑ а б ISO/IEC (2003). ISO/IEC 9075-1:2003, "SQL/Framework". ISO/IEC. Section 4.4.2: The null value. Архів оригіналу за 7 серпня 2008. Процитовано 10 травня 2022.
- ↑ а б Coles, Michael (27 червня 2005). Four Rules for Nulls. SQL Server Central. Red Gate Software. Архів оригіналу за 25 липня 2008. Процитовано 14 липня 2010.
- ↑ ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 8.7: null predicate.
- ↑ а б Coles, Michael (26 лютого 2007). Null Versus Null?. SQL Server Central. Red Gate Software. Архів оригіналу за 25 квітня 2007. Процитовано 15 липня 2010.
- ↑ а б ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 6.2.6: numeric value expressions..
- ↑ ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 6.2.8: string value expression..
- ↑ ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 4.15.4: Aggregate functions..
- ↑ ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 3.1.6.8: Definitions: distinct.
- ↑ ISO/IEC (2003). ISO/IEC 9075-2:2003, "SQL/Foundation". ISO/IEC. Section 6.11: case expression..
|
|
![]() |
Це незавершена стаття про бази даних. Ви можете допомогти проєкту, виправивши або дописавши її. |