Нулевой (SQL) - Null (SQL)

Греческий нижний регистр омега (ω) символ используется для представления Null в теория баз данных.

Ноль или же НОЛЬ специальный маркер, используемый в Структурированный язык запросов чтобы указать, что значение данных не существует в базе данных. Введено создателем реляционный модель базы данных, Э. Ф. Кодд, SQL Null служит для выполнения требования, что все истинный системы управления реляционными базами данных (СУБД) поддерживать представление «недостающей информации и неприменимой информации». Кодд также ввел использование строчных букв Греческий омега (ω) символ для представления Null в теория баз данных. В SQL НОЛЬ это зарезервированное слово используется для идентификации этого маркера.

Нулевое значение не следует путать со значением 0. Нулевое значение указывает на отсутствие значения - отсутствие значения - это не то же самое, что значение нуля, точно так же, как отсутствие ответа не является то же самое, что и ответ «нет». Например, рассмотрите вопрос «Сколько книг у Адама?» Ответ может быть «ноль» (мы знать что он владеет никто) или "ноль" (мы не знаю сколько он владеет). В таблице базы данных столбец сообщение об этом ответе начнется без значения (помечено Null) и не будет обновлено со значением «ноль», пока мы не убедимся, что Адам не владеет книгами.

SQL null - это состояние, а не значение. Это использование сильно отличается от большинства языков программирования, где нулевое значение ссылки означает, что она не указывает ни на какой объект.

История

Э. Ф. Кодд упомянул нули как метод представления отсутствующих данных в реляционная модель в статье 1975 г. Бюллетень FDT ACM -SIGMOD. Документ Кодда, который чаще всего цитируется в связи с семантикой Null (принятой в SQL), - это его статья 1979 года в Транзакции ACM в системах баз данных, в котором он также представил Реляционная модель / Тасмания, хотя многие другие предложения из последней статьи остались неясными. В разделе 2.3 его статьи 1979 года подробно описывается семантика распространения NULL в арифметических операциях, а также сравнения с использованием троичный (трехзначный) логика при сравнении с нулями; в нем также подробно описывается обработка нулевых значений в других операциях с множеством (последний вопрос сегодня все еще вызывает споры). В теория баз данных кругов, первоначальное предложение Кодда (1975, 1979) теперь упоминается как «таблицы Кодда».^[1] Позже Кодд усилил свое требование, чтобы все РСУБД поддерживали Null для указания отсутствующих данных в статье из двух частей 1985 г., опубликованной в ComputerWorld журнал.^[2][3]

Стандарт SQL 1986 года в основном принял предложение Кодда после прототипа реализации в IBM System R. Несмотря на то что Дон Чемберлин признал нули (наряду с повторяющимися строками) как одну из самых спорных особенностей SQL, он защищал дизайн нулей в SQL, ссылаясь на прагматические аргументы, что это была наименее дорогостоящая форма поддержки системы для отсутствующей информации, избавляющая программиста от множества дублирующих приложений. -уровневые проверки (см. проблема полупредиката ), в то же время предоставляя разработчику базы данных возможность не использовать пустые значения, если они того пожелают; например, чтобы избежать хорошо известных аномалий (обсуждаемых в раздел семантики этой статьи). Чемберлин также утверждал, что помимо предоставления некоторых функций с отсутствующими значениями, практический опыт работы с Nulls также привел к появлению других языковых функций, которые полагаются на Null, таких как определенные группирующие конструкции и внешние соединения. Наконец, он утверждал, что на практике пустые значения также в конечном итоге используются как быстрый способ исправить существующий схема когда ему необходимо развиваться за пределы своего первоначального замысла, кодирование не для пропущенной, а для неприменимой информации; например, база данных, которая быстро нуждается в поддержке электромобилей, имея столбец миль на галлон.^[4]

Кодд указал в своей книге 1990 г. Реляционная модель для управления базами данных, версия 2 что единственный Null, требуемый стандартом SQL, был неадекватным и должен быть заменен двумя отдельными маркерами типа Null, чтобы указать причину отсутствия данных. В книге Кодда эти два маркера нулевого типа называются «A-значения» и «I-значения», представляющие «Отсутствующие, но применимые» и «Отсутствующие, но неприменимые» соответственно.^[5] Рекомендация Кодда потребовала бы расширения логической системы SQL для включения четырехзначной логической системы. Из-за этой дополнительной сложности идея множественных пустых значений с разными определениями не получила широкого распространения в области практикующих баз данных. Тем не менее, это остается активной областью исследований, и многие статьи все еще публикуются.

Вызовы

Null был предметом споров и источником дискуссий из-за связанных с ним трехзначная логика (3VL), особые требования к его использованию в SQL присоединяется, а также особая обработка, требуемая агрегатными функциями и операторами группировки SQL. Профессор информатики Рон ван дер Мейден резюмировал различные проблемы следующим образом: «Несоответствия в стандарте SQL означают, что невозможно приписать какую-либо интуитивную логическую семантику обработке нулей в SQL».^[1] Хотя для решения этих проблем были внесены различные предложения, сложность альтернатив не позволила их широкому распространению.

Нулевое распространение

Арифметические операции

Поскольку Null - это не значение данных, а маркер отсутствующего значения, использование математических операторов для Null дает неизвестный результат, который представлен Null.^[6] В следующем примере умножение 10 на Null приводит к Null:

10 * НОЛЬ          - Результат NULL

Это может привести к неожиданным результатам. Например, когда делается попытка разделить Null на ноль, платформы могут возвращать Null вместо выдачи ожидаемого «исключения данных - деление на ноль».^[6] Хотя это поведение не определено стандартом ISO SQL, многие поставщики СУБД относятся к этой операции аналогично. Например, все платформы Oracle, PostgreSQL, MySQL Server и Microsoft SQL Server возвращают нулевой результат для следующего:

НОЛЬ / 0

Конкатенация строк

Нить конкатенация операции, которые распространены в SQL, также приводят к Null, когда один из операндов имеет значение Null.^[7] В следующем примере демонстрируется результат Null, возвращаемый при использовании Null с SQL. || оператор конкатенации строк.

'Рыбы ' || НОЛЬ || 'Чипсы'   - Результат NULL

