Дыра это просто ничто, но вы можете в ней сломать шею.Остин О'Мэлли
В этой статье я хочу с разных сторон посмотреть на NULL - явление в мире SQL фундаментальное. Все приведенные ниже примеры выполнены в СУБД Oracle 11gR2.
Создам таблицу для экспериментов и вставлю в нее несколько строк:
SQL> CREATE TABLE items (
2 id NUMBER,
3 name VARCHAR2(20)
4 );
Table created
SQL> INSERT INTO items VALUES (1, 'скрепка');
1 row inserted
SQL> INSERT INTO items VALUES (2, NULL);
1 row inserted
SQL> INSERT INTO items VALUES (NULL, NULL);
1 row inserted
SQL> SELECT rownum, i.* FROM items i;
ROWNUM ID NAME
---------- ---------- --------------------
1 1 скрепка
2 2
3
Указание литерала NULL для столбца name привело к тому, что значение в столбце name для строк с id равным 2 и 3 отсутствует. Таким образом, NULL - это ключевое слово, обозначающее отсутствие значения.
В разных языках программирования переменные, не инициализированные явно, получают самые разные значения. В зависимости от типа, от области видимости их значения могут оказаться либо непредсказуемыми, либо значениями по умолчанию для данного типа данных (0 для целых, пустая строка для строк и т.п.) В SQL нет возможности добавить в таблицу строку с "неинициализированным" столбцом: нужно явно указать значение или признак отсутствия значения, NULL, в команде INSERT, либо заранее явно объявить для столбца значение по умолчанию.
Объявим NULL в качестве значения по умолчанию для столбца name, после чего можно не указывать значение для этого столбца в команде INSERT:
ALTER TABLE items MODIFY (name VARCHAR2(20) DEFAULT NULL);
SQL> INSERT INTO items (id) VALUES (3);
1 row inserted
SQL> SELECT rownum, i.* FROM items i;
ROWNUM ID NAME
---------- ---------- --------------------
1 1 скрепка
2 2
3
4 3
Для проверки столбца на отсутствие (присутствие) в нем значения используется оператор IS NULL (IS NOT NULL), а операторы сравнения с NULL всегда дают NULL, что интерпретируется как отрицательный результат сравнения:
SQL> SELECT rownum, i.* FROM items i WHERE name IS NULL;
ROWNUM ID NAME
---------- ---------- --------------------
1 2
2
3 3
SQL> SELECT rownum, i.* FROM items i WHERE name = NULL;
ROWNUM ID NAME
---------- ---------- --------------------
SQL> SELECT rownum, i.* FROM items i WHERE name > NULL;
ROWNUM ID NAME
---------- ---------- --------------------
Выходит, значение 'скрепка' не равно NULL, и отсутствие значения тоже не равно NULL. Иначе второй SELECT вернул хотя бы что-то. Вот еще одна компактная и убедительная иллюстрация того, как работает NULL:
SQL> SELECT * FROM dual WHERE NULL = NULL;
DUMMY
-----
SQL> SELECT * FROM dual WHERE NULL != NULL;
DUMMY
-----
SQL> SELECT * FROM dual WHERE NULL IS NULL;
DUMMY
-----
X
SQL> SELECT * FROM dual WHERE NULL IS NOT NULL;
DUMMY
-----
Итак, NULL означает отсутствие значения, и отсутствие значения есть отсутствие значения (NULL IS NULL).
Чтобы не допустить отсутствия значения в столбце таблицы, необходимо объявить ограничение целостности NOT NULL для этого столбца. Сделаем это для столбца id, предварительно удалив строку, где столбец id не содержит значения:
SQL> DELETE FROM items WHERE id IS NULL;
1 row deleted
SQL> ALTER TABLE items MODIFY (id NUMBER NOT NULL);
Table altered
SQL> INSERT INTO items (id) VALUES (NULL);
ORA-01400: cannot insert NULL into ("AY"."ITEMS"."ID")
Теперь в таблице не может быть строки, в которой столбец id пуст.
Интересно, что в системном словаре Oracle ограничение целостности NOT NULL оказывается частным случаем ограничения CHECK:
SQL> SELECT constraint_name, constraint_type, search_condition
2 FROM user_constraints
3 WHERE TABLE_NAME='ITEMS';
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
--------------- --------------- ----------------------------------------
SYS_C0010385 C "ID" IS NOT NULL
Другой способ гарантировать присутствие значения в столбце - включить этот столбец в первичный ключ таблицы. То есть, создать ограничение целостности PRIMARY KEY:
-- убираю ранее объявленное ограничение целостности NOT NULL
SQL> ALTER TABLE items MODIFY (id NUMBER NULL);
Table altered
-- объявляю первичный ключ
SQL> ALTER TABLE items ADD PRIMARY KEY (id);
Table altered
SQL> SELECT constraint_name, constraint_type, search_condition
2 FROM user_constraints
3 WHERE TABLE_NAME='ITEMS';
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
--------------- --------------- ----------------------------------------
SYS_C0010388 P
Теперь первичный ключ - единственное ограничение целостности на столбце id, и оно гарантирует
- уникальность значений в столбце и
- наличие значений в столбце,
даже при отсутствии ограничения целостности NOT NULL:
SQL> -- id не может быть пустым
SQL> INSERT INTO items VALUES (NULL, NULL);
ORA-01400: cannot insert NULL into ("AY"."ITEMS"."ID")
SQL> INSERT INTO items VALUES (4, 'ножницы');
1 row inserted
SQL> -- id должен быть уникален
SQL> INSERT INTO items VALUES (4, 'карандаш');
ORA-00001: unique constraint (AY.SYS_C0010388) violated
SQL> SELECT * FROM items;
ID NAME
---------- --------------------
4 ножницы
1 скрепка
2
3
А вот ограничение целостности UNIQUE (уникальный ключ) допускает отсутствие значений в столбце, гарантируя лишь, что все присутствующие значения уникальны:
SQL> ALTER TABLE items ADD CONSTRAINT items_name_uk UNIQUE (name);
Table altered
SQL> INSERT INTO items VALUES (5, NULL);
1 row inserted
SQL> INSERT INTO items VALUES (6, 'степлер');
1 row inserted
SQL> -- значение name должно быть уникальным
SQL> INSERT INTO items VALUES (7, 'степлер');
ORA-00001: unique constraint (AY.ITEMS_NAME_UK) violated
SQL> INSERT INTO items VALUES (8, 'дырокол');
1 row inserted
-- значение name может отсутствовать во многих строках
SQL> SELECT * FROM items ORDER BY id;
ID NAME
---------- --------------------
1 скрепка
2
3
4 ножницы
5
6 степлер
8 дырокол
7 rows selected
Как известно, и первичный, и уникальный ключи используют "за кулисами" уникальный индекс для обеспечения уникальности значений столбца:
SQL> SELECT index_name, uniqueness
2 FROM user_indexes
3 WHERE table_name = 'ITEMS';
INDEX_NAME UNIQUENESS
------------------------------ ----------
SYS_C0010388 UNIQUE
ITEMS_NAME_UK UNIQUE
Отдельного внимания заслуживают взимоотношения NULL и строковых типов Oracle. В Oracle пустая строка VARCHAR2 трактуется как NULL, отсутствие значения:
SQL> INSERT INTO items VALUES (9, '');
1 row inserted
SQL> SELECT * FROM items WHERE name = '';
ID NAME
---------- --------------------
SQL> SELECT * FROM items WHERE name IS NULL;
ID NAME
---------- --------------------
5
2
3
9
SQL> INSERT INTO items VALUES ('', '');
ORA-01400: cannot insert NULL into ("AY"."ITEMS"."ID")
Продемонстрирую практическую эквивалентность пустой строки VARCHAR2 и NULL при их использовании с некоторыми функциями SQL:
SQL> SELECT
2 LENGTH('') len_empty,
3 LENGTH(NULL) len_null,
4 TO_NUMBER('') empty_to_number,
5 TO_NUMBER(NULL) null_to_number,
6 NVL('', 'IS NULL') nvl_empty,
7 NVL(NULL, 'IS NULL') nvl_null
8 FROM dual;
LEN_EMPTY LEN_NULL EMPTY_TO_NUMBER NULL_TO_NUMBER NVL_EMPTY NVL_NULL
---------- ---------- --------------- -------------- --------- --------
IS NULL IS NULL
Однако, пустая строка CHAR - другой случай, поскольку строки CHAR дополняются пробелами до объявленной длины:
SQL> set serveroutput on
SQL> DECLARE
2 c CHAR(1) := '';
3 BEGIN
4 IF c IS NULL THEN
5 DBMS_OUTPUT.PUT_LINE('IS NULL');
6 END IF;
7 IF c = '' THEN
8 DBMS_OUTPUT.PUT_LINE('= ''''');
9 END IF;
10 DBMS_OUTPUT.PUT_LINE(LENGTH(c) || ' [' || c || ']');
11 END;
12 /
1 [ ]
PL/SQL procedure successfully completed
Попробуем NULL в качестве операнда в операциях со строками, числами и датами:
SQL> SELECT
2 '3.145' || NULL str,
3 3.14 + NULL num1,
4 3.14 - NULL num2,
5 3.14 * NULL num3,
6 3.14 / NULL num4,
7 DATE '2014-03-08' + NULL dat1,
8 DATE '2014-03-08' - NULL dat2
9 FROM dual;
STR NUM1 NUM2 NUM3 NUM4 DAT1 DAT2
----- ---------- ---------- ---------- ---------- ----------- -----------
3.145
В выражении со строкой NULL работает как пустая строка, присоединение которой к другой строке не меняет последнюю. Иными словами, оператор конкатенации игнорирует NULL'ы. А в операциях с числами и датами операнд NULL приводит к результату NULL. Еще одна демонстрация этого:
SQL> SELECT *
2 FROM dual
3 WHERE NULL * 5 IS NULL
4 AND NULL + SYSDATE IS NULL;
DUMMY
-----
X
Логично, что подмешивая в выражение неизвестное значение (еще одна ипостась NULL), получаем неизвестный результат. Что для практических целей равнозначно отсутствию результата.
Выше мы видели, что операции сравнения с NULL также возвращают NULL.
Теперь посмотрим на поведение функций SQL, которым передается NULL в качестве аргумента. Начну со скалярных функций, после чего перейду к агрегатным.
Подавляющее большинство встроенных скалярных функций Oracle, получив NULL в качестве агрумента, возвращают NULL:
SQL> SELECT UPPER(NULL) upper,
2 INSTR(NULL, 'qwerty') instr1,
3 INSTR('qwe', NULL) instr2,
4 SUBSTR(NULL, 3, 5) substr1,
5 SUBSTR('qwerty', NULL) substr2,
6 GREATEST(1, 21, NULL) greatest
7 FROM dual;
UPPER INSTR1 INSTR2 SUBSTR1 SUBSTR2 GREATEST
----- ---------- ---------- ------- ------- ----------
Есть несколько функций, которые не следуют указанному правилу: REPLACE, DECODE, NVL и NVL2. Если второй аргумент REPLACE есть NULL, то возвращается первый аргумент без изменений. Работа функций DECODE, NVL и NVL2 связана с выбором из нескольких вариантов. Функции NVL и NVL2 способны превращать отсутствие значения в значение:
SQL> SELECT
2 NVL(NULL, '1-й аргумент IS NULL'),
3 NVL2('', '1-й аргумент IS NOT NULL', '1-й аргумент IS NULL')
4 FROM dual;
NVL(NULL,'1-ЙАРГУМЕНТ NVL2('','1-ЙАРГУМЕНТI
------------------------------ ------------------------------
1-й аргумент IS NULL 1-й аргумент IS NULL
SQL> SELECT name,
2 NVL(name, 'не-знаю-что') nvl,
3 NVL2(name, INITCAP(name), 'Не-знаю-что') nvl2
4 FROM items;
NAME NVL NVL2
-------------------- -------------------- --------------------
ножницы ножницы Ножницы
не-знаю-что Не-знаю-что
один один Один
не-знаю-что Не-знаю-что
степлер степлер Степлер
не-знаю-что Не-знаю-что
дырокол дырокол Дырокол
не-знаю-что Не-знаю-что
8 rows selected
Функция DECODE и оператор CASE по разному (!) справляются с NULL в качестве аргумента (операнда):
SQL> SELECT
2 DECODE('', NULL, 'IS NULL', 'IS NOT NULL') decode,
3 CASE '' WHEN NULL THEN 'IS NULL' ELSE 'IS NOT NULL' END case1,
4 CASE WHEN '' IS NULL THEN 'IS NULL' ELSE 'IS NOT NULL' END case2
5 FROM dual;
DECODE CASE1 CASE2
------- ----------- -----------
IS NULL IS NOT NULL IS NULL
Обратите внимание, что DECODE приравнивает 1-ый агрумент '' ко второму аргументу NULL, а CASE в строке 3 не приравнивает пустую строку '' к NULL. Следовательно, если анализируемое значение может оказаться пустым, то нужно использовать форму оператора CASE, как в строке 4 приведенной команды SELECT.
Перейду к агрегатным функциям. Если вставить в таблицу несколько строк, таких, что во всех столбцах отсутствуют значения, то получим такой любопытный случай:
SQL> CREATE TABLE nullrows (id NUMBER, name VARCHAR2(20));
Table created
SQL> INSERT INTO nullrows SELECT NULL, NULL FROM all_users;
14 rows inserted
SQL> SELECT COUNT(*), COUNT(id), COUNT(name) FROM nullrows;
COUNT(*) COUNT(ID) COUNT(NAME)
---------- ---------- -----------
14 0 0
Как видим, COUNT(*) считает строки, во всех столбцах которых отсутствуют значения, а COUNT(id) и COUNT(name) не считают отсутствующие значения! Подобным же образом отсутствие значений игнорируется и другими агрегатными функциями.
Следующий запрос генерирует 4 строки с NULL и 1 в столбце x:
SQL> SELECT ROWNUM, DECODE(MOD(ROWNUM, 2), 0, NULL, 1) x
2 FROM dual CONNECT BY level < 5;
ROWNUM X
---------- -
1 1
2
3 1
4
Убедимся, что агрегатные функции просто игнорируют отсутствие значений в столбце:
SQL> SELECT COUNT(x), SUM(x), AVG(x), MAX(x)
2 FROM (
3 SELECT DECODE(MOD(ROWNUM, 2), 0, NULL, 1) x
4 FROM dual CONNECT BY level < 5
5 );
COUNT(X) SUM(X) AVG(X) MAX(X)
---------- ---------- ---------- ------
2 2 1 1
В статье Пользовательские агрегатные функции в Oracle показано, что Oracle передает в пользовательские агрегатные функции только известные значения и не передает NULL.
В следующий раз я рассмотрю взаимоотношения NULL cо значениями типа BOOLEAN в PL/SQL, некоторые подзапросы и иные вещи, связанные с многоликим NULL в СУБД Oracle.
Комментариев нет:
Отправить комментарий