Нулевой (SQL)

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

Null или NULL - это специальный маркер, используемый в языке структурированных запросов, чтобы указать, что значение данных не существует в базе данных . Представленный создателем модели реляционной базы данных, EF Codd , SQL Null служит для выполнения требования, чтобы все настоящие системы управления реляционными базами данных ( RDMS ) поддерживали представление «недостающей и неприменимой информации». Кодд также ввел использование греческого символа омеги (ω) в нижнем регистре для обозначения нуля в теории баз данных . В SQL NULLэто зарезервированное слово, используемое для идентификации этого маркера.

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

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

История [ править ]

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

SQL стандарт 1986 в основном принял предложение Кодда после прототипа внедрения в IBM System R . Хотя Дон Чемберлин считал пустые значения (наряду с повторяющимися строками) одной из самых спорных особенностей SQL, он защищал конструкцию пустых значений в SQL, ссылаясь на прагматические аргументы, что это была наименее дорогостоящая форма поддержки системы для недостающей информации, избавляющая программиста от множество дублирующих проверок на уровне приложения (см. проблему полупредиката ), в то же время предоставляя разработчику базы данных возможность не использовать значения 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 на ноль, платформы могут возвращать Null вместо того, чтобы генерировать ожидаемое «исключение данных - деление на ноль». ^[6] Хотя это поведение не определено стандартом ISO SQL, многие поставщики СУБД относятся к этой операции аналогичным образом. Например, платформы Oracle, PostgreSQL, MySQL Server и Microsoft SQL Server возвращают нулевой результат для следующего:

NULL  /  0

Объединение строк [ править ]

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

«Рыба»  ||  NULL  ||  'Chips'  - результат NULL

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

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

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

SELECT  10  =  NULL  - результат неизвестен

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

SELECT  NULL  OR  TRUE  - результат будет True

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

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

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

Трехзначная логика SQL встречается в языке обработки данных (DML) в предикатах сравнения операторов и запросов DML. Предложение WHEREзаставляет оператор DML воздействовать только на те строки, для которых предикат оценивается как True. Ряды , для которых предикат принимает либо False или Unknown не с действием INSERT, UPDATEили DELETEзаявления DML, и отбрасываются по SELECTзапросам. Интерпретация Неизвестного и Ложного как одного и того же логического результата - распространенная ошибка, возникающая при работе с пустыми значениями. ^[9] Следующий простой пример демонстрирует эту ошибку:

ВЫБРАТЬ  * ИЗ  t WHERE  i  =  NULL ;

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

Предикаты сравнения, специфичные для NULL и 3VL [ править ]

Базовые операторы сравнения SQL всегда возвращают значение Unknown при сравнении чего-либо с Null, поэтому стандарт SQL предусматривает два специальных предиката сравнения, зависящих от Null. Этот IS NULLи IS NOT NULLпредикат (которые используют постфиксный синтаксис) проверить , являются ли данные или нет, NULL. ^[11]

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

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

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

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

Закон исключенного четвертого (в предложениях WHERE) [ править ]

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

ВЫБРАТЬ  *  ИЗ  материала  WHERE  (  x  =  10  )  OR  NOT  (  x  =  10  );

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

ВЫБРАТЬ  *  ИЗ  материала ;

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

ВЫБРАТЬ  *  ИЗ  материала ; - (из-за 3VL) эквивалентно: SELECT  *  FROM  stuff  WHERE  (  x  =  10  )  OR  NOT  (  x  =  10  )  OR  x  IS  NULL ;

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

ВЫБЕРИТЕ  *  ИЗ  материала  WHERE  x  IS  NOT  NULL ;

Ввиду вышеизложенного обратите внимание, что для предложения WHERE в SQL может быть написана тавтология, аналогичная закону исключенного среднего. Предполагая, что оператор IS UNKNOWN присутствует, p OR (NOT p ) OR ( p IS UNKNOWN) истинно для каждого предиката p . Среди логиков это называется законом исключенного четвертого .

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

