Sql_celko - глава 6

Глава 6. Невизначені значення - відсутні дані в SQL

6.1. Порожні і відсутні таблиці

Порожня таблиця, або уявлення, відрізняється від відсутньої. Вона визначена, містить стовпці і обмеження, але в ній немає рядків. Таке відбувається, якщо таблиця тільки що була створена або з неї видалили всі дані.
Відсутньої називається таблиця, яка була видалена зі схеми бази даних командою DROP TABLE або яка ніколи не існувала в ній (можливо, ви неправильно ввели її). Концепція відсутніх уявлень, проте, дещо складніше. Подання також може бути відсутнім в результаті дії оператора DROP VIEW або помилки введення, але, крім того, якщо була видалена таблиця або подання, на якому засновано дане подання. Якщо сконструювати уявлення під час виконання, СУБД повідомить про помилку.
Поведінка порожній таблиці, або уявлення, залежить від методу їх використання. Додаткова інформація з даного питання міститься в главах 14. 15 і 16. де описані предикати з підзапитах. В цілому порожню таблицю можна розглядати або як NULL, або як порожня множина в залежності від контексту.
Деякі реалізації SQL видаляють всі об'єкти схеми, що залежать від видаляється таблиці, але більшість такої можливості не підтримують. Це означає, що при спробі використовувати такий об'єкт ви виявите відсутність таблиці. Стандарт SQL-92 робить подібну поведінку можливим, але необов'язковим.

6.2. Відсутність значень в стовпцях

6.3. Контекст і відсутні значення

6.4. Порівняння NULL-значень

Працювати з NULL-значеннями здається неприродним. Їх не можна порівнювати один з одним за допомогою операторів порівняння (так само, нерівно, менше, більше і т.д.), так що замість двозначної логіки ми отримуємо тризначну. Це незвично для більшості програмістів. Якщо виконати оператор:

то можна отримати всі рядки таблиці SomeTable. Однак для досягнення такого результату слід виконати ще й оператор:

Предикат IS [NOT] NULL повертає тільки значення TRUE або FALSE.

6.5. NULL-значення і логіка

Двозначна логіка була розроблена Джорджем Булем, який назавжди увічнив своє ім'я в Булевой алгебри (Boole, 1854). Це не єдина система, але вона найкраще працює з "двійковим" комп'ютером (два стану) і безліччю математичних виразів. У SQL передбачена тризначна логіка - TRUE, FALSE і UNKNOWN. Останнє значення виникає в результаті використання в порівняннях і інших предиката NULL-значень. Майте на увазі, що це логічне значення, воно не збігається з NULL, яке представляє собою значення даних. Саме тому на мові SQL пишуть (х IS [NOT] NULL), ане (х = NULL). Нижче наводяться таблиці з результатами роботи трьох передбачених в SQL логічних операторів:

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

6.5.1. Застосування NULL-значень в предиката з підзапитах

Багато хто забуває про те, що підзапит часто містить порівняння з NULL. Погляньте на наступні дві таблиці:

Зверніть увагу, що їх стовпці допускають NULL. Виконайте такий запит:

Введіть тепер NULL і знову виконайте його:

Результат буде порожнім. Це не зовсім зрозуміло, але правильно. Предикат NOT IN визначається наступним чином:

Предикат IN визначено так:

Останній вираз завжди одно UNKNOWN, тому з урахуванням законів Де Моргана запит фактично виглядає наступним чином:

Поглянувши на наведені вище таблиці значень логічних операторів, ви зрозумієте, що вираз завжди дорівнюватиме UNKNOWN, яке відкидається в умови пошуку в конструкції WHERE.

6.5.2. Рішення в стандарті SQL-92

У SQL-92 дана проблема була вирішена за рахунок додавання нового предиката:

Він дозволяє відобразити будь-яку комбінацію значень тризначної логіки на двозначну. Наприклад, вираз ((age <18) OR (gender = 'Female')) IS NOT FALSE возвращает TRUE, если (age IS NULL) или (gender IS NULL), а второе условие не имеет значения.

6.6. Математичні вирази і NULL-значення

Якщо NULL зустрічається в арифметичних виразах (+, -, *, /), то вони дають у відповіді також NULL. Детальніше числові типи даних розглянуті в розділі 3.

6.7. Функції і NULL-значення

Більшість розробників однаково обробляють NULL-значення у функціях, пропонованих ними як розширення стандарту SQL. Наприклад, косинус від NULL також буде NULL. У розділі 3.5 детально описані дві функції, що перетворюють NULL в звичайні значення.
1. NULLIF (V1, V2) повертає NULL, якщо перший параметр дорівнює другому. Функція еквівалентна наступній специфікації CASE:

2. COALESCE (V1, V2, V3. Vn) переглядає список параметровслева направо і повертає перший з них, що не рівний NULL. Якщо все значення рівні NULL, функція повертає також NULL.

6.8. NULL-значення і базові мови

6.9. Поради з проектування з урахуванням NULL-значень

6.9.1. Як уникнути NULL-значень в базових програмах

6.10. Використання декількох NULL-значень

Визначимо правила: якщо всі значення відомі, ми отримуємо звичайну суму (Total). Якщо одне із значень має статус not applicable (неприйнятно), цей статус отримує і вся сума. Якщо їх немає, то сума набуває статусу missing (відсутній). Якщо ж відсутніх значень також немає, то БД виводить попередження про наявність приблизних (approximate) значень. Загальна форма запиту:

Виникає питання: а навіщо взагалі возитися з сумою valcol? У многоіндікаторном стовпці статус виходить такий же, як і в оригінальній таблиці. Пропоную виконати наступні вправи:
1. Створіть набір правил для роботи з відсутніми даними інапішіте запит, що підраховує значення функцій SUM (), AVG (), MAX (), MIN () і C0UNT ().
2. В отриманому "мультііндікаторе" встановіть ступінь наближення (плюс або мінус 5, 10 і т.д.). Передбачається, що valcol завжди знаходиться посередині.