Null sql where: SQL WHERE IS NULL, SELECT WHERE IS NOT NULL, NULL or Empty — with Examples

Команда SELECT Раздел WHERE « Язык запросов SQL

Раздел WHERE

Если в табличном выражении присутствует раздел WHERE, то следующим вычисляется он.

Условие, следующее за ключевым словом WHERE, может включать предикат условия поиска, булевские операторы AND (и), OR (или) и NOT(нет) и скобки, указывающие требуемый порядок вычислений.

Вычисление раздела WHERE производится по следующим правилам: Пусть R — результат вычисления раздела FROM. Тогда условие поиска применяется ко всем строкам R, и результатом раздела WHERE является таблица SQL, состоящая из тех строк R, для которого результатом вычисления условия поиска является true. Если условие выборки включает подзапросы, то каждый подзапрос вычисляется для каждого кортежа таблицы R (в стандарте используется термин “effectively” в том смысле, что результат должен быть таким, как если бы каждый подзапрос действительно вычислялся заново для каждого кортежа R).

Среди предикатов условия поиска в соответствии со стандартом могут находиться следующие предикаты: предикат сравнения, предикат between, предикат in, предикат like, предикат null, предикат с квантором и предикат exists.

При проверке условия выборки числа сравниваются алгебраически: отрицательные числа считаются меньше, чем положительные, независимо от их абсолютной величины. Строки сравниваются в соответствии с их представлением в коде ANSI. При сравнении двух строк, имеющих разные длины, предварительно более короткая строка дополняется справа пробелами для того, чтобы обе строки имели одинаковую длину.

Предикат сравнения с выражениями или результатами подзапроса. Условие определяется из двух выражений, разделенных одним из знаков операции отношения: =, <>(не равно), >, >=, < и <=.

Арифметические выражения левой и правой частей предиката сравнения строятся по общим правилам построения арифметических выражений и могут включать в общем случае имена столбцов таблиц из раздела FROM и константы. Типы данных арифметических выражений должны быть сравнимыми (например, если тип столбца a таблицы A является типом символьных строк, то предикат “a = 5” недопустим).

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

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

Примеры выборки SELECT с разделом WHERE

Выборка кода и фамилии покупателей, проживающих в Москве.

SELECT CUSTOMERNO, FIRSTNAME, LASTNAME FROM CUSTOMER WHERE CITY = ‘Москва’;

Выборка из таблицы emp данных по служащим отдела с номером 40:

SELECT * FROM emp  WHERE deptno = 40;

Извлечение из таблицы записи с полями  имя, должность, размер оклада и номер отдела для всех служащих за исключением продавцов из отдела с номером 30:

SELECT ename, job, sal, deptno FROM emp WHERE NOT deptno = 30;

SQL условие IS NULL — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать SQL условие IS NULL с синтаксисом и примерами.

Описание

Условие IS NULL используется в SQL для проверки значения NULL. Оно возвращает TRUE, если найдено значение NULL, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.

Синтаксис

Синтаксис для условия IS NULL в SQL:

expression IS NULL

Параметры или аргументы

expression

Выражение для проверки значения NULL.

Пример — использование IS NULL с оператором SELECT

При тестировании на NULL значение, IS NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NULL в запросе SELECT.
В этом примере у нас есть таблица customers со следующими данными:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google. com

Введите следующий SQL оператор:

SELECT *
FROM customers
WHERE favorite_website IS NULL;

Будет выбрана 1 запись. Вот результаты, которые вы получите:

В этом примере будут возвращены все записи из таблицы customers, где поле favourite_website содержит значение NULL.

Пример — использование IS NULL с оператором UPDATE

Далее давайте рассмотрим пример использования условия IS NULL в запросе UPDATE.

В этом примере у нас есть таблица products содержащая следующие данные:

Введите следующий запрос UPDATE:

UPDATE products
SET category_id = 110
WHERE category_id IS NULL;

Будет обновлена 1 запись. Снова выберите данные из таблицы products:

SELECT *
FROM products;

Вот результаты, которые вы должны получить:

В этом примере будут обновлены все значения category_id в таблице products до 110, где category_id содержит значение NULL. Как видите, category_id в последней строке обновлен до 110.

Пример — использование IS NULL с оператором DELETE

Далее давайте рассмотрим пример использования условия IS NULL в операторе DELETE.
В этом примере у нас есть таблица orders со следующими данными:

Введите следующий оператор DELETE:

DELETE FROM orders
WHERE customer_id IS NULL;

Будет удалена 1 запись. Снова выберите данные из таблицы orders:

SELECT *
FROM orders;

Вот результаты, которые вы получите:

В этом примере будут удалены все записи из таблицы orders, где customer_id содержит значение NULL. Как вы можете видеть, запрос удалил запись для order_id = 5.

Как обрабатываются значения NULL различными конструкциями языка T-SQL_часть 1

Здравствуйте уважаемые читатели блога sqlCMD.ru, постоянные и новые — автор блога рад новой встрече с вами. Наступивший сентябрь вызвал традиционный всплеск активности посетителей блога — писем и комментариев стало больше в разы. По всему видно — вновь наступила пора работы/учебы. Как обычно, вся эта активность служит автору отличным индикатором интереса и подсказывает ему какую тему следует осветить в первую очередь. Однако конкретно в данном случае ваш автор решился на довольно смелый эксперимент.

