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