Это не верно для всех реализаций баз данных. В СУБД Oracle, например, NULL и пустая строка считаются одним и тем же, и поэтому «Fish» || NULL || «Чипсы» превращаются в «Рыбные чипсы».

Сравнение с NULL и трехзначной логикой (3VL)

Поскольку Null не входит ни в одну область данных, это не считается "значением", а скорее маркером (или заполнителем), указывающим неопределенное значение. Из-за этого сравнения с Null никогда не могут привести ни к True, ни к False, но всегда к третьему логическому результату, Unknown.^[8] Логический результат выражения ниже, которое сравнивает значение 10 с Null, является Неизвестным:

ВЫБРАТЬ 10 = НОЛЬ       - Результаты неизвестны

Однако некоторые операции с Null могут возвращать значения, если отсутствующее значение не имеет отношения к результату операции. Рассмотрим следующий пример:

ВЫБРАТЬ НОЛЬ ИЛИ ЖЕ ИСТИННЫЙ   - Результаты в True

В этом случае тот факт, что значение слева от ИЛИ неизвестно, не имеет значения, потому что результат операции ИЛИ будет Истинно независимо от значения слева.

SQL реализует три логических результата, поэтому реализации SQL должны обеспечивать специализированный трехзначная логика (3VL). Правила, управляющие трехзначной логикой SQL, показаны в таблицах ниже (п и q представляют собой логические состояния) "^[9] Таблицы истинности, которые SQL использует для операций И, ИЛИ и НЕ, соответствуют общему фрагменту трехзначной логики Клини и Лукасевича (которые различаются по своему определению импликации, однако SQL не определяет такую ​​операцию).^[10]

Эффект неизвестного в предложениях WHERE

Трехзначная логика SQL встречается в Язык обработки данных (DML) в предикатах сравнения операторов и запросов DML. В КУДА Предложение заставляет оператор DML воздействовать только на те строки, для которых предикат имеет значение True. Строки, для которых предикат оценивается как False или Unknown, не обрабатываются ВСТАВЛЯТЬ, ОБНОВИТЬ, или же УДАЛИТЬ Операторы DML и отбрасываются ВЫБРАТЬ запросы. Интерпретация Unknown и False как одного и того же логического результата - распространенная ошибка, возникающая при работе с Nulls.^[9] Следующий простой пример демонстрирует эту ошибку:

ВЫБРАТЬ *ИЗ тКУДА я = НОЛЬ;

Пример запроса выше логически всегда возвращает нулевые строки, потому что сравнение я столбец с Null всегда возвращает значение Unknown, даже для тех строк, где я нулевой. Неизвестный результат вызывает ВЫБРАТЬ заявление, чтобы в целом отбросить каждую строку. (Однако на практике некоторые инструменты SQL извлекают строки, используя сравнение с Null.)

Предикаты сравнения, специфичные для нулей и 3VL

Базовые операторы сравнения SQL всегда возвращают значение Unknown при сравнении чего-либо с Null, поэтому стандарт SQL предусматривает два специальных предиката сравнения, специфичных для Null. В НУЛЕВОЙ и НЕ ПУСТО предикаты (которые используют постфикс синтаксис) проверяет, являются ли данные пустыми или нет.^[11]

Стандарт SQL содержит дополнительную функцию F571 «Проверка значения истинности», которая вводит три дополнительных логических унарных оператора (фактически шесть, если мы посчитаем их отрицание, которое является частью их синтаксиса), также с использованием постфиксной нотации. У них есть следующие таблицы истинности:^[12]

Функция F571 ортогональна присутствию логического типа данных в SQL (обсуждается далее в этой статье) и, несмотря на синтаксическое сходство, F571 не вводит логические или трехзначные литералы на языке. Функция F571 действительно присутствовала в SQL92,^[13] задолго до того, как логический тип данных был введен в стандарт в 1999 году. Однако функция F571 реализована в нескольких системах; PostgreSQL - один из тех, кто его реализует.

Добавление IS UNKNOWN к другим операторам трехзначной логики SQL делает трехзначную логику SQL функционально полный,^[14] это означает, что его логические операторы могут выражать (в комбинации) любую мыслимую трехзначную логическую функцию.

В системах, которые не поддерживают функцию F571, можно эмулировать IS UNKNOWN п перебирая все аргументы, которые могут привести к выражению п Неизвестно и проверьте эти аргументы с помощью IS NULL или других специфичных для NULL функций, хотя это может быть более громоздким.

Закон исключенной четвертой (в пунктах WHERE)

В трехзначной логике SQL закон исключенного среднего, п ИЛИ НЕТ п, больше не оценивается как истина для всех п. Точнее, в трехзначной логике SQL п ИЛИ НЕТ п неизвестно, когда именно п неизвестно и верно в противном случае. Поскольку прямые сравнения с Null приводят к неизвестному логическому значению, следующий запрос

ВЫБРАТЬ * ИЗ вещи КУДА ( Икс = 10 ) ИЛИ ЖЕ НЕТ ( Икс = 10 );

не эквивалентно в SQL с

ВЫБРАТЬ * ИЗ вещи;

если столбец x содержит какие-либо Nulls; в этом случае второй запрос вернет некоторые строки, которые не возвращает первый, а именно все те, в которых x равно Null. В классической двузначной логике закон исключенного третьего позволяет упростить предикат предложения WHERE, фактически исключив его. Попытка применить закон исключенного среднего к 3VL SQL - это, по сути, ложная дихотомия. Второй запрос фактически эквивалентен:

ВЫБРАТЬ * ИЗ вещи;- является (из-за 3VL) эквивалентно:ВЫБРАТЬ * ИЗ вещи КУДА ( Икс = 10 ) ИЛИ ЖЕ НЕТ ( Икс = 10 ) ИЛИ ЖЕ Икс ЯВЛЯЕТСЯ НОЛЬ;

Таким образом, для правильного упрощения первого оператора в SQL требуется, чтобы мы возвращали все строки, в которых x не равен нулю.

ВЫБРАТЬ * ИЗ вещи КУДА Икс ЯВЛЯЕТСЯ НЕТ НОЛЬ;

