В SQL нуль или NULL — это специальный маркер, используемый для обозначения того, что значение данных не существует в базе данных . Введенный создателем модели реляционной базы данных Э. Ф. Коддом , SQL null служит для выполнения требования, чтобы все настоящие системы управления реляционными базами данных ( СУБД ) поддерживали представление «недостающей информации и неприменимой информации». Кодд также ввел в теорию баз данных использование строчной греческой омеги (ω) для обозначения нуля . В SQL это зарезервированное слово, используемое для идентификации этого маркера.NULL
Не следует путать значение NULL со значением . Нуль указывает на отсутствие значения, что не то же самое, что нулевое значение. Например, рассмотрим вопрос «Сколько книг у Адама?» Ответ может быть «ноль» (мы знаем , что у него нет ни одного ) или «ноль» (мы не знаем, сколько у него есть). В таблице базы данных столбец , сообщающий об этом ответе, будет начинаться со значения без значения (отмечен нулем) и не будет обновляться нулевым значением до тех пор, пока не будет установлено, что у Адама нет книг.
В SQL значение null является маркером, а не значением. Такое использование сильно отличается от большинства языков программирования, где нулевое значение ссылки означает, что она не указывает ни на какой объект .
Э. Ф. Кодд упомянул нули как метод представления недостающих данных в реляционной модели в статье 1975 года, опубликованной в бюллетене FDT ACM - SIGMOD . Статья Кодда, которая чаще всего цитируется в связи с семантикой Null (принятой в SQL), — это его статья 1979 года в журнале ACM Transactions on Database Systems , в которой он также представил свою реляционную модель/Tasmania , хотя многие другие предложения из последняя статья осталась неясной. В разделе 2.3 его статьи 1979 года подробно описана семантика распространения нулей в арифметических операциях, а также сравнения, использующие троичную (трехзначную) логику при сравнении с нулями; в нем также подробно описывается обработка нулей в других операциях над множествами (последний вопрос до сих пор остается спорным). В кругах теории баз данных первоначальное предложение Кодда (1975, 1979) теперь называется «таблицами Кодда». [1] Позже Кодд в статье, состоящей из двух частей, опубликованной в журнале Computerworld в 1985 году, подтвердил свое требование о том, чтобы все СУБД поддерживали Null для указания недостающих данных . [2] [3]
Стандарт SQL 1986 года в основном принял предложение Кодда после прототипа реализации в IBM System R. Хотя Дон Чемберлин признавал нули (наряду с повторяющимися строками) одной из наиболее спорных особенностей SQL, он защищал дизайн пустых значений в SQL, ссылаясь на прагматичные аргументы, что это наименее дорогая форма системной поддержки недостающей информации, избавляющая программиста от множество дублирующих проверок на уровне приложения (см. проблему полупредикатов ), в то же время предоставляя разработчику базы данных возможность не использовать значения Null, если они того пожелают; например, чтобы избежать хорошо известных аномалий (обсуждаемых в разделе семантики этой статьи). Чемберлин также утверждал, что, помимо обеспечения некоторой функциональности недостающих значений, практический опыт работы с нулями также привел к появлению других функций языка, основанных на нулях, таких как определенные конструкции группировки и внешние соединения. Наконец, он утверждал, что на практике нули также используются как быстрый способ исправить существующую схему , когда она должна выйти за рамки своего первоначального предназначения, кодируя не недостающую, а скорее неприменимую информацию; например, база данных, которая должна быстро поддерживать электромобили, имея при этом столбец миль на галлон. [4]
Кодд указал в своей книге «Реляционная модель управления базами данных, версия 2» 1990 года, что одиночный нуль, предусмотренный стандартом SQL, недостаточен и должен быть заменен двумя отдельными маркерами нулевого типа, чтобы указать причину отсутствия данных. В книге Кодда эти два маркера нулевого типа называются «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 возвращают нулевой результат для следующих значений:
НУЛЬ / 0 Операции конкатенации строк , распространенные в SQL, также приводят к значению NULL, если один из операндов имеет значение NULL. [7] В следующем примере демонстрируется результат Null, возвращаемый при использовании Null с ||оператором конкатенации строк SQL.
'Рыба' || НУЛЬ || «Чипы» — результат NULL Это справедливо не для всех реализаций баз данных. В СУБД Oracle, например, NULL и пустая строка считаются одним и тем же, и поэтому 'Fish' || НУЛЬ || «Чипсы» приводят к «рыбным чипсам». [8]
Поскольку Null не является членом какой-либо области данных , он считается не «значением», а скорее маркером (или заполнителем), указывающим неопределенное значение . По этой причине сравнение с Null никогда не может привести ни к True, ни к False, а всегда к третьему логическому результату — Unknown. [9] Логический результат приведенного ниже выражения, которое сравнивает значение 10 с нулевым значением, — «Неизвестно»:
SELECT 10 = NULL — результат неизвестен Однако некоторые операции с Null могут возвращать значения, если отсутствующее значение не имеет отношения к результату операции. Рассмотрим следующий пример:
SELECT NULL OR TRUE — результат True В этом случае тот факт, что значение слева от ИЛИ неизвестно, не имеет значения, поскольку результат операции ИЛИ будет истинным независимо от значения слева.
SQL реализует три логических результата, поэтому реализации SQL должны обеспечивать специализированную трехзначную логику (3VL) . Правила, управляющие трехзначной логикой SQL, показаны в таблицах ниже ( p и q представляют логические состояния)» [10]. Таблицы истинности, используемые SQL для И, ИЛИ и НЕ, соответствуют общему фрагменту трехзначной таблицы Клини и Лукасевича. значная логика (которые различаются определением импликации, однако SQL не определяет такую операцию) [11] .
Трехзначная логика SQL встречается в языке манипулирования данными (DML) в предикатах сравнения операторов и запросов DML. Это WHEREпредложение заставляет оператор DML действовать только над теми строками, для которых предикат имеет значение True. Строки, для которых предикат имеет значение False или Unknown, не обрабатываются операторами INSERT, UPDATEили DELETEDML и отбрасываются SELECTзапросами. Интерпретация Unknown и False как одного и того же логического результата является распространенной ошибкой, возникающей при работе с нулями. [10] Следующий простой пример демонстрирует это заблуждение:
SELECT * FROM t WHERE i = NULL ; Приведенный выше пример запроса логически всегда возвращает ноль строк, поскольку сравнение столбца i с Null всегда возвращает Unknown, даже для тех строк, где i равно Null. Результат «Неизвестно» приводит к тому, SELECTчто оператор суммарно отбрасывает каждую строку. (Однако на практике некоторые инструменты SQL извлекают строки, используя сравнение с Null.)
Базовые операторы сравнения SQL всегда возвращают Unknown при сравнении чего-либо с Null, поэтому стандарт SQL предусматривает два специальных предиката сравнения, специфичных для Null. Предикаты IS NULLи IS NOT NULL(которые используют постфиксный синтаксис) проверяют, являются ли данные нулевыми или нет. [12]
Стандарт SQL содержит дополнительную функцию F571 «Проверка истинностного значения», которая вводит три дополнительных логических унарных оператора (фактически шесть, если считать их отрицание, которое является частью их синтаксиса), также использующих постфиксную нотацию. У них есть следующие таблицы истинности: [13]
Функция F571 ортогональна наличию логического типа данных в SQL (обсуждаемому далее в этой статье) и, несмотря на синтаксическое сходство, F571 не вводит в язык логические или трехзначные литералы . Функция F571 фактически присутствовала в SQL92 [14] задолго до того , как логический тип данных был введен в стандарт в 1999 году. Однако функция F571 реализована немногими системами; PostgreSQL — один из тех, кто его реализует.
Добавление IS UNKNOWN к другим операторам трехзначной логики SQL делает трехзначную логику SQL функционально завершенной , [15] что означает, что ее логические операторы могут выражать (в комбинации) любую мыслимую трехзначную логическую функцию.
В системах, которые не поддерживают функцию F571, можно эмулировать IS UNKNOWN p , просматривая каждый аргумент, который может сделать выражение p Unknown, и проверять эти аргументы с помощью IS NULL или других функций, специфичных для NULL, хотя это может быть более громоздкий.
В трехзначной логике SQL закон исключенного третьего , p ИЛИ НЕ p , больше не считается истинным для всех p . Точнее, в трехзначной логике SQL p OR NOT p неизвестен именно тогда, когда p неизвестен, и истинен в противном случае. Поскольку прямое сравнение с Null приводит к неизвестному логическому значению, следующий запрос
ВЫБРАТЬ * ИЗ материала ГДЕ ( x = 10 ) ИЛИ НЕ ( x = 10 ); не эквивалентно в SQL с
ВЫБРАТЬ * ИЗ материала ; если столбец x содержит нули; в этом случае второй запрос вернет некоторые строки, которые не возвращает первый, а именно все те, в которых x имеет значение Null. В классической двузначной логике закон исключенного третьего позволил бы упростить предикат предложения WHERE, фактически его устранить. Попытка применить закон исключенного третьего к 3VL SQL фактически является ложной дихотомией . Второй запрос фактически эквивалентен:
ВЫБРАТЬ * ИЗ материала ; -- (из-за 3VL) эквивалентно: SELECT * FROM материал WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL ; Таким образом, для правильного упрощения первого оператора SQL необходимо вернуть все строки, в которых x не равен нулю.
SELECT * FROM материал , где x НЕ NULL ; Учитывая вышеизложенное, заметим, что для предложения SQL WHERE можно записать тавтологию, подобную закону исключенного третьего. Предполагая, что присутствует оператор IS UNKNOWN, p OR (NOT p ) OR ( p IS UNKNOWN) истинен для каждого предиката p . Среди логиков это называется законом исключенной четвертой .
Есть некоторые выражения SQL, в которых менее очевидно, где возникает ложная дилемма, например:
SELECT 'ok' WHERE 1 NOT IN ( SELECT CAST ( NULL AS INTEGER )) UNION SELECT 'ok' WHERE 1 IN ( SELECT CAST ( NULL AS INTEGER )); не создает строк, поскольку INпреобразуется в повторяющуюся версию равенства набора аргументов, а 1<>NULL является неизвестным, так же как 1=NULL является неизвестным. (CAST в этом примере необходим только в некоторых реализациях SQL, таких как PostgreSQL, которые в противном случае отклонят его с ошибкой проверки типа. Во многих системах в подзапросе работает простой SELECT NULL.) Отсутствующий случай выше, конечно, таков:
SELECT 'ok' WHERE ( 1 IN ( SELECT CAST ( NULL AS INTEGER ))) IS UNKNOWN ; Объединения оцениваются с использованием тех же правил сравнения, что и для предложений WHERE. Поэтому необходимо соблюдать осторожность при использовании столбцов, допускающих значение NULL, в критериях соединения SQL. В частности, таблица, содержащая любые значения NULL, не равна естественному самообъединению самой себя. Это означает, что, хотя это верно для любого отношения R в реляционной алгебре , самообъединение SQL исключит все строки, имеющие где-либо значение NULL. [16] Пример такого поведения приведен в разделе, посвященном анализу семантики пропущенных значений Nulls.
COALESCEФункцию или выражения SQL CASEможно использовать для «имитации» равенства Null в критериях соединения, а также в критериях соединения можно использовать предикаты и IS NULL. IS NOT NULLСледующий предикат проверяет равенство значений A и B и считает значения NULL равными.
( A = B ) ИЛИ ( A ЕСТЬ NULL И B IS NULL ) SQL предоставляет два варианта условных выражений . Один из них называется «простым CASE» и работает как оператор переключения . Другой в стандарте называется «search CASE» и работает как if...elseif .
В простых CASEвыражениях используются неявные сравнения на равенство, которые действуют по тем же правилам, что и WHEREправила предложения DML для Null. Таким образом, простое CASEвыражение не может напрямую проверить наличие Null. Проверка значения Null в простом CASEвыражении всегда приводит к результату Unknown, как показано ниже:
SELECT CASE i WHEN NULL THEN 'Is Null' -- Это значение никогда не будет возвращено WHEN 0 THEN 'Is Zero' -- Оно будет возвращено, когда i = 0 WHEN 1 THEN 'Is One' -- Это будет возвращено, когда i = 1 КОНЕЦ ОТ t ; Поскольку выражение i = NULLоценивается как Неизвестно независимо от того, какое значение содержит столбец i (даже если оно содержит значение Null), строка 'Is Null'никогда не будет возвращена.
С другой стороны, «искомое» CASEвыражение может использовать в своих условиях предикаты типа IS NULLи IS NOT NULL. В следующем примере показано, как использовать искомое CASEвыражение для правильной проверки значения Null:
SELECT CASE WHEN i IS NULL THEN 'Null Result' -- Это будет возвращено, когда i равно NULL WHEN i = 0 THEN 'Zero' -- Это будет возвращено, когда i = 0 WHEN i = 1 THEN 'One' -- Это будет возвращен, когда i = 1 END FROM t ; В искомом CASEвыражении строка 'Null Result'возвращается для всех строк, в которых i имеет значение NULL.
Диалект SQL Oracle предоставляет встроенную функцию DECODE, которую можно использовать вместо простых выражений CASE и считает два нуля равными.
SELECT DECODE ( i , NULL , «Нулевой результат» , 0 , «Ноль» , 1 , «Один» ) FROM t ; Наконец, все эти конструкции возвращают NULL, если совпадение не найдено; у них есть ELSE NULLпункт по умолчанию.
SQL/PSM (постоянные хранимые модули SQL) определяет процедурные расширения для SQL, такие как IFоператор. Однако основные поставщики SQL исторически включали свои собственные процедурные расширения. Процедурные расширения для циклов и сравнений работают в соответствии с правилами сравнения значений Null, аналогичными правилам для операторов и запросов DML. Следующий фрагмент кода в стандартном формате ISO SQL демонстрирует использование Null 3VL в IFинструкции.
IF i = NULL THEN SELECT «Результат верен» ELSEIF NOT ( i = NULL ) THEN SELECT «Результат ложен» ELSE SELECT «Результат неизвестен» ; Оператор IFвыполняет действия только для тех сравнений, которые оцениваются как True. Для операторов, которые оцениваются как False или Unknown, IFоператор передает управление предложению ELSEIFи, наконец, этому ELSEпредложению. Результатом приведенного выше кода всегда будет сообщение, 'Result is Unknown'поскольку сравнение с Null всегда дает значение Unknown.
Новаторская работа Т. Имелинского и В. Липского-младшего (1984) [17] предоставила основу для оценки предполагаемой семантики различных предложений по реализации семантики пропущенных значений, которая называется алгеброй Имелинского-Липского . Этот раздел примерно соответствует главе 19 учебника «Алиса». [18] Аналогичная презентация появляется в обзоре Рона ван дер Мейдена, §10.4. [1]
Конструкции, представляющие недостающую информацию, такие как таблицы Кодда, на самом деле предназначены для представления набора отношений, по одному для каждого возможного экземпляра их параметров; в случае таблиц Кодда это означает замену нулей каким-то конкретным значением. Например,
Конструкция (такая как таблица Кодда) называется сильной системой представления (недостающей информации), если любой ответ на запрос, сделанный на основе конструкции, может быть конкретизирован для получения ответа на любой соответствующий запрос по отношениям, которые она представляет, что рассматриваются как модели конструкции. Точнее, если q — это формула запроса в реляционной алгебре («чистых» отношений) и если q — это ее повышение до конструкции, предназначенной для представления недостающей информации, сильное представление обладает свойством, что для любого запроса q и (таблица) конструкция T , q поднимает все ответы на конструкцию, т.е.:
(Вышеуказанное справедливо для запросов, принимающих любое количество таблиц в качестве аргументов, но для данного обсуждения достаточно ограничения одной таблицей.) Очевидно, что таблицы Кодда не обладают этим сильным свойством, если выборки и проекции рассматриваются как часть языка запросов. Например, все ответы на
ВЫБЕРИТЕ * FROM Emp WHERE Возраст = 22 ; должен включать возможность существования такой связи, как EmpH22. Однако таблицы Кодда не могут представлять результат дизъюнкции, возможно, с 0 или 1 строкой. Однако такой ответ может представлять собой устройство, представляющее в основном теоретический интерес, называемое условной таблицей (или c-таблицей):
где столбец условия интерпретируется как строка не существует, если условие ложно. Оказывается, поскольку формулы в столбце условий c-таблицы могут быть произвольными формулами пропозициональной логики , алгоритм решения проблемы, представляет ли c-таблица какое-то конкретное отношение, имеет ко-NP-полную сложность, а значит, не имеет большого значения. практическая ценность.
Поэтому желательно более слабое понятие представительства. Имелински и Липски ввели понятие слабого представления , которое, по сути, позволяет (снимать) запросы к конструкции, чтобы возвращать представление только для достоверной информации, т.е. если оно действительно для всех « возможных мировых » экземпляров (моделей) конструкции. Конкретно, конструкция является слабой системой представления, если
Правая часть приведенного выше уравнения представляет собой достоверную информацию, т. е. информацию, которую можно с уверенностью извлечь из базы данных независимо от того, какие значения используются для замены нулей в базе данных. В примере, который мы рассмотрели выше, легко увидеть, что пересечение всех возможных моделей (т. е. достоверной информации) выбора запроса на самом деле пусто, потому что, например, (неподнятый) запрос не возвращает строк для отношения EmpH37. В более общем плане Имелински и Липски показали, что таблицы Кодда являются слабой системой представления, если язык запросов ограничен проекциями, выборками (и переименованием столбцов). Однако как только мы добавляем в язык запросов соединения или объединения, даже это слабое свойство теряется, как показано в следующем разделе.WHERE Age = 22
Рассмотрим следующий запрос к той же таблице Кодда Emp из предыдущего раздела:
ВЫБРАТЬ Имя FROM Emp WHERE Age = 22 UNION SELECT Name FROM Emp WHERE Age <> 22 ; Какое бы конкретное значение ни было выбрано для NULLвозраста Харриет, приведенный выше запрос вернет полный столбец имен любой модели Emp , но когда (снятый) запрос выполняется на самом Emp , Гарриет всегда будет отсутствовать, т. е. мы имеем :
Таким образом, когда в язык запросов добавляются объединения, таблицы Кодда даже не являются слабой системой представления недостающей информации, а это означает, что запросы к ним даже не сообщают всю достоверную информацию. Здесь важно отметить, что семантика UNION для нулевых значений, которая обсуждается в следующем разделе, даже не использовалась в этом запросе. «Забывчивый» характер двух подзапросов был достаточным для того, чтобы гарантировать, что некоторая достоверная информация останется незамеченной при выполнении вышеуказанного запроса к таблице Кодда Emp.
Для естественных объединений пример, показывающий, что определенная информация может не сообщаться некоторым запросом, немного сложнее. Рассмотрим таблицу
и запрос
ВЫБРАТЬ F1 , F3 ИЗ ( ВЫБРАТЬ F1 , F2 ИЗ J ) КАК F12 ЕСТЕСТВЕННОЕ СОЕДИНЕНИЕ ( ВЫБРАТЬ F2 , F3 ИЗ J ) КАК F23 ; Интуиция того, что происходит выше, заключается в том, что таблицы Кодда, представляющие проекции в подзапросах, упускают из виду тот факт, что нули в столбцах F12.F2 и F23.F2 на самом деле являются копиями оригиналов в таблице J. Это наблюдение предполагает, что относительно простым улучшением таблиц Кодда (которое правильно работает в этом примере) было бы использование констант Сколема (то есть функций Сколема , которые также являются постоянными функциями ), скажем, ω 12 и ω 22 вместо одного символа NULL. Такой подход, называемый v-таблицами или наивными таблицами, требует меньше вычислительных затрат, чем c-таблицы, обсуждавшиеся выше. Однако это все еще не полное решение для неполной информации в том смысле, что v-таблицы являются лишь слабым представлением для запросов, не использующих никаких отрицаний при выборе (и не использующих никаких различий между множествами). В первом примере, рассматриваемом в этом разделе, используется предложение отрицательного выбора, поэтому это также пример, когда запросы к v-таблицам не сообщают достоверную информацию.WHERE Age <> 22
Основное место, где трехзначная логика SQL пересекается с языком определения данных SQL (DDL), — это форма проверочных ограничений . Проверочное ограничение, помещенное в столбец, действует по несколько иному набору правил, чем правила для WHEREпредложения DML. Хотя предложение DML WHEREдолжно иметь значение True для строки, проверочное ограничение не должно иметь значение False. (С точки зрения логики назначенными значениями являются «Истина» и «Неизвестно».) Это означает, что ограничение проверки будет успешным, если результат проверки будет либо «Истина», либо «Неизвестно». Следующий пример таблицы с проверочным ограничением запрещает вставку любых целочисленных значений в столбец i , но разрешает вставку Null, поскольку результат проверки всегда будет оцениваться как Unknown для Nulls. [19]
CREATE TABLE t ( i INTEGER , CONSTRAINT ck_i CHECK ( i < 0 AND i = 0 AND i > 0 ) ); Из-за изменения назначенных значений относительно предложения WHERE с логической точки зрения закон исключенного третьего является тавтологией для ограничений CHECK , что означает всегда успешный результат. Более того, если предположить, что значения NULL следует интерпретировать как существующие, но неизвестные значения, некоторые патологические ПРОВЕРКИ, подобные показанному выше, позволяют вставлять значения NULL, которые никогда не могут быть заменены каким-либо значением, отличным от NULL.CHECK (p OR NOT p)
Чтобы запретить столбцу отклонять значения NULL, NOT NULLможно применить ограничение, как показано в примере ниже. Ограничение NOT NULLсемантически эквивалентно проверочному ограничению с IS NOT NULLпредикатом.
CREATE TABLE t ( i INTEGER NOT NULL ); По умолчанию проверка ограничений на внешние ключи завершается успешно, если какое-либо из полей таких ключей имеет значение NULL. Например, таблица
CREATE TABLE Books ( title VARCHAR ( 100 ), author_last VARCHAR ( 20 ), author_first VARCHAR ( 20 ), FOREIGN KEY ( author_last , author_first ) REFERENCES Authors ( last_name , first_name )); позволит вставлять строки, в которых указаны автор_last или автор_первый, NULLнезависимо от того, как определена таблица «Авторы» или что она содержит. Точнее, значение null в любом из этих полей допускает любое значение в другом, даже если оно не найдено в таблице авторов. Например, если бы Authors содержал только ('Doe', 'John'), то это ('Smith', NULL)удовлетворяло бы ограничению внешнего ключа. В SQL-92 добавлены две дополнительные опции для сужения совпадений в таких случаях. Если MATCH PARTIALпосле объявления добавляется REFERENCES, то любой ненулевой ключ должен соответствовать внешнему ключу, например, ('Doe', NULL)все равно будет соответствовать, но ('Smith', NULL)не будет. Наконец, если MATCH FULLдобавлено, то ('Smith', NULL)также не будет соответствовать ограничению, но (NULL, NULL)все равно будет соответствовать ему.
NULLВнешние соединения SQL , включая левые внешние соединения, правые внешние соединения и полные внешние соединения, автоматически создают пустые значения в качестве заполнителей для отсутствующих значений в связанных таблицах. Например, для левых внешних соединений нули создаются вместо строк, отсутствующих в таблице, появляющихся в правой части оператора LEFT OUTER JOIN. В следующем простом примере используются две таблицы, чтобы продемонстрировать создание пустого заполнителя в левом внешнем соединении.
Первая таблица ( Сотрудник ) содержит идентификационные номера и имена сотрудников, а вторая таблица ( PhoneNumber ) содержит соответствующие идентификационные номера сотрудников и номера телефонов , как показано ниже.
Следующий пример SQL-запроса выполняет левое внешнее соединение этих двух таблиц.
ВЫБЕРИТЕ e . идентификатор , эл . Фамилия , эл . Имя , шт . Номер FROM сотрудника e LEFT OUTER JOIN PhoneNumber pn ON e . ID = пн . ИДЕНТИФИКАТОР ; Набор результатов , сгенерированный этим запросом, демонстрирует, как SQL использует Null в качестве заполнителя для значений, отсутствующих в правой таблице ( PhoneNumber ), как показано ниже.
SQL определяет агрегатные функции для упрощения агрегатных вычислений данных на стороне сервера. За исключением функции COUNT(*), все агрегатные функции выполняют этап исключения нулей, поэтому нули не включаются в окончательный результат вычисления. [20]
Обратите внимание, что удаление 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]
Выходные данные агрегатной функции также могут быть нулевыми. Вот пример:
ВЫБЕРИТЕ СЧЕТ ( * ), MIN ( e . Wage ), MAX ( e . Wage ) ОТ Сотрудника e WHERE e . Фамилия LIKE '%Jones%' ; Этот запрос всегда выводит ровно одну строку, подсчитывая количество сотрудников, фамилия которых содержит «Джонс», и выдавая минимальную и максимальную заработную плату, найденную для этих сотрудников. Однако что произойдет, если ни один из сотрудников не соответствует заданным критериям? Вычислить минимальное или максимальное значение пустого набора невозможно, поэтому эти результаты должны быть NULL, что указывает на отсутствие ответа. Это не неизвестное значение, это значение Null, обозначающее отсутствие значения. Результатом будет:
Поскольку SQL:2003 определяет все нулевые маркеры как неравные друг другу, требовалось специальное определение для группировки нулевых значений вместе при выполнении определенных операций. SQL определяет «любые два значения, равные друг другу, или любые два нуля» как «неразличимые». [21] Это определение неразличимости позволяет SQL группировать и сортировать нули при GROUP BYиспользовании этого предложения (и других ключевых слов, выполняющих группировку).
В других операциях SQL, предложениях и ключевых словах при обработке значений NULL используется слово «не различимо». К ним относятся следующие:
PARTITION BYпункт о ранжировании и оконных функциях, таких какROW_NUMBERUNION, INTERSECTи EXCEPTоператоры, которые обрабатывают значения NULL одинаково для целей сравнения/исключения строк.DISTINCTключевое слово, используемое в SELECTзапросахПринцип, согласно которому значения NULL не равны друг другу (а скорее, что результат неизвестен), фактически нарушается в спецификации SQL для оператора UNION, который идентифицирует значения NULL друг с другом. [1] Следовательно, некоторые операции над множествами в SQL, такие как объединение или различие, могут давать результаты, не представляющие достоверную информацию, в отличие от операций, включающих явное сравнение с NULL (например, в предложении, WHEREобсуждавшемся выше). В предложении Кодда 1979 года (которое в основном было принято в SQL92) это семантическое несоответствие объясняется тем, что удаление дубликатов в операциях над множествами происходит «на более низком уровне детализации, чем проверка на равенство при оценке операций поиска». [11]
Стандарт SQL явно не определяет порядок сортировки по умолчанию для нулей. Вместо этого в соответствующих системах нули можно сортировать до или после всех значений данных, используя предложения NULLS FIRSTили NULLS LASTсписка ORDER BYсоответственно. Однако не все поставщики СУБД реализуют эту функциональность. Поставщики, которые не реализуют эту функциональность, могут указать в СУБД разные способы сортировки значений Null. [19]
Некоторые продукты SQL не индексируют ключи, содержащие NULL. Например, в версиях PostgreSQL до 8.3 этого не было, а в документации по индексу B-дерева указано, что [22]
B-деревья могут обрабатывать запросы на равенство и диапазон данных, которые можно отсортировать в определенном порядке. В частности, планировщик запросов PostgreSQL будет рассматривать возможность использования индекса B-дерева всякий раз, когда индексированный столбец участвует в сравнении с использованием одного из этих операторов: < ≤ = ≥ >
Конструкции, эквивалентные комбинациям этих операторов, такие как BETWEEN и IN, также могут быть реализованы с помощью поиска по индексу B-дерева. (Но обратите внимание, что IS NULL не эквивалентен = и не индексируется.)
В тех случаях, когда индекс обеспечивает уникальность, значения NULL исключаются из индекса, а уникальность между значениями NULL не обеспечивается. Опять же, цитата из документации PostgreSQL : [23]
Если индекс объявлен уникальным, несколько строк таблицы с одинаковыми индексированными значениями не допускаются. Нулевые значения не считаются равными. Уникальный индекс с несколькими столбцами будет отклонять только те случаи, когда все индексированные столбцы равны в двух строках.
Это соответствует поведению скалярных сравнений Null, определенному в SQL:2003 .
Другой метод индексации нулей предполагает обработку их как неразличимых в соответствии с поведением, определенным в SQL:2003. Например, в документации Microsoft SQL Server говорится следующее: [24]
В целях индексации значения NULL сравниваются как равные. Следовательно, уникальный индекс или ограничение UNIQUE не может быть создан, если ключи имеют значение NULL более чем в одной строке. Выберите столбцы, которые определены как NOT NULL, когда выбраны столбцы для уникального индекса или ограничения уникальности.
Обе эти стратегии индексирования соответствуют поведению Null, определенному в SQL:2003. Поскольку методологии индексирования явно не определены стандартом SQL:2003, разработка и реализация стратегий индексирования для значений Null полностью оставлены на усмотрение поставщиков.
SQL определяет две функции для явной обработки значений NULL: NULLIFи COALESCE. Обе функции являются сокращениями искомых CASEвыражений . [25]
Функция NULLIFпринимает два параметра. Если первый параметр равен второму параметру, NULLIFвозвращается значение Null. В противном случае возвращается значение первого параметра.
НУЛЛИС ( значение1 , значение2 ) Таким образом, NULLIFэто сокращение для следующего CASEвыражения:
СЛУЧАЙ КОГДА значение1 = значение2 THEN NULL ELSE значение1 КОНЕЦ Функция COALESCEпринимает список параметров, возвращая первое ненулевое значение из списка:
ОБЪЕДИНИТЬ ( значение1 , значение2 , значение3 , ...) COALESCEопределяется как сокращение для следующего CASEвыражения SQL:
СЛУЧАЙ, КОГДА значение1 НЕТ NULL , ТОГДА значение1 , КОГДА значение2 НЕ NULL , ТОГДА значение2 , КОГДА значение3 НЕ NULL , ТОГДА значение3 ... КОНЕЦ Некоторые СУБД SQL реализуют функции, специфичные для конкретного поставщика, аналогичные COALESCE. Некоторые системы (например, Transact-SQL ) реализуют ISNULLфункцию или другие подобные функции, функционально похожие на COALESCE. (Подробнее о IsфункцияхIS в Transact-SQL см. в разделе Функции.)
Функция Oracle NVLпринимает два параметра. Он возвращает первый параметр, отличный от NULL, или NULL, если все параметры имеют значение NULL.
Выражение COALESCEможно преобразовать в эквивалентное NVLвыражение следующим образом:
ОБЪЕДИНЕНИЕ ( значение1 , ... , значение { n } ) превращается в:
НВЛ ( знач1 , НВЛ ( знач2 , НВЛ ( знач3 , … , НВЛ ( знач { n - 1 } , val { n } ) … ))) Вариант использования этой функции — замена в выражении значения NULL значением, например, в NVL(SALARY, 0)котором говорится: «Если SALARYзначение NULL, замените его значением 0».
Однако есть одно примечательное исключение. В большинстве реализаций COALESCEоценивает свои параметры до тех пор, пока не достигнет первого значения, отличного от NULL, при этом NVLоценивает все свои параметры. Это важно по нескольким причинам. Параметр после первого параметра, отличного от NULL, может быть функцией, которая может быть либо дорогостоящей в вычислительном отношении, либо недопустимой, либо создавать неожиданные побочные эффекты.
Литерал не типизирован в SQL, что означает, что он не обозначается как целое число, символ или какой-либо другой конкретный NULL тип данных . [26] По этой причине иногда необходимо (или желательно) явно преобразовывать нули в определенный тип данных. Например, если СУБД поддерживает перегруженные функции, SQL не сможет автоматически разрешить правильную функцию, не зная типов данных всех параметров, включая те, для которых передается значение Null.
Преобразование литерала NULLв Null определенного типа возможно с использованием CASTвведенного в SQL-92 . Например:
ПРИВЕДЕНИЕ ( НОЛЬ КАК ЦЕЛОЕ ЧИСЛО ) представляет отсутствующее значение типа INTEGER.
Фактический тип Unknown (отличается или нет от самого NULL) варьируется в зависимости от реализации SQL. Например, следующее
ВЫБЕРИТЕ 'ок' ГДЕ ( NULL <> 1 ) IS NULL ; анализирует и успешно выполняет в некоторых средах (например, SQLite или PostgreSQL ), которые объединяют логическое значение NULL с неизвестным, но не могут анализировать в других (например, в SQL Server Compact ). В этом отношении MySQL ведет себя аналогично PostgreSQL (с небольшим исключением: MySQL считает TRUE и FALSE ничем не отличающимся от обычных целых чисел 1 и 0). PostgreSQL дополнительно реализует IS UNKNOWNпредикат, который можно использовать для проверки того, является ли логический результат с тремя значениями неизвестным, хотя это всего лишь синтаксический сахар.
Стандарт ISO SQL:1999 ввел в SQL тип данных BOOLEAN, однако это по-прежнему всего лишь необязательная, неосновная функция, имеющая код T031. [27]
При ограничении ограничением NOT NULLSQL BOOLEAN работает так же, как логический тип в других языках. Однако без ограничений тип данных BOOLEAN, несмотря на свое название, может содержать значения истинности TRUE, FALSE и UNKNOWN, которые все определяются как логические литералы в соответствии со стандартом. Стандарт также утверждает, что NULL и UNKNOWN «могут использоваться взаимозаменяемо и означать одно и то же». [28] [29]
Логический тип подвергался критике, особенно из-за обязательного поведения литерала UNKNOWN, который никогда не равен самому себе из-за идентификации с NULL. [30]
Как обсуждалось выше, в реализации SQL в PostgreSQL Null используется для представления всех НЕИЗВЕСТНЫХ результатов, включая НЕИЗВЕСТНОЕ БУЛЕВОЕ значение. PostgreSQL не реализует литерал UNKNOWN (хотя он реализует оператор IS UNKNOWN, который является ортогональной функцией). Большинство других основных поставщиков не поддерживают логический тип (как определено в T031) по состоянию на 2012 год. [31] Процедурная часть Однако PL/SQL Oracle поддерживает переменные BOOLEAN; им также может быть присвоено NULL, и это значение считается таким же, как UNKNOWN. [32]
Непонимание того, как работает 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 примером нерефлексивной логики . [33]
Точно так же нули часто путают с пустыми строками. Рассмотрим LENGTHфункцию, которая возвращает количество символов в строке. Когда в эту функцию передается значение Null, функция возвращает значение Null. Это может привести к неожиданным результатам, если пользователи плохо разбираются в трехзначной логике. Пример ниже:
SELECT * FROM sometable WHERE LENGTH ( строка ) < 20 ; -- Строки, в которых строка равна NULL, не будут возвращены. Это осложняется тем фактом, что в некоторых программах интерфейса базы данных (или даже в реализациях баз данных, таких как Oracle), NULL отображается как пустая строка, а пустые строки могут быть неправильно сохранены как NULL.
Реализация Null в ISO SQL является предметом критики, споров и призывов к переменам. В книге «Реляционная модель управления базами данных: версия 2» Кодд предположил, что реализация Null в SQL ошибочна и ее следует заменить двумя отдельными маркерами нулевого типа. Маркеры, которые он предложил, должны были обозначать «Отсутствует, но применимо» и «Отсутствует, но неприменимо» , известные как A-значения и I-значения соответственно. Рекомендация Кодда, если бы она была принята, потребовала бы реализации четырехзначной логики в SQL. [5] Другие предложили добавить к рекомендации Кодда дополнительные маркеры нулевого типа, чтобы указать еще больше причин, по которым значение данных может быть «отсутствующим», что увеличивает сложность логической системы SQL. В разное время также выдвигались предложения по реализации в SQL нескольких определяемых пользователем нулевых маркеров. Из-за сложности обработки Null и логических систем, необходимых для поддержки нескольких нулевых маркеров, ни одно из этих предложений не получило широкого признания.
Крис Дейт и Хью Дарвен , авторы «Третьего манифеста» , предположили, что реализация SQL Null по своей сути ошибочна и должна быть полностью устранена, [34] указывая на несоответствия и недостатки в реализации SQL-обработки Null (особенно в агрегатных функциях). как доказательство того, что вся концепция Null ошибочна и ее следует удалить из реляционной модели. [35] Другие, такие как автор Фабиан Паскаль , высказали убеждение, что «то, как вычисление функции должно обрабатывать пропущенные значения, не регулируется реляционной моделью». [ нужна цитата ]
Еще один конфликт, касающийся нулей, заключается в том, что они нарушают модель предположений о закрытом мире реляционных баз данных, вводя в нее предположение об открытом мире . [36] Предположение о закрытом мире, применительно к базам данных, гласит: «Все, что заявлено в базе данных, явно или неявно, истинно; все остальное ложно». [37] Эта точка зрения предполагает, что знание мира, хранящееся в базе данных, является полным. Однако нули действуют в предположении открытого мира, в котором некоторые элементы, хранящиеся в базе данных, считаются неизвестными, что делает хранимые в базе данных знания о мире неполными.
{{cite web}}: Отсутствует или пусто |url=( помощь )