ВЫБРАТЬ  'ОК'  ГДЕ  1  НЕ  В  ( ВЫБРАТЬ  ПРИВОД  ( NULL  КАК  ЦЕЛОЕ )) СОЕДИНЕНИЕ ВЫБРАТЬ  'ОК'  ГДЕ  1  В  ( ВЫБРАТЬ  ПРИОБРЕТЕНИЕ  ( NULL  КАК  Целое число ));

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

Выберите  'OK'  ГДЕ  ( 1  В  ( SELECT ,  CAST  ( NULL  AS  INTEGER )))  IS  НЕИЗВЕСТНА ;

Эффект Null и Unknown в других конструкциях [ править ]

Присоединяется [ править ]

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

COALESCEФункция или CASEвыражения SQL могут использоваться для «имитации» нулевого равенства в критериях соединения, IS NULLа IS NOT NULLпредикаты и также могут использоваться в критериях соединения. Следующий предикат проверяет равенство значений A и B и рассматривает Null как равные.

( = В ) ИЛИ ( IS NULL и B IS NULL )          

Выражения CASE [ править ]

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

Простые CASEвыражения используют неявные сравнения на равенство, которые работают по тем же правилам, что и WHEREправила предложения DML для Null. Таким образом, простое CASEвыражение не может напрямую проверить наличие Null. Проверка на Null в простом CASEвыражении всегда приводит к Unknown, как показано ниже:

SELECT  CASE  i  WHEN  NULL  THEN  'Is Null'  - никогда не будет возвращено  КОГДА  0  ТО  'Is Zero'  - это будет возвращено, когда i = 0,  КОГДА  1  ТО  'Is One'  - это будет возвращено, когда i = 1  КОНЕЦ ОТ  t ;

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

С другой стороны, в «искомом» CASEвыражении могут использоваться такие предикаты, как IS NULLи IS NOT NULLв его условиях. В следующем примере показано, как использовать искомое CASEвыражение для правильной проверки на Null:

ВЫБЕРИТЕ  СЛУЧАЙ,  КОГДА  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 Oracle предоставляет встроенную функцию, DECODEкоторая может использоваться вместо простых выражений CASE и считает два значения NULL равными.

ВЫБРАТЬ  DECODE ( i ,  NULL ,  'Null Result' ,  0 ,  'Zero' ,  1 ,  'One' )  FROM  t ;

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

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

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

ЕСЛИ  i  =  NULL  THEN  SELECT  'Результат истинный' ELSEIF  NOT ( i  =  NULL )  THEN  SELECT  'Результат False' ELSE  SELECT  'Результат неизвестен' ;

В IFзаявлении выполняет действия только для тех сравнений , которые оценивают Истину. Для операторов, которые оцениваются как False или Unknown, IFоператор передает управление ELSEIFпредложению и, наконец, ELSEпредложению. Результатом приведенного выше кода всегда будет сообщение, 'Result is Unknown'поскольку сравнение с Null всегда оценивается как Unknown.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВЫБРАТЬ  Имя  ИЗ  Emp  WHERE  Age  =  22 UNION ВЫБРАТЬ  Имя  FROM  Emp  WHERE  Age  <>  22 ;

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

Таким образом, когда в язык запросов добавляются объединения, таблицы Кодда даже не являются слабой системой представления недостающей информации, а это означает, что запросы по ним даже не сообщают всю достоверную информацию. Здесь важно отметить, что семантика UNION on Nulls, которая обсуждается в следующем разделе, даже не участвовала в этом запросе. «Забывчивый» характер двух подзапросов - это все, что требовалось, чтобы гарантировать, что некоторая достоверная информация не будет сообщена, когда вышеуказанный запрос был запущен в таблице Кодда 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 WHERE. В то время как WHEREпредложение DML должно оцениваться как True для строки, контрольное ограничение не должно оцениваться как False. (С логической точки зрения обозначенными значениями являются Истина и Неизвестно.) Это означает, что ограничение проверки будет успешным, если результат проверки будет Истинным или Неизвестным. Следующая таблица с ограничением проверки запрещает вставку любых целочисленных значений в столбец i., но позволит вставить Null, поскольку результат проверки всегда будет оцениваться как Unknown for Nulls. ^[18]