Ввиду вышеизложенного обратите внимание, что для предложения SQL WHERE a тавтология аналогично закону исключенного третьего. Предполагая, что оператор IS UNKNOWN присутствует, п ИЛИ НЕТ п) ИЛИ ЖЕ (п IS UNKNOWN) верно для каждого предиката п. Среди логиков это называется закон исключенной четвертой.

Есть некоторые выражения SQL, в которых менее очевидно, где возникает ложная дилемма, например:

ВЫБРАТЬ 'Ok' КУДА 1 НЕТ В (ВЫБРАТЬ В РОЛЯХ (НОЛЬ В КАЧЕСТВЕ ЦЕЛОЕ))СОЮЗВЫБРАТЬ 'Ok' КУДА 1 В (ВЫБРАТЬ В РОЛЯХ (НОЛЬ В КАЧЕСТВЕ ЦЕЛОЕ));

не производит строк, потому что В переводится в повторяющуюся версию равенства по набору аргументов, а 1 <> NULL - это неизвестно, так же как 1 = NULL - неизвестно. (CAST в этом примере требуется только в некоторых реализациях SQL, таких как PostgreSQL, которые в противном случае отклонили бы его с ошибкой проверки типа. Во многих системах простой SELECT NULL работает в подзапросе.) Разумеется, отсутствующий случай выше:

ВЫБРАТЬ 'Ok' КУДА (1 В (ВЫБРАТЬ В РОЛЯХ (НОЛЬ В КАЧЕСТВЕ ЦЕЛОЕ))) ЯВЛЯЕТСЯ НЕИЗВЕСТНЫЙ;

Эффект Null и Unknown в других конструкциях

Присоединяется

Объединения оцениваются с использованием тех же правил сравнения, что и для предложений WHERE. Поэтому следует соблюдать осторожность при использовании столбцов, допускающих значение NULL, в критериях соединения SQL. В частности, таблица, содержащая любые нули, является не равный с естественным самосоединением, что означает, что тогда как ${ Displaystyle R Bowtie R = R}$ верно для любого отношения р в реляционная алгебра, самосоединение SQL исключает все строки, где где-либо находится Null.^[15] Пример такого поведения приведен в разделе, посвященном анализу семантики отсутствующих значений Nulls.

SQL КОАЛЕС функция или ДЕЛО выражения могут использоваться для "имитации" нулевого равенства в критериях соединения, а НУЛЕВОЙ и НЕ ПУСТО предикаты также могут использоваться в критериях соединения. Следующий предикат проверяет равенство значений A и B и рассматривает Null как равные.

(А = B) ИЛИ ЖЕ (А ЯВЛЯЕТСЯ НОЛЬ И B ЯВЛЯЕТСЯ НОЛЬ)

CASE-выражения

SQL предоставляет два вида условных выражений. Один называется "простой СЛУЧАЙ" и действует как оператор переключения. Другой в стандарте называется «CASE с поиском» и действует как если ... иначе.

Простой ДЕЛО в выражениях используются неявные сравнения на равенство, которые работают по тем же правилам, что и DML. КУДА правила пункта для Null. Таким образом, просто ДЕЛО выражение не может напрямую проверить наличие Null. Проверка на Null в простом ДЕЛО выражение всегда приводит к Неизвестному, как показано ниже:

ВЫБРАТЬ ДЕЛО я КОГДА НОЛЬ ТОГДА 'Нулевой'  - Это никогда не будет возвращено              КОГДА    0 ТОГДА "Ноль"  - Это будет возвращено, когда я = 0              КОГДА    1 ТОГДА 'Является одним'   - Это будет возвращено, когда я = 1              КОНЕЦИЗ т;

Потому что выражение я = NULL оценивается как Неизвестно независимо от столбца значений я содержит (даже если он содержит Null), строка 'Нулевой' никогда не будет возвращен.

С другой стороны, "обысканный" ДЕЛО выражение может использовать такие предикаты, как НУЛЕВОЙ и НЕ ПУСТО в своих условиях. В следующем примере показано, как использовать поиск ДЕЛО выражение для правильной проверки на Null:

ВЫБРАТЬ ДЕЛО КОГДА я ЯВЛЯЕТСЯ НОЛЬ ТОГДА «Нулевой результат»  - Это будет возвращено, когда i будет NULL            КОГДА     я = 0 ТОГДА 'Нуль'         - Это будет возвращено, когда я = 0            КОГДА     я = 1 ТОГДА 'Один'          - Это будет возвращено, когда я = 1            КОНЕЦИЗ т;

В разыскиваемом ДЕЛО выражение, строка «Нулевой результат» возвращается для всех строк, в которых я нулевой.

Диалект SQL Oracle предоставляет встроенную функцию ДЕКОДИРОВАТЬ который может использоваться вместо простых выражений CASE и считает два нуля равными.

ВЫБРАТЬ ДЕКОДИРОВАТЬ(я, НОЛЬ, «Нулевой результат», 0, 'Нуль', 1, 'Один') ИЗ т;

Наконец, все эти конструкции возвращают NULL, если совпадений не найдено; у них есть дефолт ELSE NULL пункт.

Операторы IF в процедурных расширениях

SQL / PSM (Постоянно хранимые модули SQL) определяет процедурный расширения для SQL, такие как ЕСЛИ утверждение. Однако основные поставщики SQL исторически включали свои собственные проприетарные процедурные расширения. Процедурные расширения для циклов и сравнений работают в соответствии с правилами сравнения Null, аналогичными правилам для операторов и запросов DML. Следующий фрагмент кода в стандартном формате ISO SQL демонстрирует использование Null 3VL в ЕСЛИ утверждение.

ЕСЛИ я = НОЛЬ ТОГДА      ВЫБРАТЬ «Результат верный»ELSEIF НЕТ(я = НОЛЬ) ТОГДА      ВЫБРАТЬ "Результат ложный"ЕЩЕ      ВЫБРАТЬ "Результат неизвестен";

В ЕСЛИ Оператор выполняет действия только для тех сравнений, которые имеют значение True. Для утверждений, которые оцениваются как Ложь или Неизвестно, ЕСЛИ оператор передает управление ELSEIF пункт, и, наконец, ЕЩЕ пункт. Результатом приведенного выше кода всегда будет сообщение "Результат неизвестен" поскольку сравнения с Null всегда оцениваются как Unknown.