Дело в том, что достаточно давно, если и не на самой заре своей IT-карьеры то определенно где-то в тех числах, автор принялся вести этакий «внутренний SQL Server FAQ», а проще говоря текстовый файл, куда он записывал непонятные ему вопросы, мысли, идеи имеющие отношение к SQL Server. Потом непонятные вопросы дополнялись понятными ответами, и вот так составился довольно внушительный FAQ имеющий в настоящее время объем в несколько мегабайт, притом что его формат как был так и остался чисто текстовым, без единой иллюстрации. Так вот смелый эксперимент заключается в том, что, как подумалось автору, куски этого FAQ можно без проблем «конвертировать» в статьи блога. Ведь если тот или иной вопрос был интересен лично мне, и/или был в свое время до конца мне непонятен, то, с большой степенью вероятности можно предположить, что в той же ситуации оказались/окажутся несколько (десятков? сотен? — кто знает…) читателей блога. Которые с радостью прочтут готовый ответ на этот самый мучающий их вопрос. Поэтому автор и решил вырезать наиболее интересные места своего персонального FAQ, «причесывать» их, существенно дополнять поясняющим текстом/иллюстрациями/кодом T-SQL и в таком переработанном виде выкладывать на сайт. Что из того получится покажет время и, конечно же, ваши отзывы. Разумеется, статьи «по письмам читателей» так же будут продолжать создаваться, и более того, одна из них уже готова в черновике. Переложение FAQ просто станет еще одним и, как видится автору, довольно многообещающим способом поделиться накопленными знаниями/опытом со своими читателями.

Раздел FAQ которым автор решил сегодня поделиться с вами анализирует реакцию нашего любимого сервера на такую любопытную «зверушку» как значение NULL. Нельзя сказать, что данный вопрос сложен. Нет, как раз для именно понимания он не только прост, а даже, в определенной степени, элементарен. Хитрость заключается исключительно в разветвленности, то есть многообразии той самой реакции, и, самое главное, ее непоследовательности. Как известно абсолютно всем читателям данных строк NULL никогда не равен ничему, даже другому NULL. То есть выражение NULL=<что_угодно> не вернет нам TRUE никогда и ни за что. Правильно? В теории — на 100% правильно. А вернет ли тоже самое выражение FALSE? Нет, конечно, ведь NULL это «место подо что-то», полная неизвестность. Как мы можем утверждать что NULL точно не равен десяти? А если все-таки равен? Поэтому и FALSE мы тоже никогда из этого выражения не получим. Правильно? В теории — снова на 100% правильно. А на практике? А вот если бы те же самые утверждения выполнялись бы на практике хотя бы в 80% случаев, то и не было бы соответствующего раздела в персональном FAQ автора. И, как следствие, не было бы данной статьи. А она — перед вами. Так что давайте приступать…

NULL, UNKNOWN и троичная логика.

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

Сразу определимся, что в «большую науку» лезть мы не будем и рассуждать о принципиальной допустимости значений NULL в реляционных СУБД как таковых тоже не станем. Апологеты «чистоты» таких СУБД и их избавления от самого духа NULL-значений свою позицию обозначили давно, четко и ясно. И привели аргументы в поддержку той позиции. Аргументы, к слову сказать, весьма существенные, а некоторые — так и просто «железобетонные», спорить с которыми очень и очень проблематично. Да вот только все такие непрошибаемые аргументы ни на йоту не помогли нашим апологетам — NULL-ы в большинстве современных СУБД есть, и точка. Другое дело, что в SQL Server вы вольны тотально запретить это «нехорошее» значение обозначив все и каждую колонку своей базы NOT NULL. Однако на практике дизайн со столь суровыми ограничениями встречается зело нечасто, а поэтому, отринув дальнейшее хождение вокруг да около, мы можем переходить к простому и понятному вопросу — как нам жить с NULL и успешно миновать все «грабли», что этот самый NULL заботливо для нас приготовил?

Самых больших проблем с NULL-ом две. Первая формулируется очень просто: сравнение NULL c чем угодно, включая и другой NULL, дает в результате UNKNOWN.

Строго говоря, установив значение опции уровня сеанса ANSI_NULLS в значение OFF можно добиться, что бы сравнение NULL=NULL возвращало все-таки TRUE, а не UNKNOWN. Автор мог бы привести длиннющий список причин, по которым вам не следует никогда и ни при каких обстоятельствах устанавливать данную опцию в значение OFF, но ограничимся одной, самой весомой: в будущих версиях SQL Server данная опция будет изъята, а ее значение ON будет «вшито» в код движка сервера и не будет поддаваться никакой модификации. Не сработает подобный финт и если вы надумаете воспользоваться парной и одноименной опцией уровня базы — и она тоже будет «залочена» в положении ON. Весь последующей текст данной статьи написан из предположения что обсуждаемая опция имеет «нормальное» значение, то есть ON.

В общем-то, в самом факте что сравнение с NULL дает не TRUE, не FALSE, а именно UNKNOWN нет ничего страшного. «Страшно» следствие из этого факта: привычная многим (и даже почти всем) программистам языков высокого уровня двоичная логика сменяется троичной. «И что», возможно спросите вы, «это добавочное и третье логическое значение так уж усложняет жизнь SQL-администратора/разработчика»? Намного, ответит вам автор! Именно это самое третье значение вносит в жизнь означенных IT-персон то разнообразие, которое приятным никак не назовешь. Собственно говоря, разбор и анализ этого самого разнообразия является центральным вопросом статьи читаемой вами в настоящий момент.

Вторая большая проблема — в SQL Server нет типа bool. Вот все мыслимые (а иногда и трудно вообразимые) типы есть, а bool — нет. Внимательные читатели сразу же зададутся вопросом — осведомлен ли автор текущих строк о таком замечательном типе нашего сервера, как bit? Автор спешит уверить, что о данном замечательном типе он узнал уже много-много лет назад, и большой опыт работы с ним позволяет ему утверждать — это не есть тип bool. Сравните:

Это отрывок кода на C#, имеющего первоклассный и «всамделишный» тип bool. «И?», снова спросят те же читатели. «А вот так на T-SQL»:

«не тоже ли самое»? Уверяю вас, в мире нет ничего дальше отстоящего друг от друга чем два показанных фрагмента. Разумеется, вы вольны считать что на логическом уровне там и там переменная b получила значение истина, и основания для подобного утверждения у вас есть. Ну а вот если копнуть «физику процесса»? А вот тогда выясняется, что в C#:

• переменная b может принимать только значения логического типа. Попытка поместить туда, к примеру, целое (b=1) заканчивается ошибкой компиляции;
• переменную b можно сравнивать только с булевым же значением. Попытка соотнести ее с единицей (b==1) вновь вызывает недовольство компилятора, а вот b==(1>2) — пожалуйста, true сравнивается с false, все законно.

Параллельно выясняется же, что в T-SQL:

• переменная @b получает значение 1, а вовсе никакое не TRUE. Да и как может быть иначе, если по своему определению тип bit целочисленный? Он априори может быть или 1 (число!) или 0 (число!). Строковые константы TRUE/FALSE просто приводятся к этим двум числам, успешно создавая у нас иллюзию «bool-ориентированности» языка T-SQL. Еще тип bit может быть NULL, но пока оставим это дабы не усложнять и без того непростые вещи;
• вы запросто можете присвоить 1 (число!) нашей переменной — SET @b=1. А хотите — сравните их: IF (@b=1) PRINT ‘@b is 1’;
• а вот IF (@b=(1>2)) PRINT ‘Must be false’ — не прокатывает. Сравнение 1>2 дает «реальный» тип bool из мира T-SQL, для которого у нас просто нет подходящего контейнера! И уж конечно этот «реальный bool» никоим образом не может быть соотнесен с простой цифрой.