CREATE  TABLE  T  (  я  INTEGER ,  CONSTRAINT  ck_i  ПРОВЕРКА  (  я  <  0  и  я  =  0  и  я  >  0  )  );

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

Чтобы запретить столбцу отклонять значения NULL, NOT NULLможно применить ограничение, как показано в примере ниже. NOT NULLОграничение семантический эквивалентно проверочное ограничение с IS NOT NULLпредикатом.

СОЗДАТЬ  ТАБЛИЦУ  t  (  i  INTEGER  NOT  NULL  );

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

СОЗДАТЬ  ТАБЛИЦУ  Книги (  название  VARCHAR ( 100 ),  author_last  VARCHAR ( 20 ),  author_first  VARCHAR ( 20 ), FOREIGN  KEY  ( author_last ,  author_first )  ССЫЛКИ  Авторы ( last_name ,  first_name ));

позволит вставлять строки, в которых author_last или author_first имеют значение NULL, независимо от того, как таблица Authors определена или что она содержит. Точнее, пустое значение в любом из этих полей допускает любое значение в другом, даже если оно не найдено в таблице авторов. Например, если авторы содержат только ('Doe', 'John'), то ('Smith', NULL) удовлетворяет ограничению внешнего ключа. SQL-92 добавил две дополнительные опции для сужения совпадений в таких случаях. Если MATCH PARTIALон добавлен после REFERENCESобъявления, то любое ненулевое значение должно соответствовать внешнему ключу, например ('Doe', NULL) все равно будет совпадать, а ('Smith', NULL) - нет. Наконец, если MATCH FULLдобавлено, то ('Smith', NULL) также не будет соответствовать ограничению, но (NULL, NULL) все равно будет соответствовать ему.

Внешние соединения [ править ]

Внешние соединения SQL , включая левые внешние соединения, правые внешние соединения и полные внешние соединения, автоматически создают пустые значения в качестве заполнителей для отсутствующих значений в связанных таблицах. Для левых внешних объединений, например, пустые значения создаются вместо строк, отсутствующих в таблице, появляющейся справа от LEFT OUTER JOINоператора. В следующем простом примере используются две таблицы для демонстрации создания пустого заполнителя в левом внешнем соединении.

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

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

ВЫБРАТЬ  e . ID ,  эл . Фамилия ,  e . FirstName ,  pn . Количество FROM  Employee  е ЛЕВЫЙ  OUTER  JOIN  PhoneNumber  р ON  е . ID  =  pn . ID ;

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

Агрегатные функции [ править ]

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

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

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

Результатом агрегатной функции также может быть NULL. Вот пример:

ВЫБРАТЬ  COUNT ( * ),  MIN ( е . Наемный ),  MAX ( е . Заработная плата ) FROM  Employee  е ГДЕ  е . Фамилия  LIKE  '% Jones%' ;

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

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

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

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

• PARTITION BY пункт ранжирования и оконных функций, таких как ROW_NUMBER
• UNION, INTERSECTИ EXCEPTоператор, который относиться к NULLs , как то же самое для сравнения целей строк / ликвидации
• DISTINCTключевое слово, используемое в SELECTзапросах

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

Стандарт SQL явно не определяет порядок сортировки по умолчанию для значений NULL. Вместо этого, на соответствующих системах, Нули могут быть отсортированы до или после того, как все значения данных с помощью NULLS FIRSTили NULLS LASTположения в ORDER BYсписке, соответственно. Однако не все производители СУБД реализуют эту функцию. Поставщики, которые не реализуют эту функциональность, могут указать различные способы обработки нулевой сортировки в СУБД. ^[18]

Влияние на операцию индекса [ править ]

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

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

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

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

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

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

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

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

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

Функции обработки нуля [ править ]

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

NULLIF [ править ]

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

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

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

CASE  WHEN  value1  =  value2  THEN  NULL  ELSE  value1  END