Анализ семантики отсутствующих значений SQL Null

Новаторская работа Т. Имелиньски и В. Липски мл. (1984)^[16] предоставил основу для оценки предполагаемой семантики различных предложений по реализации семантики пропущенных значений, которая упоминается как Алгебры Имелинского-Липского. Этот раздел примерно соответствует главе 19 учебника «Алиса».^[17] Похожая презентация появляется в обзоре Рона ван дер Мейдена, §10.4.^[1]

В выборках и прогнозах: слабое представление

Конструкции, представляющие недостающую информацию, такие как таблицы Кодда, на самом деле предназначены для представления набора отношений, по одному для каждого возможного экземпляра их параметров; в случае таблиц Кодда это означает замену пустых значений некоторым конкретным значением. Например,

Конструкция (например, таблица Кодда) называется сильное представительство система (отсутствующей информации), если любой ответ на запрос, сделанный по конструкции, может быть конкретизирован, чтобы получить ответ для любой соответствующий запрос на отношения, которые он представляет, которые рассматриваются как модели конструкции. Точнее, если ${ displaystyle q}$ формула запроса в реляционная алгебра («чистых» отношений) и если ${ displaystyle { bar {q}}}$ это подъем к конструкции, предназначенной для представления недостающей информации, сильное представление имеет свойство, которое для любого запроса q и (таблица) построить Т, ${ displaystyle { bar {q}}}$ лифты все ответы на конструкцию, то есть:

${ Displaystyle mathop { mathrm {Модели}} ({ bar {q}} (T)) = {q (R) , | R in mathop { mathrm {Models}} (T) }}$

(Вышеупомянутое должно выполняться для запросов, принимающих любое количество таблиц в качестве аргументов, но для этого обсуждения достаточно ограничения одной таблицей.) Очевидно, что таблицы Кодда не обладают этим сильным свойством, если выборки и проекции рассматриваются как часть языка запросов. Например, все ответы на

ВЫБРАТЬ * ИЗ Emp КУДА Возраст = 22;

должен включать возможность существования такого отношения, как EmpH22. Однако таблицы Кодда не могут представить дизъюнкционный «результат с возможно 0 или 1 строкой». Устройство, представляющее в основном теоретический интерес, называется условная таблица (или c-table), однако, может представлять такой ответ:

где столбец условия интерпретируется как строка не существует, если условие ложно. Оказывается, поскольку формулы в столбце условий c-таблицы могут быть произвольными. логика высказываний формулы, алгоритм решения проблемы, представляет ли c-таблица конкретное отношение, имеет совместно NP-полный сложность, поэтому не имеет практической ценности.

Поэтому желательно более слабое понятие репрезентации. Имелинский и Липски ввели понятие слабое представительство, что по существу позволяет (поднятым) запросам над конструкцией возвращать представление только для Конечно информация, т.е. если она действительна для всех "возможный мир "экземпляры (модели) конструкции. Конкретно, конструкция является слабой системой представления, если

${ displaystyle bigcap mathop { mathrm {Models}} ({ bar {q}} (T)) = bigcap {q (R) , | R in mathop { mathrm {Models}} (T) }}$

Правая часть приведенного выше уравнения - это Конечно информация, то есть информация, которую можно точно извлечь из базы данных, независимо от того, какие значения используются для замены Nulls в базе данных. В примере, который мы рассмотрели выше, легко увидеть, что пересечение всех возможных моделей (то есть достоверная информация) запроса, выбирающего WHERE Age = 22, на самом деле пусто, потому что, например, (неподтвержденный) запрос не возвращает строк для отношение EmpH37. В более общем плане Имелински и Липски показали, что таблицы Кодда являются слабой системой представления, если язык запросов ограничен проекциями, выборками (и переименованием столбцов). Однако, как только мы добавляем объединения или объединения в язык запросов, даже это слабое свойство теряется, как показано в следующем разделе.

Если рассматриваются объединения или союзы: даже не слабое представление

Рассмотрим следующий запрос к той же таблице Кодда Emp из предыдущего раздела:

ВЫБРАТЬ Имя ИЗ Emp КУДА Возраст = 22СОЮЗВЫБРАТЬ Имя ИЗ Emp КУДА Возраст <> 22;

Какое бы конкретное значение ни было выбрано для нулевого возраста Харриет, приведенный выше запрос вернет полный столбец с именами любой модели Emp, но когда (поднятый) запрос выполняется на самом Emp, Харриет всегда будет отсутствовать, т. Е. Мы имеют:

Таким образом, когда объединения добавляются к языку запросов, таблицы Кодда даже не являются слабой системой представления недостающей информации, а это означает, что запросы по ним даже не сообщают все Конечно Информация. Здесь важно отметить, что семантика UNION on Nulls, которая обсуждается в следующем разделе, даже не участвовала в этом запросе. «Забывчивый» характер двух подзапросов был всем, что требовалось, чтобы гарантировать, что некоторая достоверная информация не будет сообщена, когда вышеуказанный запрос был запущен в таблице Codd Emp.

За естественные соединения, пример, необходимый для демонстрации того, что определенная информация может не передаваться некоторым запросом, немного сложнее. Рассмотрим таблицу

и запрос

ВЫБРАТЬ F1, F3 ИЗ  (ВЫБРАТЬ F1, F2 ИЗ J) В КАЧЕСТВЕ F12  ЕСТЕСТВЕННЫЙ ПРИСОЕДИНИТЬСЯ  (ВЫБРАТЬ F2, F3 ИЗ J) В КАЧЕСТВЕ F23;