Итого, в T-SQL тип bool мало того что усложнен третьим возможным значением, так он еще по природе своей столь эфемерен, что значения этого типа возникают только в момент исполнения одного из операторов сравнения (равно, больше, меньше и т.д.) и «испаряются» сразу же после этого. Мы не можем (как это совершенно без проблем делают C#-программисты) «законсервировать» результат такого сравнения, дабы проанализировать его позже — нам попросту некуда его поместить! То есть, в C#: b=2>3 — запросто, все отлично, b теперь ложь. В T-SQL: SET @b=(2>3) — Incorrect syntax near ‘>’. А что, собственно, вы бы хотели что бы оказалось в переменной @b с учетом ее типа? Ну не ноль же, в самом деле. .. «Два больше трех равно нулю» это определенно не та фраза которую хочется прочесть в документации к любой программной платформе. А сверх того, T-SQL лишен изящества с которым тот же C# выявляет окончательный логический результат того или иного выражения. В последнем языке Console.WriteLine(2>3) с удовольствием напечатает вам False, в T-SQL PRINT 2>3 не напечатает ровным счетом ничего и даже не исполнится. А все потому, что в C# bool является именно что типом. Определены его характеристики, свойства, поведение, и, в том числе, визуальное представление каждого из его возможных значений. T-SQL всего этого праздника начисто лишен. Отсюда и берутся столь уродливые конструкции как SELECT 1 WHERE 2>3, которые, между прочим, так же не до конца корректны (а почему они не являются верными на 100% мы выясним через пару абзацев) когда мы хотим однозначно установить итоговое логическое значение того или иного выражения в T-SQL.

Теперь зафиксируем для себя пару вещей которые «и так всем хорошо известны».

Если выражение не являющееся логическим состоит из нескольких операндов связанных какими угодно операциями/операторами, и хотя бы один из этих операндов имеет значение NULL — все выражение безусловно имеет тоже самое значение.

Пример:

Все приведенные только что SELECT-ы будут просчитаны в одно и тоже значение, и вы, разумеется, догадались в какое. Забавно, что NULL, в определенном смысле, даже попирает законы арифметики. Последний SELECT по хорошему должен бы заканчиваться ошибкой деления на ноль, а он как ни в чем не бывало возвращает нам NULL.

Проверьте свои знания T-SQL: Что изменится для трех последних SELECT-ов, если мы после создания таблицы T2 не вставим в нее ни единой строки?

Смотреть ответ

Ровным счетом ничего. Если колонка Col1 не содержит ни единого значения, то ее максимальное значение неизвестно, то есть NULL. О чем нам любезно просигнализирует агрегатная функция MAX. Можно дать и такое пояснение: подавляющее большинство таких функций значения NULL просто игнорируют, так что наша вставленная строка из примера изначально была совершенно бесполезна для всех трех обсуждаемых SELECT-ов.

Вторая «и так всем известная» вещь:

Если в логическом выражении два операнда связаны любым оператором сравнения (больше, меньше, равно, и т.д.) и один из этих операторов имеет значение NULL — все выражение однозначно имеет итоговое значение UNKNOWN.

То есть, если структура и содержимое таблицы T2 те же самые что и в предыдущем примере, то все показанные ниже логические выражения будут вычислены именно в UNKNOWN

1 2 3 4 5 6 7 8 9 10

IF(NULL>NULL+4). .. IF(0=NULL)… IF(NULL=NULL)… IF(NULL<>NULL)… IF(»=NULL)… DECLARE @max int SELECT @max=MAX(Col1) FROM T2 IF(@max=@max)… IF(@max=NULL)…

Как автор может быть в этом уверен? Нет, понятно что правило только что было объявлено и согласно ему все верно — везде UNKNOWN. Однако — доказательства?

Как отмечалось в статье чуть выше отсутствие в T-SQL «вменяемого» bool-типа приводит к уродливым конструкциям наподобие SELECT 1 WHERE »=NULL. А как еще оценить довольно сложное логическое выражение? Однако и она, конструкция эта, не доказывает что исследуемое выражение просчитывается в UNKNOWN. Тот факт что показанный SELECT вернет пустой резалт-сет однозначно перечеркивает лишь мысль о возможной эквивалентности пустой строки и значения NULL. То есть мы лишь установим что »=NULL точно НЕ TRUE, на что сложно было рассчитывать и без всяких проверочных кодов. Однако пустой резалт-сет того же SELECT-а вовсе не отменяет той возможности, что результатом сопоставления »=NULL является FALSE, а вовсе не UNKNOWN. Приходится быть хитрее:

В показанном фрагменте кода тильду (~) следует заменить тем логическим выражением, чей результат вызывает у вас неуверенность. Например, вместо этого символа можно было бы впечатать все те же »=NULL. Разумеется выражение должно быть одним и тем же и в первой, и во второй строке показанного фрагмента. И вот только пройдя такую двойную проверку мы выносим неопровержимый вердикт: »=NULL есть UNKNOWN, как и любое иное логическое выражение где фигурирует NULL.

Из только что рассмотренных «и так всем известных» фактов следуют два далеко неочевидных вывода. Первый из которых: когда мы читаем фразы вида «NULL плюс что угодно равно NULL…» или «NULL сравнить с чем угодно будет UNKNOWN…», то под NULL в этих фразах (а так же в тех двух правилах, что автор выделил для вас специальной разметкой чуть выше) следует понимать как строковый литерал NULL, так и любое выражение вычисляемое в то же самое значение. Например строки SELECT MAX(Col1)/0 FROM T2 и IF(@max=@max)… вроде бы и не содержат никаких NULL-ов, а соответствующие правила к ним применимы всецело. И все потому, что и MAX(Col1), и @max представляют собой именно NULL. Так что если вы просматриваете код и не видите там никаких упоминаний о NULL-ах это вовсе не гарантирует что в ходе его выполнения вы не получите UNKNOWN как результат оценки логического выражения.

Теперь полистаем наш любимый и глубокоуважаемый BOL. Возьмем, к примеру, вот такую его статеечку — = (Equals). Прямо черными английскими буквами по белому полю сказано (выделение автора):

Comparing NULL to a non-NULL value always results in FALSE.

Это удивительно, но автору известны лишь три статьи из BOL где все сказано правильно «NULL vs.<что_угодно>=UNKNOWN». И порядка десятка статей утверждающих что сравнение с NULL приводит к результату FALSE (что попросту чудовищно по степени ошибочности), или что сравнение с NULL приводит к результату NULL (что несколько лучше, но снова «не айс», хотя бы в силу того обстоятельства что NULL не является итоговым результатом сопоставления двух значений; максимум чем он может быть — одним из этих значений). С учетом что подобные предложения трудолюбивые работники MS аккуратно копи-пастят (видимо не читая?) при выходе очередной версии SQL Server так что срок их жизни только на памяти автора составляет лет по 15-ти, приходим ко второму далеко неочевидному выводу: стабильность — не всегда признак мастерства, а изучать BOL следует… осмотрительно, как минимум. Следствие: если вы читаете что угодно по SQL Server (причем источник информации не важен абсолютно) и при этом в соседнем окне у вас не открыта SQL Server Management Studio с целью контроля достоверности информации (если не сплошной, то выборочной как минимум) — вы напрасно это читаете.

Вернемся к нашему фрагменту кода «двойной проверки»:

Почему именно такой код позволяет нам уверенно вычленять те логические выражения что в результате дают UNKNOWN? Да потому, что только это единственное (из трех возможных) bool-значение обладает уникальным свойством: его отрицание (NOT) никак не влияет на результат! Иными словами NOT UNKNOWN=UNKNOWN. TRUE/FALSE при той же операции меняют свои значения на противоположные, как это всем хорошо известно. А что у нас с логическими операторами OR/AND связывающих два и более логических выражения одно из которых просчитано в UNKNOWN? Тут у многих SQL-администраторов/разработчиков наблюдается интересный, вполне объяснимый, но от того не менее ошибочный «дедуктивный» вывод: «раз NULL плюс что угодно равно NULL», рассуждают они, «то и UNKNOWN плюс что угодно будет UNKNOWN». Насчет NULL они совершенно правы, а вот насчет UNKNOWN — заблуждаются. Опять же, здесь начинаются довольно тонкие материи: NULL это значение, UNKNOWN это возможный результат сравнения двух значений. И если связываются два логических выражения одно из которых просчитано в UNKNOWN, а второе может иметь любое из трех bool-значений, то:

• UNKNOWN OR TRUE = TRUE
• UNKNOWN OR FALSE = UNKNOWN
• UNKNOWN OR UNKNOWN = UNKNOWN

И, соответственно:

• UNKNOWN AND FALSE = FALSE
• UNKNOWN AND TRUE = UNKNOWN
• UNKNOWN AND UNKNOWN = UNKNOWN

Как видите UNKNOWN AND/OR <что_угодно> чаще всего будет действительно давать в итоге снова UNKNOWN. Но — отнюдь не всегда! Ну и что бы завершить картину взаимоотношений логических операторов с UNKNOWN и еще раз подчеркнуть уникальное свойство этого результата сопоставления двух значений:

• NOT TRUE=FALSE
• NOT FALSE=TRUE
• NOT UNKNOWN = UNKNOWN

Теперь — подумаем над механизмом выполнения команды типа SELECT…FROM…WHERE Col1=NULL. Да, автор уже слышит справедливые «выкрики с места»: кто ж так с NULL сравнивает-то? IS [NOT] NULL для кого сделаны были? Все это совершенно правильно и понятно, но! Показанный фрагмент абсолютно корректен с точки зрения «голого» синтаксиса T-SQL. И выполняется он тоже без проблем. А мы с вами, как IT-профессионалы стремящиеся к полному и совершенному постижению SQL Server и того языка на котором пишутся программы для него обязаны понимать механику работы любого синтаксически корректного выражения. Хотя бы с целью его исправления и приведения к логически корректному виду. Так вот, задается вопрос — как работает наша «неправильная» команда? Вне сомнений, к текущим строкам статьи, мы без всяких тестов и проверок уверенно заявляем: Col1=NULL это у нас UNKNOWN. То есть: берется первая строка исходной таблицы и условие для ее включения/исключения в/из итоговый резалт-сет просчитывается в это «неудобное» bool-значение. Что делать? В теории показанный SELECT должен открыть интерактивную сессию вопросов-ответов с пользователем: «строка 1, критерий включения в итоговый резалт-сет неизвестен, включаем?»; «строка 2, критерий неизвестен…» и т.д. На практике же UNKNOWN в данном случае (а случаи, как мы вскоре выясним, бывают разные) приравнивается к FALSE, что вносит изрядную долю путаницы в мысли администраторов, разработчиков, да и писатели статей для BOL видимо так же «дают сбои» на том же самом месте. То есть: в теории у нас получилось UNKNOWN, на практике мы работаем с FALSE. Вот в этот самый «разлом» между теорией и практикой и вклинивается наша статья. Ну а ответ на заданный вопрос — вот так и работает, условие для включения в итоговый резалт-сет для каждой строки просчитывается в FALSE (причем гарантированно для каждой строки без исключения) и в силу этого обстоятельства мы получаем пустой итоговый резалт-сет (и снова гарантированно).

SQL WHERE IS NULL, SELECT WHERE IS NOT NULL, NULL или Empty — с примерами

Что такое NULL и как запросить значения NULL?

NULL — это специальное значение, обозначающее «нет значения».
Сравнение столбца с NULL с помощью оператора = undefined .
Вместо этого используйте WHERE IS NULL или WHERE IS NOT NULL.

Синтаксис SQL WHERE IS NULL

Общий синтаксис IS NULL —

ВЫБЕРИТЕ имена столбцов
  ОТ имя-таблицы
 WHERE column-name IS NULL
 

Общий синтаксис IS NOT NULL:

ВЫБЕРИТЕ имена столбцов
  ОТ имя-таблицы
  ГДЕ имя-столбца НЕ ПУСТО
 

SQL, WHERE IS NULL Примеры

Проблема : Список всех поставщиков, у которых нет номера факса

ВЫБЕРИТЕ Id, CompanyName, Phone, Fax
  ОТ поставщика
 ГДЕ ФАКС ЕСТЬ NULL
 

Результат: 16 записей

Проблема : Перечислите всех поставщиков, у которых – есть номер факса

ВЫБЕРИТЕ Id, CompanyName, Phone, Fax
  ОТ поставщика
 ГДЕ Факс НЕ ПУСТО
 

Результат: 13 записей

Id	Название компании	Телефон	Факс
3	Усадьба бабушки Келли	(313) 555-5735	(313) 555-3349
7	Павлова, ООО	(03) 444-2343	(03) 444-6588
9	PB Knäckebröd AB	031-987 65 43	031-987 65 91
13	Nord-Ost-Fisch Handelsgesellschaft mbH	(04721) 8713	(04721) 8714
14	Formaggi Fortini s. r.l.	(0544) 60323	(0544) 60603
18	Aux joyeux ecclésiastiques	(1) 03.83.00.68	(1) 03.83.00.62
19	Консервный завод морепродуктов Новой Англии	(617) 555-3267	(617) 555-3389
21	Люнгбисилд	43844108	43844115
22	Zaanse Snoepfabriek	(12345) 1212	(12345) 1210
24	Добрый день, товарищ	(02) 555-5914	(02) 555-4873
26	Паста Буттини s.r.l.	(089) 6547665	(089) 6547667
28	Gai pâturage	38. 76.98.06	38,76,98,58
29	Forêts d’érables	(514) 555-2955	(514) 555-2921

SQL: IS NULL Условие

В этом руководстве по SQL объясняется, как использовать условие SQL IS NULL с синтаксисом и примерами.

Описание

Условие IS NULL используется в SQL для проверки значения NULL. Он возвращает TRUE, если найдено значение NULL, в противном случае возвращает FALSE. Его можно использовать в операторах SELECT, INSERT, UPDATE или DELETE.

Подписаться

Синтаксис

Синтаксис условия IS NULL в SQL:

 выражение IS NULL 

Параметры или аргументы

выражение

Выражение для проверки значения NULL.

DDL / DML для примеров

Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных. Тогда попробуйте примеры в своей базе данных!

Получить DDL / DML

Пример — использование IS NULL с оператором SELECT

При проверке значения NULL рекомендуется использовать оператор сравнения IS NULL в SQL. Начнем с рассмотрения примера, показывающего, как использовать условие IS NULL в операторе SELECT.

В этом примере у нас есть таблица с именем клиентов со следующими данными:

customer_id	фамилия	имя	избранное_вебсайт
4000	Джексон	Джо	techonthenet.com
5000	Смит	Джейн	digminecraft.com
6000	Фергюсон	Саманта	bigactivities.com
7000	Рейнольдс	Аллен	checkyourmath. com
8000	Андерсон	Пейдж	НЕТ
9000	Джонсон	Дерек	techonthenet.com

Введите следующий оператор SQL:

Попытайся

 ВЫБРАТЬ *
ОТ клиентов
ГДЕ favourite_website IS NULL; 

Будет выбрана 1 запись. Вот результаты, которые вы должны увидеть:

В этом примере будут возвращены все записи из таблицы customers , где favourite_website содержит значение NULL.

Пример — использование IS NULL с оператором UPDATE

Затем давайте рассмотрим пример использования условия IS NULL в операторе UPDATE.

В этом примере у нас есть таблица с названием продукты со следующими данными:

Введите следующий оператор UPDATE:

Попытайся

 ОБНОВЛЕНИЕ продуктов
УСТАНОВИТЬ category_id = 100
WHERE category_id IS NULL; 

Будет обновлена ​​1 запись.Снова выберите данные из таблицы продуктов :

 ВЫБРАТЬ * ИЗ продуктов; 

Вот результаты, которые вы должны увидеть:

В этом примере все значения category_id в таблице products будут обновлены до 100, где category_id содержит значение NULL. Как видите, category_id в последней строке обновлен до 100.

Пример — использование IS NULL с оператором DELETE

Далее давайте рассмотрим пример использования условия IS NULL в операторе DELETE.

В этом примере у нас есть таблица под названием orders со следующими данными:

Введите следующую инструкцию DELETE:

Попытайся

 УДАЛИТЬ ИЗ заказов
WHERE customer_id IS NULL; 

Будет удалена 1 запись. Снова выберите данные из таблицы заказов :

 ВЫБРАТЬ * ИЗ заказов; 

Вот результаты, которые вы должны увидеть:

В этом примере будут удалены все записи из таблицы orders , где customer_id содержит значение NULL.Как видите, он удалил запись для order_id = 5.

SQL IS NULL | IS NOT NULL: правильная обработка NULL в SQL

Резюме : в этом руководстве мы познакомим вас с концепцией NULL и покажем, как использовать операторы SQL IS NULL и IS NOT NULL для тестирования если выражение NULL или нет.

Что такое NULL

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

Например, если у сотрудника нет номера телефона, вы можете сохранить его как пустую строку. Однако, если мы не знаем его или ее номер телефона во время вставки записи о сотруднике, мы будем использовать значение NULL для неизвестных номеров телефонов.

Значение NULL является особенным, потому что любые сравнения со значением NULL никогда не могут привести к истинному или ложному результату, но к третьему логическому результату, неизвестному.

Следующая инструкция возвращает нулевое значение.

 
  ВЫБРАТЬ NULL = 5;  
 

Посмотреть в действии

Значение NULL даже не равно самому себе, как показано в следующем утверждении.

 
  ВЫБРАТЬ NULL = NULL;  
 

Посмотрите в действии

В этом примере результатом является нулевое значение.

Нельзя использовать оператор сравнения, равный (=), для сравнения значения со значением NULL .Например, следующий оператор не даст правильного результата.

 
  ВЫБРАТЬ
employee_id,
имя,
фамилия,
телефонный номер
ИЗ
сотрудники
ГДЕ
phone_number = NULL;  
 

Посмотреть в действии

Операторы IS NULL и IS NOT NULL

Чтобы определить, является ли выражение или столбец NULL , вы используете оператор IS NULL следующим образом:

 
  выражение IS NULL;  
 

Если результатом выражения является NULL , оператор IS NULL возвращает истину; в противном случае возвращается false.

Чтобы проверить, не является ли выражение или столбец NULL , вы используете оператор IS NOT :

 
  выражение IS NOT NULL;  
 

IS NOT NULL возвращает false, если значение выражения равно NULL; в противном случае возвращается истина;

Примеры SQL IS NULL и IS NOT NULL

В этих примерах мы будем использовать таблицу сотрудников из образца базы данных для демонстрации.

Чтобы найти всех сотрудников, у которых нет телефонных номеров, используйте оператор IS NULL следующим образом:

 
  SELECT
employee_id,
имя,
фамилия,
телефонный номер
ИЗ
сотрудники
ГДЕ
phone_number IS NULL;  
 

Посмотреть в действии

Чтобы найти всех сотрудников, у которых есть номера телефонов, используйте IS NOT NULL , как показано в следующем заявлении:

 
  SELECT
employee_id,
имя,
фамилия,
телефонный номер
ИЗ
сотрудники
ГДЕ
phone_number НЕ ПУСТО;  
 

Посмотреть в действии

Теперь вы должны понять концепцию NULL и знать, как использовать оператор SQL IS , чтобы проверить, является ли значение NULL или нет

• Было ли это руководство полезно?
• Да Нет

Подсчет значений NULL — Блог Бенджамина

Меня несколько раз спрашивали о подсчете значений NULL, поэтому я собираюсь написать об этом в блоге в надежде, что другим поможет это объяснение значений NULL в SQL и того, как их СЧИТАТЬ при необходимости. Обратите внимание, в моих примерах используется таблица из базы данных System Center Configuration Manager.

Во-первых, важно знать, что такое NULL в SQL. NULL в SQL просто означает, что для поля нет значения. Сравнение для NULL невозможно с помощью операторов «=» или «! =» (Или «») *. Кроме того, «значения» NULL не будут JOIN когда в JOIN (это означает, что значение NULL в одном table.column не «=» значение NULL в другом table.column). Это означает, что вместо того, чтобы говорить что-то вроде «WHERE NullableField = NULL», вы должны вместо этого говорить «WHERE NullableField IS NULL» при попытке найти NULL (или НЕ найти NULL) **.

Теперь мы готовы рассмотреть решения:

Функция COUNT может сообщить вам общее количество строк, возвращенных в результирующем наборе (как NULL, так и не NULL вместе, в зависимости от того, как он используется). Например:

· Использование SELECT COUNT (*) или SELECT COUNT (1) (что я предпочитаю использовать) вернет сумму всех записей, возвращенных в наборе результатов, независимо от значений NULL.

· Использование COUNT () подсчитает количество элементов, отличных от NULL в указанном столбце (поля NULL будут проигнорированы).

Таким образом, вы можете найти количество полей NULL в наборе результатов, вычтя поля, отличные от NULL, из полей Total, например:

 ВЫБРАТЬ СЧЕТЧИК (1) - СЧЕТЧИК (<здесь определенное имя столбца>) 

Но это было бы скучно. Так что вместо этого мы сделаем это более нескучным способом. Мы будем использовать оператор CASE, чтобы определить, какие строки содержат значения NULL, и создать способ для SQL СЧИТАТЬ (или SUM) их. Вот как это выглядит (я мог бы использовать COUNT вместо SUM):

 СУММА (СЛУЧАЙ, КОГДА ЕСТЬ НУЛЬ, ТО 1 КОНЕЦ) 

Причина, по которой это будет работать, заключается в том, что, когда в операторе CASE нет «ELSE», любая строка, не соответствующая критериям, обрабатывается как NULL.Поскольку COUNT (и другие агрегатные функции) игнорируют значения NULL, мы используем CASE для преобразования значений NULL в значения и значений в NULL.

Вот реальный пример того, как это выглядит (с использованием модифицированной версии отправленного мне запроса):

 ВЫБЕРИТЕ LastStatusMessageIDName
       , COUNT (1) AS [количество общих записей]
       , COUNT (LastExecutionResult) AS [количество ненулевых записей]
       , SUM (CASE WHEN LastExecutionResult IS NULL THEN 1 END) AS [Количество записей NULL]
  ОТ dbo.v_ClientAdverticingStatus
 ГДЕ AdvertisingID = 'CAZ201AE'
   И LastStateName! = 'Успешно'
 ГРУППА ПО LastStatusMessageIDName
 ЗАКАЗАТЬ ПО 4 DESC; 

* Технически это возможно, если вы скажете SQL не думать как SQL, но я не считаю это решением или даже вариантом. Если вы используете «SET ANSI_NULLS OFF» в своих сценариях, я предлагаю вам переписать их. Вот что говорится об этой опции в электронной документации: В будущей версии SQL Server ANSI_NULLS всегда будет включен, и любые приложения, которые явно устанавливают этот параметр на OFF, будут генерировать ошибку.Избегайте использования этой функции в новых разработках и запланируйте изменение приложений, которые в настоящее время используют эту функцию.

** Чтобы увидеть это на реальном примере, попробуйте запустить следующее, чтобы увидеть поведение:

 / ***** Сравнение пустого поля ***** /
SELECT COUNT (1) FROM dbo.v_ClientAdvertisingStatus WHERE LastExecutionResult IS NULL AND AdvertisingID = 'CAZ201AE' - будет подсчитано общее количество записей с NULL «LastExecutionResult» (для рекламы).
ВЫБЕРИТЕ COUNT (1) ИЗ dbo.v_ClientAdvertisingStatus WHERE LastExecutionResult = NULL AND AdvertisingID = 'CAZ201AE' - это не работает, потому что нет значений, технически «равных» «NULL»! 

Нравится:

Нравится Загрузка …

Связанные

Почему NULL никогда не сравнивает ложь ни с чем в SQL

Один из наиболее распространенных вопросов начинающих SQL — почему значения NULL «не работают правильно» в предложениях WHERE . В этой статье я объясню это так, как я надеюсь, будет иметь смысл и легко запомнить.

Если вы новичок в SQL и вам трудно понять эту статью, я призываю вас ломать голову над ней, пока не загорится свет. Мне пришлось сделать это самому (и мне пришлось хорошенько подумать над этим, чтобы написать эту статью), и я видел, как многие люди изучают SQL. NULL s всегда кажутся важным камнем преткновения.

Запрос, который не работает

Вот два распространенных запроса, которые просто не работают:

  выберите * из таблицы, где столбец = ноль;

выберите * из таблицы, где столбец <> null;
  

Они оба возвращают , нет строк! Бесчисленные ветераны SQL пытались объяснить это новичкам.Новичок обычно думает, что первая строка должна возвращать строки, где c1 равно NULL . Затем ветеран указывает, что NULL никогда ничему не равняется. Затем новичок думает: «Если NULL не равно ничему, то« WHERE COLUMN IS NOT EQUAL TO NULL »всегда истинно, поэтому второй запрос должен вернуть все результаты!» Второе предложение WHERE логически противоположно первому, верно? Правильно? К сожалению, нет.

Настоящая проблема: языковая ловушка

Новичок попал в языковую ловушку, которую, вероятно, установил опытный программист, сказав: « NULL никогда ничему не равняется.«Это утверждение, кажется, подразумевает, что« NULL — это , НЕ РАВНО ». К сожалению, это неправильно. NULL не только ничему не равно, но и не равно . Вот где язык сбивает с толку.

По правде говоря, высказывание чего-либо со словами «равно» или «не равно» является ловушкой при обсуждении NULL s, потому что не существует концепции равенства или неравенства, больше или меньше чем с NULL s .Вместо этого при обсуждении NULL s можно сказать только «есть» или «не является» (без слова «равно»).

Как правильно думать NULL

Правильный способ понять NULL — это то, что это не значение. Не «это значение NULL », а «это значение NULL не является значением». Все либо является ценностью, либо нет. Когда что-то является значением, это «1», или «привет», или «зеленый», или «5 долларов США» и т. Д., Но когда что-то не является значением, это просто совсем не что-то .SQL представляет «это не имеет значения» с помощью специального не-значения NULL . Когда кто-то говорит «значение NULL », следует мысленно не согласиться, потому что не существует . NULL — полное, полное отсутствие какого-либо значения.

Что вы получите, если сравните значение с NULL ?

Краткий ответ: NULL . Каждый раз. Результат сравнения что-либо с NULL , даже сам по себе, всегда, всегда NULL .Сравнение с NULL никогда не бывает истинным или ложным. Поскольку NULL никогда не может быть равным какому-либо значению, оно также никогда не может быть неравным.

Иногда люди не могут понять, почему сравнение с NULL никогда не может быть истинным или ложным. Вот неофициальное доказательство, которое может помочь:

Учитывая следующие предикаты,

1. NULL не является значением
2. Никакое значение не может быть равно значению, не являющемуся значением

Вот доказательство от противного: представьте на мгновение, что NULL не равно значению — скажем, действительному числу, исключая бесконечность и отрицательную бесконечность.Я выберу примерное число, скажем 5.

1. Предположим, что NULL <> 5 .
2. То есть NULL <> 5 является истинным выражением (операции сравнения бывают логическими, истинными или ложными).
3. Это означает, что « NULL <5 или NULL> 5 » верно, поскольку я имею дело с конечными действительными числами; если он не равен, он должен быть больше или меньше.
4. Следовательно, существует действительное число, равное NULL ; либо меньше 5, либо больше 5.
5. Это противоречие, потому что я принял как данность, что никакое значение не может быть равным NULL .

Следовательно, NULL не равно значению и не равно ему, поэтому любое сравнение с участием NULL не будет ни истинным, ни ложным . Результат сравнения с участием NULL не является логическим значением — это не значение . Вы просто не можете сравнивать то, что существует, с тем, чего не существует.

Так и должно быть, потому что, если сравнение с не-значением имело определенное значение, каждый запрос можно было бы переписать, чтобы вернуть неправильный результат. Можно было бы преобразовать выражения в эквивалентные выражения, дающие противоположный ответ, и так далее.

Как правильно писать запросы

Вместо использования логических операторов сравнения, таких как меньше и больше, равно и не равно, эти запросы должны быть написаны с помощью специального оператора сравнения IS NULL :

  выберите * из таблицы, в которой значение столбца равно нулю;

выберите * из таблицы, где столбец не равен нулю;
  

Оператор IS NULL проверяет, является ли значение нулевым или ненулевым, и возвращает логическое значение.

Правда в том, что я солгал

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

Поскольку компьютеры работают только с существующими объектами, несуществование на самом деле невозможно, поэтому NULL должны быть внутренне реализованы как какое-то значение, где-то — даже если это значение указывает, что другое значение не является значением (да ?).

Я также не замечаю сравнений с NULL . NULL s приводят к трехзначной логике; логические значения больше не просто ИСТИНА и ЛОЖЬ , но также могут быть НЕИЗВЕСТНО . Результатом сравнения NULL s будет UNKNOWN , что не то же самое, что NULL , но это просто семантические различия и глубокие математические размышления, которые существенно не влияют на то, как вы пишете запросы.

MySQL, например, реализует UNKNOWN как NULL , хотя это не совсем согласованно — попробуйте следующие запросы:

  выбрать неизвестно;
выберите null;
выберите true;
выберите false;
выбрать null неизвестно;
select false имеет значение null;
select true имеет значение null;
select unknown is null;
  

Просто помните, что NULL не может быть ни равным, ни неравным, и я обещаю, что вы всегда будете в безопасности.Бесполезно придирчиво подходить к тонким пунктам NULL против UNKNOWN и тому подобное.

Пазл с COUNT

Кто-то разместил на странице руководства MySQL комментарий о расширениях предложения GROUP BY, и я думаю, что это интересно обсудить здесь. Запрос — это способ подсчета подмножеств в группе:

  выбрать стиль обуви,
   count (цвет) как Count,
   count (color = 'red' ИЛИ ​​NULL) как redCount,
   count (color = 'green' ИЛИ ​​NULL) как greenCount,
   count (color = 'blue' ИЛИ ​​NULL) как blueCount
из боулинга
группировать по стилям обуви;
  

Автор комментария сказал: «Необходимо OR NULL , иначе вы просто подсчитаете все строки в группе.» Почему это?

Если опущено OR NULL , результатом выражения будет логическое значение TRUE или FALSE , которые являются фактическими значениями. Функция COUNT считает любое существующее значение, а не то, является ли оно ИСТИНА, или ЛОЖЬ, , поэтому запрос работает правильно.

С другой стороны, результатом выражения color = 'green' OR NULL является либо TRUE , либо NULL .Булевы выражения при вычислении замыкаются. Как только первое подвыражение в логическом выражении OR истинно, весь результат будет истинным, поэтому, когда цвет зеленый, выражение сразу становится ИСТИНА — значение COUNT -able. Если цвет не зеленый, выражение принимает вид FALSE OR NULL , что, конечно, равно NULL , а не COUNT -able значение.

Вы можете увидеть это в действии с помощью следующих запросов:

  mysql> выберите true или null;
+ -------------- +
| истина или ноль |
+ -------------- +
| 1 |
+ -------------- +
1 ряд в комплекте (0.00 сек)

mysql> выберите false или null;
+ --------------- +
| ложь или ноль |
+ --------------- +
| NULL |
+ --------------- +
1 ряд в комплекте (0,00 сек)
  

SQL имеет значение NULL, SQL не равно NULL

Иногда у нас есть столбцы, в которых разрешены значения NULL. В таких случаях очень полезен оператор SQL IS NULL.

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

Давайте подробно обсудим оператор IS NULL.

SQL IS NULL

Давайте быстро рассмотрим синтаксис оператора SQL is null.

 
ВЫБЕРИТЕ столбцы FROM table_name WHERE column IS NULL;
  

Вышеупомянутый запрос даст нам набор результатов, в котором указанное значение столбца равно нулю.

Мы рассмотрим вышеупомянутый синтаксис более подробно на некоторых примерах.

Давайте рассмотрим следующую таблицу учеников для примера цели.

Сценарий : получить процент учащихся, чей возраст нулевой.

 
ВЫБЕРИТЕ StudentPercent ОТ Student, ГДЕ StudentAge ЕСТЬ NULL;
  

Вывод:

Пустая строка Oracle эквивалентна NULL

Null не ограничивается только ключевым словом null в базе данных Oracle, фактически, столбцы с пустым значением также считаются пустыми при использовании оператора IS NULL. Обратите внимание, что эти типы столбцов должны быть VARCHAR, а не CHAR.

Мы обнаружили аналогичное поведение для базы данных PostgreSQL, но не для базы данных MySQL. Так что это похоже на поведение базы данных.

Давайте рассмотрим следующую таблицу поставщиков для примера цели.

Сценарий : Получите название продукта, если имя поставщика не доступно.

 
ВЫБЕРИТЕ ProductName ОТ поставщика WHERE SupplierName IS NULL;
  

Выход:

SQL IS NOT NULL

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

Давайте подробно обсудим оператор IS NOT NULL.

Синтаксис :

 
ВЫБЕРИТЕ столбец (столбцы) FROM table_name WHERE Column IS NOT NULL;
  

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

Рассмотрим для примера ранее определенную таблицу Supplier.

Сценарий : Получите имя продукта, имя поставщика которого не равно нулю.

 
ВЫБЕРИТЕ ProductName ОТ поставщика, ГДЕ SupplierName НЕ НУЛЕНО;
  

Выход :

.