КОАЛЕС [ править ]

COALESCEФункция принимает список параметров, возвращая первое значение не равно нулю из списка:

COALESCE ( значение1 ;  значение2 ;  значение3 ;  ...)

COALESCEопределяется как сокращение для следующего CASEвыражения SQL :

СЛУЧАЙ ,  КОГДА  value1  IS  NOT  NULL  THEN  значение1  WHEN  значение2  IS  NOT  NULL  THEN  значение2  КОГДА  value3  IS  NOT  NULL  THEN  value3  ...  END

Некоторые СУБД SQL реализуют специфичные для поставщика функции, аналогичные COALESCE. Некоторые системы (например, Transact-SQL ) реализуют ISNULLфункцию или другие подобные функции, которые функционально похожи на COALESCE. (Дополнительные сведения о функциях в Transact-SQL см. В разделе « IsФункции»IS .)

NVL [ править ]

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

COALESCEВыражение может быть преобразовано в эквивалентное NVLвыражение таким образом:

COALESCE  (  val1 ,  ...  ,  Вэл { п }  )

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

NVL (  val1  ,  NVL (  val2  ,  NVL (  val3  ,  ...  ,  NVL  (  Вэл { п - 1 }  ,  Вэл { п }  )  ...  )))

Пример использования этой функции - заменить в выражении NULL значением, например, в NVL(SALARY, 0)котором говорится: «Если SALARYNULL, замените его значением 0».

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

Тип данных Null и Unknown [ править ]

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

Преобразование NULLлитерала в Null определенного типа возможно с использованием CASTвведенного в SQL-92 . Например:

CAST  ( NULL  AS  INTEGER )

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

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

ВЫБЕРИТЕ  'ok'  WHERE  ( NULL  <>  1 )  IS  NULL ;

анализирует и успешно выполняет в некоторых средах (например, SQLite или PostgreSQL ), которые объединяют логическое значение NULL с Неизвестным, но не удается выполнить синтаксический анализ в других (например, в SQL Server Compact ). В этом отношении MySQL ведет себя аналогично PostgreSQL (за небольшим исключением, что MySQL считает TRUE и FALSE ничем не отличными от обычных целых чисел 1 и 0). PostgreSQL дополнительно реализует IS UNKNOWNпредикат, который можно использовать для проверки того, является ли трехзначный логический результат Неизвестным, хотя это всего лишь синтаксический сахар.

Тип данных BOOLEAN [ править ]

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

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

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

Как обсуждалось выше, в реализации SQL PostgreSQL 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 указывает на отсутствие какого-либо значения, пустая строка и числовой ноль представляют фактические значения.

Классическая ошибка - это попытка использовать оператор равенства =в сочетании с ключевым словом NULLдля поиска строк с нулевыми значениями. Согласно стандарту SQL это недопустимый синтаксис и приведет к сообщению об ошибке или исключению. Но большинство реализаций принимают синтаксис и оценивают такие выражения как UNKNOWN. Следствием этого является то, что строки не найдены - независимо от того, существуют ли строки с нулевыми значениями или нет. Предлагаемый способ получения строк с нулевыми значениями - использование предиката IS NULLвместо = NULL.

SELECT  * FROM  sometable WHERE  num  =  NULL ;  - Должно быть "WHERE num IS NULL".

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

SELECT  * FROM  sometable WHERE  num  <>  1 ;  - Строки, где num равно NULL, не будут возвращены,  - вопреки ожиданиям многих пользователей.

Эти недоразумения возникают из-за того, что закон идентичности ограничен логикой SQL. При работе со сравнениями на равенство с использованием NULLлитерала или значения UNKNOWNистинности SQL всегда будет возвращать UNKNOWNрезультат выражения. Это отношение частичной эквивалентности делает SQL примером нерефлексивной логики . ^[32]

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

SELECT  * FROM  sometable WHERE  LENGTH ( строка )  <  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)
• Третий манифест

Ссылки [ править ]

Дальнейшее чтение [ править ]

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

Внешние ссылки [ править ]

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