Интуиция относительно того, что происходит выше, заключается в том, что таблицы Кодда, представляющие проекции в подзапросах, не учитывают тот факт, что пустые значения в столбцах F12.F2 и F23.F2 на самом деле являются копиями оригиналов в таблице J. Это наблюдение предполагает, что относительно простое улучшение таблиц Кодда (которое правильно работает в этом примере) заключалось бы в использовании Константы Сколема (смысл Сколемские функции которые также постоянные функции ), скажем ω₁₂ и ω₂₂ вместо одного символа NULL. Такой подход, называемый v-таблицами или наивными таблицами, является менее затратным с точки зрения вычислений, чем c-таблицы, описанные выше. Однако это все еще не полное решение для неполной информации в том смысле, что v-таблицы являются лишь слабым представлением для запросов, не использующих никаких отрицаний при выборе (и не использующих никаких различий в наборах). В первом примере, рассмотренном в этом разделе, используется предложение отрицательного выбора WHERE Age <> 22, поэтому это также пример, когда запросы v-таблиц не будут сообщать достоверную информацию.

Проверить ограничения и внешние ключи

Основное место пересечения трехзначной логики SQL и SQL Язык определения данных (DDL) имеет вид проверить ограничения. Проверочное ограничение, наложенное на столбец, работает по немного другому набору правил, чем для DML. КУДА пункт. Хотя DML КУДА Предложение должно оцениваться как True для строки, контрольное ограничение не должно оцениваться как False. (С логической точки зрения обозначенные значения являются Истинным и Неизвестным.) Это означает, что ограничение проверки будет успешным, если результат проверки будет Истинным или Неизвестным. Следующая таблица в качестве примера с проверочным ограничением запрещает вставку любых целочисленных значений в столбец. я, но позволит вставить Null, поскольку результат проверки всегда будет оцениваться как Unknown for Nulls.^[18]

СОЗДАЙТЕ СТОЛ т (     я ЦЕЛОЕ,     ОГРАНИЧЕНИЕ ck_i ПРОВЕРИТЬ ( я < 0 И я = 0 И я > 0 ) );

Из-за изменения обозначенных значений относительно предложения WHERE с логической точки зрения закон исключенного среднего является тавтологией для ограничений CHECK, что означает CHECK (п ИЛИ НЕТ п) всегда получается. Более того, предполагая, что пустые значения должны интерпретироваться как существующие, но неизвестные значения, некоторые патологические проверки, подобные приведенному выше, позволяют вставлять пустые значения, которые никогда не могут быть заменены никаким ненулевым значением.

Чтобы ограничить столбец отклонением пустых значений, НЕ НОЛЬ ограничение может быть применено, как показано в примере ниже. В НЕ НОЛЬ ограничение семантически эквивалентно проверить ограничение с НЕ ПУСТО предикат.

СОЗДАЙТЕ СТОЛ т ( я ЦЕЛОЕ НЕТ НОЛЬ );

По умолчанию проверяйте ограничения на внешние ключи успешно, если любое из полей в таких ключах равно NULL. Например, таблица

СОЗДАЙТЕ СТОЛ Книги( заглавие VARCHAR(100),  author_last VARCHAR(20),  author_first VARCHAR(20),ИНОСТРАННЫЙ КЛЮЧ (author_last, author_first)  РЕКОМЕНДАЦИИ Авторы(фамилия, имя));

позволит вставлять строки, в которых author_last или author_first имеют значение NULL, независимо от того, как таблица Authors определена или что она содержит. Точнее, пустое значение в любом из этих полей допускает любое значение в другом, даже если оно не найдено в таблице авторов. Например, если авторы содержат только ('Doe', 'John'), тогда ('Smith', NULL) удовлетворяет ограничению внешнего ключа. SQL-92 добавлены две дополнительные опции для сужения совпадений в таких случаях. Если ЧАСТИЧНЫЙ МАТЧ добавляется после РЕКОМЕНДАЦИИ объявление, то любое ненулевое значение должно соответствовать внешнему ключу, e. грамм. ('Doe', NULL) все равно будет соответствовать, но ('Smith', NULL) не будет. Наконец, если МАТЧ ПОЛНЫЙ добавляется, то ('Smith', NULL) также не будет соответствовать ограничению, но (NULL, NULL) все равно будет соответствовать ему.

Внешние стыки

Пример SQL внешнее соединение запрос с пустыми заполнителями в наборе результатов. Нулевые маркеры представлены словом НОЛЬ вместо данных в результатах. Результаты получены из Microsoft SQL Server, как показано в SQL Server Management Studio.

SQL внешние соединения, включая левые внешние соединения, правые внешние соединения и полные внешние соединения, автоматически создают пустые значения в качестве заполнителей для отсутствующих значений в связанных таблицах. Для левых внешних соединений, например, пустые значения создаются вместо строк, отсутствующих в таблице, появляющейся в правой части таблицы. ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ оператор. В следующем простом примере используются две таблицы для демонстрации создания пустого заполнителя в левом внешнем соединении.

Первая таблица (Наемный рабочий) содержит идентификационные номера и имена сотрудников, а вторая таблица (Телефонный номер) содержит соответствующие идентификационные номера сотрудников и телефонные номера, как показано ниже.

В следующем примере SQL-запроса выполняется левое внешнее соединение этих двух таблиц.

ВЫБРАТЬ е.Я БЫ, е.Фамилия, е.Имя, пн.ЧислоИЗ Наемный рабочий еОСТАВИЛИ ВНЕШНИЙ ПРИСОЕДИНИТЬСЯ Телефонный номер пнНА е.Я БЫ = пн.Я БЫ;

В набор результатов сгенерированный этим запросом демонстрирует, как SQL использует Null в качестве заполнителя для значений, отсутствующих в правом (Телефонный номер), как показано ниже.

Агрегатные функции

SQL определяет агрегатные функции для упрощения агрегированных вычислений данных на стороне сервера. За исключением СЧИТАТЬ(*) функции, все агрегатные функции выполняют шаг исключения Null, так что Nulls не включаются в окончательный результат вычисления.^[19]

Обратите внимание, что удаление Null не эквивалентно замене Null нулем. Например, в следующей таблице AVG (i) (среднее значение я) даст результат, отличный от результата СРЕДНЕЕ (j):

Здесь AVG (i) составляет 200 (в среднем 150, 200 и 250), а AVG (j) равно 150 (в среднем 150, 200, 250 и 0). Хорошо известным побочным эффектом этого является то, что в SQL AVG (z) эквивалентно not СУММ (z) / СЧЁТ (*) но СУММ (z) / СЧЁТ (z).^[4]

Выход агрегатной функции также может иметь значение NULL. Вот пример:

ВЫБРАТЬ СЧИТАТЬ(*), MIN(е.Заработная плата), МАКСИМУМ(е.Заработная плата)ИЗ Наемный рабочий еКУДА е.Фамилия ПОДОБНО '% Джонс%';

Этот запрос всегда будет выводить ровно одну строку, подсчитывая количество сотрудников, чья фамилия содержит «Джонс», и давая минимальную и максимальную заработную плату, найденную для этих сотрудников. Однако что произойдет, если ни один из сотрудников не соответствует заданным критериям? Вычислить минимальное или максимальное значение пустого набора невозможно, поэтому эти результаты должны иметь значение NULL, что означает отсутствие ответа. Это не значение Unknown, это Null, обозначающий отсутствие значения. Результат будет:

Когда два нуля равны: группировка, сортировка и некоторые операции над наборами

Потому что SQL: 2003 определяет все нулевые маркеры как не равные друг другу, требовалось специальное определение, чтобы сгруппировать нулевые маркеры вместе при выполнении определенных операций. SQL определяет «любые два значения, которые равны друг другу, или любые два значения Null» как «неотличимые».^[20] Это определение не отчетливый позволяет SQL группировать и сортировать пустые значения, когда ГРУППА ПО предложение (и другие ключевые слова, выполняющие группировку).

В других операциях, предложениях и ключевых словах SQL при обработке пустых значений используется «неотличимый». К ним относятся следующие:

• РАЗДЕЛЕНИЕ ПО раздел ранжирования и оконных функций, таких как ROW_NUMBER
• СОЮЗ, ПЕРЕСЕЧЕНИЕ, и КРОМЕ оператор, который обрабатывает NULL как одно и то же для сравнения / исключения строк
• ОТЧЕТЛИВЫЙ ключевое слово, используемое в ВЫБРАТЬ запросы

Принцип, что Nulls не равны друг другу (а скорее результат Unknown), эффективно нарушается в спецификации SQL для СОЮЗ оператор, который идентифицирует нули друг с другом.^[1] Следовательно, некоторые операции с множествами в SQL, такие как объединение или разность, могут давать результаты, не представляющие достоверную информацию, в отличие от операций, включающих явные сравнения с NULL (например, операции в КУДА пункт, обсуждаемый выше). В предложении Кодда 1979 года (которое в основном было принято SQL92) эта семантическая несогласованность рационализирована тем, что удаление дубликатов в операциях над наборами происходит «на более низком уровне детализации, чем проверка равенства при оценке операций поиска».^[10]

Стандарт SQL явно не определяет порядок сортировки по умолчанию для пустых значений. Вместо этого в соответствующих системах пустые значения могут быть отсортированы до или после всех значений данных с помощью ПЕРВЫЕ НУЛИ или же ПОСЛЕДНИЕ НУЛИ статьи СОРТИРОВАТЬ ПО list соответственно. Однако не все производители СУБД реализуют эту функцию. Поставщики, которые не реализуют эту функцию, могут указать различные методы сортировки Null в СУБД.^[18]

Влияние на работу индекса

Некоторые продукты SQL не индексируют ключи, содержащие NULL. Например, PostgreSQL версий до 8.3 не было, с документацией для B-дерево указатель, указывающий, что^[21]

B-деревья могут обрабатывать запросы на равенство и диапазоны данных, которые можно отсортировать в некотором порядке. В частности, планировщик запросов PostgreSQL рассмотрит возможность использования индекса B-дерева всякий раз, когда индексированный столбец участвует в сравнении с использованием одного из следующих операторов: <≤ = ≥>

Конструкции, эквивалентные комбинациям этих операторов, например BETWEEN и IN, также могут быть реализованы с помощью поиска по индексу B-дерева. (Но обратите внимание, что IS NULL не эквивалентно = и не индексируется.)

В случаях, когда индекс обеспечивает уникальность, значения NULL исключаются из индекса, а уникальность между значениями NULL не применяется. Опять же, цитируя PostgreSQL документация:^[22]

Когда индекс объявлен уникальным, несколько строк таблицы с равными индексированными значениями не допускаются. Нули не считаются равными. Уникальный индекс с несколькими столбцами отклоняет только те случаи, когда все проиндексированные столбцы равны в двух строках.

Это согласуется с SQL: 2003 -определенное поведение скалярных сравнений Null.

Другой метод индексации нулевых значений заключается в обработке их как не отчетливый в соответствии с поведением, определенным в SQL: 2003. Например, Microsoft SQL Server в документации указано следующее:^[23]

Для целей индексации NULL сравниваются как равные. Следовательно, уникальный индекс или ограничение UNIQUE не могут быть созданы, если ключи имеют значение NULL в более чем одной строке. Выберите столбцы, которые определены как NOT NULL, если выбраны столбцы для уникального индекса или ограничения уникальности.

Обе эти стратегии индексации согласуются с определенным в SQL: 2003 поведением Nulls. Поскольку методологии индексирования явно не определены стандартом SQL: 2003, разработка и реализация стратегий индексирования для нулевых значений полностью предоставлена ​​поставщикам.

Функции обработки нуля

SQL определяет две функции для явной обработки пустых значений: NULLIF и КОАЛЕС. Обе функции являются сокращениями для искал ДЕЛО выражения.^[24]

NULLIF

В NULLIF функция принимает два параметра. Если первый параметр равен второму параметру, NULLIF возвращает Null. В противном случае возвращается значение первого параметра.

NULLIF(значение1, значение2)

Таким образом, NULLIF это сокращение от следующего ДЕЛО выражение:

ДЕЛО КОГДА значение1 = значение2 ТОГДА НОЛЬ ЕЩЕ значение1 КОНЕЦ

КОАЛЕС

В КОАЛЕС Функция принимает список параметров, возвращая первое ненулевое значение из списка:

КОАЛЕС(значение1, значение2, значение3, ...)

КОАЛЕС определяется как сокращение для следующего SQL ДЕЛО выражение:

ДЕЛО КОГДА значение1 ЯВЛЯЕТСЯ НЕТ НОЛЬ ТОГДА значение1     КОГДА значение2 ЯВЛЯЕТСЯ НЕТ НОЛЬ ТОГДА значение2     КОГДА значение3 ЯВЛЯЕТСЯ НЕТ НОЛЬ ТОГДА значение3     ...     КОНЕЦ

Некоторые СУБД SQL реализуют специфичные для поставщика функции, аналогичные КОАЛЕС. Некоторые системы (например, Transact-SQL ) реализовать НУЛЕВОЙ функция или другие подобные функции, которые функционально похожи на КОАЛЕС. (Видеть Является функции для получения дополнительной информации о ЯВЛЯЕТСЯ функции в Transact-SQL.)

NVL

Оракул NVL функция принимает два параметра. Он возвращает первый параметр, отличный от NULL, или NULL, если все параметры равны NULL.

А КОАЛЕС выражение может быть преобразовано в эквивалент NVL выражение таким образом:

КОАЛЕС ( val1, ... , вал{п} )

превращается в:

NVL( val1 , NVL( val2 , NVL( val3 , … , NVL ( вал{п-1} , вал{п} ) … )))

Пример использования этой функции - заменить в выражении NULL значением, например NVL (ЗАРПЛАТА, 0) который говорит: «если ЗАРПЛАТА равно NULL, замените его значением 0 '.

Однако есть одно заметное исключение. В большинстве реализаций КОАЛЕС оценивает свои параметры, пока не достигнет первого, отличного от NULL, а NVL оценивает все его параметры. Это важно по нескольким причинам. Параметр после первым параметром, отличным от NULL, может быть функция, которая может быть дорогостоящей с точки зрения вычислений, недействительной или может создавать неожиданные побочные эффекты.

Типирование данных Null и Unknown

В НОЛЬ буквальный нетипизирован в SQL, что означает, что он не обозначен как целое число, символ или какой-либо другой конкретный тип данных.^[25] Из-за этого иногда обязательно (или желательно) явно преобразовывать Nulls в определенный тип данных. Например, если перегружен функции поддерживаются СУБД, SQL может быть не в состоянии автоматически преобразовать в правильную функцию без знания типов данных всех параметров, включая те, для которых передается Null.

Конверсия из НОЛЬ буквальное значение Null определенного типа возможно с помощью В РОЛЯХ введено в SQL-92. Например:

В РОЛЯХ (НОЛЬ В КАЧЕСТВЕ ЦЕЛОЕ)

представляет отсутствующее значение типа INTEGER.

Фактическая типизация Unknown (отличная от самого NULL или отличная от нее) варьируется в зависимости от реализации SQL. Например, следующие

ВЫБРАТЬ 'Ok' КУДА (НОЛЬ <> 1) ЯВЛЯЕТСЯ НОЛЬ;

анализирует и успешно выполняет в некоторых средах (например, SQLite или же PostgreSQL ), которые объединяют логическое значение NULL с Unknown, но не могут анализировать в других (например, в SQL Server Compact ). MySQL ведет себя аналогично PostgreSQL в этом отношении (за небольшим исключением, что MySQL считает ИСТИНА и ЛОЖЬ ничем не отличными от обычных целых чисел 1 и 0). PostgreSQL дополнительно реализует НЕИЗВЕСТНО предикат, который можно использовать для проверки того, является ли трехзначный логический результат Неизвестным, хотя это просто синтаксический сахар.

Тип данных BOOLEAN

ISO SQL: 1999 Стандарт представил в SQL тип данных BOOLEAN, однако он по-прежнему является необязательной, неосновной функцией, кодированной T031.^[26]

Когда ограничено НЕ НОЛЬ ограничение, SQL BOOLEAN работает как Логический тип с других языков. Однако без ограничений тип данных BOOLEAN, несмотря на свое название, может содержать истинные значения TRUE, FALSE и UNKNOWN, которые в соответствии со стандартом определены как логические литералы. Стандарт также утверждает, что NULL и UNKNOWN «могут использоваться взаимозаменяемо для обозначения одного и того же».^[27][28]

Тип Boolean подвергался критике, особенно из-за обязательного поведения литерала UNKNOWN, который никогда не равен самому себе из-за идентификации с NULL.^[29]

Как обсуждалось выше, в PostgreSQL реализация SQL, Null используется для представления всех результатов UNKNOWN, включая UNKNOWN BOOLEAN. PostgreSQL не реализует литерал UNKNOWN (хотя он реализует оператор IS UNKNOWN, который является ортогональной функцией). Большинство других крупных поставщиков не поддерживают логический тип (как определено в T031) с 2012 года.^[30] Процедурная часть Oracle PL / SQL поддерживает BOOLEAN, однако переменные; им также может быть присвоено значение NULL, и значение считается таким же, как UNKNOWN.^[31]

Полемика

Распространенные ошибки

Непонимание того, как работает Null, является причиной большого количества ошибок в коде SQL, как в стандартных операторах SQL ISO, так и в определенных диалектах SQL, поддерживаемых реальными системами управления базами данных. Эти ошибки обычно являются результатом путаницы между Null и 0 (нулем) или пустой строкой (строковое значение с нулевой длиной, представленное в SQL как ''). Null определяется стандартом SQL как отличное от пустой строки и числового значения. 0, тем не мение. В то время как Null указывает на отсутствие какого-либо значения, пустая строка и числовой ноль представляют фактические значения.

Классическая ошибка - это попытка использовать оператор равенства = в сочетании с ключевым словом НОЛЬ найти строки с пустыми значениями. Согласно стандарту SQL это недопустимый синтаксис и должен привести к сообщению об ошибке или исключению. Но большинство реализаций принимают синтаксис и оценивают такие выражения как НЕИЗВЕСТНЫЙ. Следствием этого является то, что строки не найдены - независимо от того, существуют ли строки с нулевыми значениями или нет. Предлагаемый способ получения строк с нулевыми значениями - это использование предиката НУЛЕВОЙ вместо = NULL.

ВЫБРАТЬ *ИЗ какой-нибудь столКУДА число = НОЛЬ;  - Должно быть "WHERE num IS NULL".

В связанном, но более тонком примере КУДА предложение или условный оператор могут сравнивать значение столбца с константой. Часто ошибочно предполагается, что отсутствующее значение будет «меньше» или «не равно» константе, если это поле содержит Null, но на самом деле такие выражения возвращают значение Unknown. Пример ниже:

ВЫБРАТЬ *ИЗ какой-нибудь столКУДА число <> 1;  - Строки, где num равно NULL, не возвращаются,                 - вопреки ожиданиям многих пользователей.

Эти недоразумения возникают из-за того, что Закон идентичности ограничен в логике SQL. При работе со сравнениями на равенство с использованием НОЛЬ буквальный или НЕИЗВЕСТНЫЙ значение истины, SQL всегда будет возвращать НЕИЗВЕСТНЫЙ как результат выражения. Это отношение частичной эквивалентности и делает SQL примером Нерефлексивная логика.^[32]

Точно так же пустые значения часто путают с пустыми строками. Рассмотрим ДЛИНА функция, которая возвращает количество символов в строке. Когда в эту функцию передается Null, функция возвращает Null. Это может привести к неожиданным результатам, если пользователи плохо разбираются в трехзначной логике. Пример ниже:

ВЫБРАТЬ *ИЗ какой-нибудь столКУДА ДЛИНА(нить) < 20; - Строки, в которых строка имеет значение NULL, не возвращаются.

Это осложняется тем фактом, что в некоторых программах интерфейса баз данных (или даже в реализациях баз данных, таких как Oracle), NULL отображается как пустая строка, а пустые строки могут быть неправильно сохранены как NULL.

Критика

Реализация Null в ISO SQL является предметом критики, споров и призывов к изменениям. В Реляционная модель для управления базами данных: версия 2, Кодд предположил, что реализация Null в SQL ошибочна и должна быть заменена двумя разными маркерами нулевого типа. Предложенные им маркеры должны были обозначать «Отсутствует, но применимо» и «Отсутствует, но неприменимо», известный как A-ценности и I-ценности, соответственно. Рекомендация Кодда, если бы она была принята, потребовала бы реализации четырехзначной логики в SQL.^[5] Другие предложили добавить дополнительные маркеры нулевого типа к рекомендации Кодда, чтобы указать еще больше причин, по которым значение данных может быть «отсутствующим», увеличивая сложность логической системы SQL. В разное время также выдвигались предложения по реализации нескольких определяемых пользователем маркеров Null в SQL. Из-за сложности систем обработки нулей и логических систем, необходимых для поддержки нескольких нулевых маркеров, ни одно из этих предложений не получило широкого признания.

Крис Дата и Хью Дарвен, авторы Третий манифест, предположили, что реализация SQL Null изначально ошибочна и должна быть полностью устранена,^[33] указывая на несоответствия и недостатки в реализации обработки SQL Null (особенно в агрегатных функциях) как доказательство того, что вся концепция Null ошибочна и должна быть удалена из реляционной модели.^[34] Другие, как автор Фабиан Паскаль, заявили, что «то, как вычисление функции должно обрабатывать пропущенные значения, не регулируется реляционной моделью».^{[нужна цитата ]}

Предположение о замкнутом мире

Еще один конфликт, связанный с нулевыми значениями, заключается в том, что они нарушают предположение о замкнутом мире модель реляционных баз данных путем введения предположение об открытом мире внутрь.^[35] Предположение о закрытом мире, относящееся к базам данных, гласит: «Все, что указано в базе данных, явно или неявно, верно; все остальное - ложно».^[36] Этот взгляд предполагает, что знание мира, хранящееся в базе данных, является полным. Нулевые значения, однако, действуют в предположении открытого мира, в котором некоторые элементы, хранящиеся в базе данных, считаются неизвестными, что делает хранимые в базе данных сведения о мире неполными.

Смотрите также

• SQL
• NULL в: Wikibook SQL
• Учебник D
• Тернарная логика
• Язык манипулирования данными
• 12 правил Кодда
• Проверить ограничение
• Реляционная модель / Тасмания
• Система управления реляционной базой данных
• Присоединиться (SQL)
• Третий манифест

Рекомендации

дальнейшее чтение

• Э. Ф. Кодд. Понимание отношений (часть 7). Бюллетень FDT ACM-SIGMOD, 7 (3-4): 23–28, 1975.
• Кодд, Э. Ф. (1979). «Расширение реляционной модели базы данных для получения большего смысла». Транзакции ACM в системах баз данных. 4 (4): 397–434. CiteSeerX  10.1.1.508.5701. Дои:10.1145/320107.320109. Особенно §2.3.
• Дата, C.J. (2000). Реляционная модель базы данных: ретроспективный обзор и анализ: исторический отчет и оценка вклада Э. Ф. Кодда в область технологий баз данных. Эддисон Уэсли Лонгман. ISBN  978-0-201-61294-3.
• Кляйн, Ханс-Иоахим (1994). «Как изменить SQL-запросы, чтобы гарантировать точные ответы». Запись ACM SIGMOD. 23 (3): 14–20. Дои:10.1145/187436.187445.
• Клод Рубинсон, Нули, трехзначная логика и двусмысленность в SQL: критика критики Дейта, SIGMOD Record, декабрь 2007 г. (Том 36, № 4)
• Джон Грант, Нулевые значения в SQL. SIGMOD Record, сентябрь 2008 г. (Том 37, № 3)
• Варапорн, Наронгрит и Криенгкрай Поркаев. "Нулевая семантика для подзапросов и атомарных предикатов ". IAENG Международный журнал компьютерных наук 35.3 (2008): 305-313.
• Бернхард Тальхейм, Клаус-Дитер Шеве (2011). "NULL" Value "Алгебры и логики". Границы в области искусственного интеллекта и приложений. 225 (Информационное моделирование и базы знаний XXII). Дои:10.3233/978-1-60750-690-4-354.CS1 maint: использует параметр авторов (связь)
• Энрико Франкони и Серджио Тессарис, О логике пустых значений SQL, Труды 6-го Международного семинара Альберто Мендельзона по основам управления данными, Ору-Прету, Бразилия, 27–30 июня 2012 г. С. 114–128

внешняя ссылка

• Oracle NULL
• Третий манифест
• Влияние NULL на последовательность данных
• Отчет об ошибке Java о том, что jdbc не различает пустую и пустую строку, что Sun закрыла как «не ошибка»