Oracle sql не равно null

Обновлено: 07.07.2024

Мы хотим, чтобы запрос включал строки, где col1 равен null (а col2 = 'something'). В настоящее время запрос не будет делать этого для строк, где col1 равен null. Является ли приведенный ниже запрос лучшим и самым быстрым способом?

В качестве альтернативы мы могли бы при необходимости обновить все значения col1 null до пустых строк. Будет ли это лучшим подходом? Тогда наш первый запрос сработает.

У меня есть сводная таблица фактов в моем хранилище данных SQL Server, которая содержит заказ QTY по номеру заказа и типу продукта. Я хотел бы использовать это для создания таблицы, которая показывает состав Заказа (кол-во каждого типа) в качестве текстового поля для отчетности. Исходные данные.

В Oracle нет никакой разницы между пустой строкой и NULL.

Это вопиющее пренебрежение стандартом SQL, но вот вам .

Кроме того, вы не можете сравнивать NULL (или не NULL) с операторами "normal": "col1 = null" не будет работать, "col1 = '' " will not work, "col1 != null" не будет работать, вы должны использовать "is null".

Так что нет, вы не можете заставить это работать каким-либо другим способом, кроме "col 1 is null" или какой-либо вариации на эту тему (например, с помощью nvl).

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

Что касается производительности, на мой взгляд, в этом случае это не большая разница, если в предложении уже есть сравнение!=, обычно оптимизатор не будет использовать индекс в этом столбце, потому что селективности недостаточно, поэтому более различающий фильтр будет другой стороной условия "and".

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

Хотя не самый читаемый - Oracle имеет функцию LNNVL, которая по сути является функцией not(), но инвертирует поведение для нулей. Это означает, что сравнение чего-либо с null внутри lnnvl вернет true (я не знаю, какие последствия для производительности это может иметь).

Чтобы сделать то, что вы хотите, в одном заявлении:

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

Это зависит от ваших данных, но большинство оптимизаторов будут смотреть на col2 перед col1, так как = - это более простой индекс, чем !=.

В противном случае существуют различные способы ускорить этот запрос. Вероятно, это лучше всего сделать (col1 != 'blah' или col1-null), но некоторые базы данных позволяют индексировать функцию. Таким образом, вы можете индексировать coalesce(col1, 0) и получать хорошую производительность.

На самом деле это зависит от ваших данных и вашей таблицы.

Если вы хотите ускорить этот вид запроса, и вы находитесь на Oracle 10g или более поздней версии, используйте индекс на основе функций, чтобы превратить эти NULLs в значения:

База данных, скорее всего, будет использовать индекс в этом сценарии (конечно, в зависимости от решения CBO, на которое влияет количество строк и точность статистики). В запросе MUST используется точное выражение, указанное в индексе - в данном случае " NVL(col1,'***NULL***') "

Конечно, выберите значение для '***NULL***' , которое не будет конфликтовать ни с какими данными в col1!

Приведенный ниже запрос SQL работает для меня, но он возвращает значение свойства, когда staffNo равно null 6 раз(по одному разу для каждого сотрудника). Я хочу вернуть собственность владельцу, даже если staffNo - это null, без 6 возвратов. SELECT s.branchNo, s.staffNo, s.fName, s.lName.

Я выбираю id там, где он не может быть равен 0, и значение не может быть (null) . Я написал: WHERE id IS NOT NULL (+) and id not in ('0') У меня 2 ошибки Ошибка (9,5): PL/SQL: SQL оператор игнорируется Ошибка(38,119): PL/SQL: ORA-00933: SQL команда не завершена должным образом Я меняю его на.

В oracle используйте функцию nvl

А как насчет этого варианта? Я думаю, что это может сработать, если ваша ценность никогда не будет null.

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

*this-это единственный "wrong", но это нормально, так как наша ценность никогда не null

Обновление

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

Обновление 2

Это решение работает, если ваше значение никогда не будет null (соответствует таблице истинности выше)

Я думаю, что ваше увеличение будет минимальным при изменении значений NULL на строки"". Однако если 'blah' не является null, то он должен включать значения NULL.

EDIT: Наверное, я удивлен, почему за меня проголосовали здесь. Если 'blah', если не null или пустая строка, то это никогда не должно иметь значения, так как вы уже проверяете, не равен ли COL1 'blah', который является NOT a NULL или пустой строкой.

Похожие вопросы:

У меня есть следующее заявление SQL: SELECT r.NAME AS REGIONNAME, s.NAME AS STADTNAME, f.ADRESSE AS STRASSEADRESSE FROM REGION r LEFT OUTER JOIN STADT s ON s.REGION_ID = r.ID LEFT OUTER JOIN.

Я читал эту статью: Получить null == null в SQL И консенсус заключается в том, что при попытке проверить равенство между двумя (обнуляемыми) столбцами sql правильным подходом является: where ((A=B).

У меня возникли проблемы с некоторыми запросами SQL, которые я не могу понять. SELECT * FROM MasterList WHERE Requested <> Yes; Если я выполню вышеприведенный запрос в своей таблице, то получу.

Я построил конкатенацию, используя SQL (Oracle), но я хочу, чтобы конкатенация выводилась только тогда, когда значение в поле не равно null. Я эффективно строю сайт URL в поле, но в некоторых.

Я выбираю id там, где он не может быть равен 0, и значение не может быть (null) . Я написал: WHERE id IS NOT NULL (+) and id not in ('0') У меня 2 ошибки Ошибка (9,5): PL/SQL: SQL оператор.

Я просмотрел несколько примеров, но не могу понять, почему это утверждение SQL говорит Incorrect syntax near IS and THEN Я пытаюсь установить столбец status_date в зависимости от того, была ли.

У меня есть запрос linq с предложением where, как показано ниже var ExistingGroupDataSource = (from ppatg in dbContext.XXXXXXXXX join pd1 in dbContext.XXXXXXXXXXX on ppatg.ScheduleID equals.

Как вернуть значение по умолчанию, если значение столбца равно null в SQL

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

Основная особенность NULLа заключается в том, что он не равен ничему, даже другому NULLу. С ним нельзя сравнить какое-либо значение с помощью любых операторов: = , < , > , like … Даже выражение NULL != NULL не будет истинным, ведь нельзя однозначно сравнить одну неизвестность с другой. Кстати, ложным это выражение тоже не будет, потому что при вычислении условий Oracle не ограничивается состояниями ИСТИНА и ЛОЖЬ . Из-за наличия элемента неопределённости в виде NULLа существует ещё одно состояние — НЕИЗВЕСТНО .

Таким образом, Oracle оперирует не двухзначной, а трёхзначной логикой. Эту особенность заложил в свою реляционную теорию дедушка Кодд, а Oracle, являясь реляционной СУБД, полностью следует его заветам. Чтобы не медитировать над “странными” результатами запросов, разработчику необходимо знать таблицу истинности трёхзначной логики. Ознакомиться с ней можно, например, на английской википедии: Three-valued_logic.

Для удобства сделаем процедуру, печатающую состояние булевого параметра:

Привычные операторы сравнения пасуют перед NULLом:

Существуют специальные операторы IS NULL и IS NOT NULL , которые позволяют производить сравнения с NULLами. IS NULL вернёт истину, если операнд имеет значение NULL и ложь, если он им не является.

Соответственно, IS NOT NULL действует наоборот: вернёт истину, если значение операнда отлично от NULLа и ложь, если он является NULLом:

Кроме того, есть пара исключений из правил, касающихся сравнений с отсутствующими значениями. Во-первых, — это функция DECODE , которая считает два NULLа эквивалентными друг другу. Во-вторых, — это составные индексы: если два ключа содержат пустые поля, но все их непустые поля равны, то Oracle считает эти два ключа эквивалентными.

DECODE идёт против системы:

Пример с составными индексами находится в параграфе про индексы.

Обычно, состояние НЕИЗВЕСТНО обрабатывается так же, как ЛОЖЬ . Например, если вы выбираете строки из таблицы и вычисление условия x = NULL в предложении WHERE дало результат НЕИЗВЕСТНО , то вы не получите ни одной строки. Однако, есть и отличие: если выражение НЕ(ЛОЖЬ) вернёт истину, то НЕ(НЕИЗВЕСТНО) вернёт НЕИЗВЕСТНО . Логические операторы AND и OR также имеют свои особенности при обработке неизвестного состояния. Конкретика в примере ниже.

В большинстве случаев неизвестный результат обрабатывается как ЛОЖЬ :

Отрицание неизвестности даёт неизвестность:

Оператор OR :

Оператор AND :

Для начала сделаем несколько предварительных действий. Для тестов создадим таблицу T с одним числовым столбцом A и четырьмя строками: 1, 2, 3 и NULL

Включим трассировку запроса (для этого надо обладать ролью PLUSTRACE ).
В листингах от трассировки оставлена только часть filter, чтобы показать, во что разворачиваются указанные в запросе условия.

Предварительные действия закончены, давайте теперь поработаем с операторами. Попробуем выбрать все записи, которые входят в набор (1, 2, NULL) :

Как видим, строка с NULLом не выбралась. Произошло это из-за того, что вычисление предиката "A"=TO_NUMBER(NULL) вернуло состояние НЕИЗВЕСТНО . Для того, чтобы включить NULLы в результат запроса, придётся указать это явно:

Попробуем теперь с NOT IN :

Вообще ни одной записи! Давайте разберёмся, почему тройка не попала в результаты запроса. Посчитаем вручную фильтр, который применила СУБД, для случая A=3 :

Из-за особенностей трёхзначной логики NOT IN вообще не дружит с NULLами: как только NULL попал в условия отбора, данных не ждите.

Здесь Oracle отходит от стандарта ANSI SQL и провозглашает эквивалентность NULLа и пустой строки. Это, пожалуй, одна из наиболее спорных фич, которая время от времени рождает многостраничные обсуждения с переходом на личности, поливанием друг друга фекалиями и прочими непременными атрибутами жёстких споров. Судя по документации, Oracle и сам бы не прочь изменить эту ситуацию (там сказано, что хоть сейчас пустая строка и обрабатывается как NULL, в будущих релизах это может измениться), но на сегодняшний день под эту СУБД написано такое колоссальное количество кода, что взять и поменять поведение системы вряд ли реально. Тем более, говорить об этом они начали как минимум с седьмой версии СУБД (1992-1996 годы), а сейчас уже двенадцатая на подходе.

NULL и пустая строка эквивалентны:

непременный атрибут жёсткого спора:

Если последовать завету классика и посмотреть в корень, то причину эквивалентности пуcтой строки и NULLа можно найти в формате хранения varchar`ов и NULLов внутри блоков данных. Oracle хранит строки таблицы в структуре, состоящей из заголовка, за которым следуют столбцы данных. Каждый столбец представлен двумя полями: длина данных в столбце (1 или 3 байта) и, собственно, сами данные. Если varchar2 имеет нулевую длину, то в поле с данными писать нечего, оно не занимает ни байта, а в поле с длиной записывается специальное значение 0xFF , обозначающее отсутствие данных. NULL представлен точно так же: поле с данными отсутствует, а в поле с длиной записывается 0xFF . Разработчики Оракла могли бы, конечно, разделить эти два состояния, но так уж издревле у них повелось.

Лично мне эквивалентность пустой строки и NULLа кажется вполне естественной и логичной. Само название «пустая строка» подразумавает отсутствие значения, пустоту, дырку от бублика. NULL, в общем-то, обозначает то же самое. Но здесь есть неприятное следствие: если про пустую строку можно с уверенностью сказать, что её длина равна нулю, то длина NULLа никак не определена. Поэтому, выражение length('') вернёт вам NULL, а не ноль, как вы, очевидно, ожидали. Ещё одна проблема: нельзя сравнивать с пустой строкой. Выражение val = '' вернёт состояние НЕИЗВЕСТНО , так как, по сути, эквивалентно val = NULL .

Длина пустой строки не определена:

Сравнение с пустой строкой невозможно:

Критики подхода, предлагаемого Ораклом, говорят о том, что пустая строка не обязательно обозначает неизвестность. Например, менеджер по продажам заполняет карточку клиента. Он может указать его контактный телефон (555-123456), может указать, что он неизвестен (NULL), а может и указать, что контактный телефон отсутствует (пустая строка). С оракловым способом хранения пустых строк реализовать последний вариант будет проблемно. С точки зрения семантики довод правильный, но у меня на него всегда возникает вопрос, полного ответа на который я так и не получил: как менеджер введёт в поле «телефон» пустую строку и как он в дальнейшем отличит его от NULLа? Варианты, конечно, есть, но всё-таки…

Вообще-то, если говорить про PL/SQL, то где-то глубоко внутри его движка пустая строка и NULL различаются. Один из способов увидеть это связан с тем, что ассоциативные коллекции позволяют сохранить элемент с индексом '' (пустая строка), но не позволяют сохранить элемент с индексом NULL:

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

Этот маленький абзац писался пятничным вечером под пиво, на фоне пятничного РЕН-ТВшного фильма. Переписывать его лень, уж извините.

Задача. У Маши до замужества с Колей было неизвестное количество любовников. Коля знает, что после замужества у Маши был секс с ним, Сашей и Витей. Помогите найти Коле точное количество любовников Маши.

Очевидно, что мы ничем не сможем помочь Коле: неизвестное количество любовников Маши до замужества сводит все расчёты к одному значению — неизвестно. Oracle, хоть и назвался оракулом, в этом вопросе уходит не дальше, чем участники битвы экстрасенсов: он даёт очевидные ответы только на очевидные вопросы. Хотя, надо признать, что Oracle гораздо честнее: в случае с Колей он не будет заниматься психоанализом и сразу скажет: «я не знаю»:

С конкатенацией дела обстоят по другому: вы можете добавить NULL к строке и это её не изменит. Такая вот политика двойных стандартов.

Почти все агрегатные функции, за исключением COUNT (и то не всегда), игнорируют пустые значения при расчётах. Если бы они этого не делали, то первый же залетевший NULL привёл бы результат функции к неизвестному значению. Возьмём для примера функцию SUM , которой необходимо просуммировать ряд (1, 3, null, 2) . Если бы она учитывала пустые значения, то мы бы получили такую последовательность действий:
1 + 3 = 4; 4 + null = null; null + 2 = null .
Вряд ли вас устроит такой расчёт при вычислении агрегатов, ведь вы наверняка не это хотели получить. А какой бы был геморрой с построением хранилищ данных… Бррррр…

Таблица с данными. Используется ниже много раз:

Пустые значения игнорируются агрегатами:

Функция подсчёта количества строк COUNT , если используется в виде COUNT(*) или COUNT(константа) , будет учитывать пустые значения. Однако, если она используется в виде COUNT(выражение) , то пустые значения будут игнорироваться.

с константой:

С выражением:

Также, следует быть осторожным с функциями вроде AVG . Поскольку она проигнорирует пустые значения, результат по полю N будет равен (1+3+2)/3 , а не (1+3+2)/4 . Возможно, такой расчёт среднего вам не нужен. Для решения подобных проблем есть стандартное решение — воспользоваться функцией NVL :

Агрегатные функции возвращают состояние НЕИЗВЕСТНО , если они применяются к пустому набору данных, либо если он состоит только из NULLов. Исключение составляют предназначенные для подсчёта количества строк функции REGR_COUNT и COUNT(выражение) . Они в перечисленных выше случаях вернут ноль.

Набор данных только из NULLов:

Пустой набор данных:

NULL в OLAP

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

В общем, такая вот краткая и сумбурная информация про дуализм NULLа в многомерном анализе. Ниже пример использования GROUPING , а за подробностями велкам ту Data Warehousing Guide, глава 21.

Удобная фишка sqlplus: при выводе данных заменяет NULL на указанную строку:

Может ли кто-нибудь объяснить следующее поведение в SQL?

<> - стандартный SQL-92; != - его эквивалент. Оба оценивают значения, которые не являются NULL - NULL - это заполнитель, указывающий на отсутствие значения.

Вот почему вы можете использовать только IS NULL / IS NOT NULL в качестве предикатов для таких ситуаций.

Это поведение не характерно для SQL Server. Все диалекты SQL, соответствующие стандартам, работают одинаково.

Примечание . Для сравнения, если ваше значение не равно нулю , вы используете IS NOT NULL , а для сравнения с не нулевым значением вы используете <> 'YOUR_VALUE' . Я не могу сказать, равно ли мое значение NULL или нет, но я могу сказать, является ли мое значение NULL или NOT NULL. Я могу сравнить, отличается ли мое значение от NULL.

NULL - это ничего . неизвестно. NULL ничего не равно. Вот почему вам нужно использовать волшебную фразу IS NULL вместо = NULL в ваших SQL-запросах.

Я хотел бы предложить этот код, который я сделал, чтобы узнать, есть ли изменение в значении, i - новое значение, а d - старое (хотя порядок не имеет значения). В этом отношении изменение от значения к нулю или наоборот - это изменение, а от нуля к нулю - нет (конечно, от значения к другому значению это изменение, но от значения к тому же нет).

Чтобы использовать эту функцию, вы можете

Использование sql_variant делает его совместимым с множеством типов.

Старый вопрос, но следующее может предложить более подробную информацию.

null не представляет значения или неизвестное значение. В нем не указывается, почему нет значения, что может привести к некоторой двусмысленности.

Предположим, вы запускаете такой запрос:

То есть вы ищете строки, в которых даты ordered и delivered совпадают.

Чего ожидать, если один или оба столбца равны нулю?

Поскольку по крайней мере одна из дат неизвестна, нельзя ожидать, что эти две даты совпадают. Это также относится к случаям, когда обе даты неизвестны: как они могут быть одинаковыми, если мы даже не знаем, какие они?

По этой причине любое выражение, обрабатывающее null как значение, должно завершиться ошибкой. В этом случае он не будет совпадать. Это также верно, если вы попробуете следующее:

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

В SQL есть специальный тест на пропущенные значения:

В частности, это не сравнение значений, а поиск отсутствующих значений.

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

NULL Невозможно сравнить ни с одним значением с помощью операторов сравнения. NULL = NULL ложно. Null - это не значение. Оператор IS специально разработан для обработки NULL-сравнений.

Единственный тест на NULL - IS NULL или IS NOT NULL. Проверка на равенство бессмысленна, потому что по определению неизвестно, каково значение.

Вот статья в Википедии, которую стоит прочитать:

В SQL все, что вы оцениваете / вычисляете с NULL , приводит к НЕИЗВЕСТНОМУ

Вот почему SELECT * FROM MyTable WHERE MyColumn != NULL или SELECT * FROM MyTable WHERE MyColumn <> NULL не дают результатов.

Для проверки значений NULL предоставляется функция isNull.

Кроме того, вы можете использовать оператор IS , как в третьем запросе.

Надеюсь это поможет.

Для возврата всех строк, в которых MyColumn имеет значение NULL, или всех строк, в которых MyColumn является пустой строкой. Для многих «конечных пользователей» проблема NULL и пустой строки - это различие без необходимости и путаницы.

Я просто не вижу функциональной и простой причины, по которой значения NULL не могут быть сопоставимы с другими значениями или другими значениями NULL, потому что мы можем четко сравнить их и сказать, что они одинаковы или нет в нашем контексте. Это забавно. Просто из-за каких-то логических выводов и непротиворечивости нам нужно постоянно с этим возиться. Это не функционально, сделайте его более функциональным и оставьте философам и ученым решать, согласован он или нет, и придерживается ли он «универсальной логики». :) Кто-то может сказать, что это из-за индексов или чего-то еще, я сомневаюсь, что эти вещи нельзя было сделать так, чтобы поддерживать нули так же, как значения. Это то же самое, что и сравнение двух пустых стаканов, один - винный стакан, а другой - пивной бокал, мы сравниваем не типы объектов, а значения, которые они содержат, так же, как вы могли бы сравнить int и varchar, с null это еще проще, это ничего и что два небытия имеют общего, они одинаковы, они явно сопоставимы для меня и для всех, кто пишет sql, потому что мы постоянно нарушаем эту логику, сравнивая их странным образом из-за некоторых стандартов ANSI. Почему бы не использовать мощность компьютера, чтобы сделать это за нас, и я сомневаюсь, что это замедлит работу, если все, что связано с этим, будет построено с учетом этого. «Это не ноль, это ничто», это не яблоко, это апфел, давай . Функционально твой друг, и здесь тоже есть логика. В конце концов, единственное, что имеет значение, - это функциональность, и использование нулей таким образом обеспечивает большую или меньшую функциональность и простоту использования. Это более полезно?

Рассмотрим этот код:

Вот код, который привел меня сюда:

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

Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».

В первой части статьи мы рассмотрели такие темы:

В этой части мы рассмотрим:

Операторы для проверки существования (IN / NOT IN)

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

Аналогично, для отрицания используется NOT IN :

Частичное совпадение — использование LIKE

Иногда нам нужно найти строки, основываясь на частичном совпадении.

Допустим, мы хотим найти всех пользователей, которые зарегистрировались в нашем приложении при помощи адреса Gmail. Мы можем поискать частичное совпадение в столбце, используя ключевое слово LIKE . Также при этом можно использовать групповой символ — % .

Чтобы поиск не зависел от регистра, нужно заменить LIKE на ILIKE :

Мы также можем использовать столько групповых символов, сколько нам нужно.

Например, поиск %j%o% вернет любой email-адрес, соответствующий шаблону «<все-что-угодно>, за чем следует j, за чем следует <все-что-угодно>, за чем следует o, за чем следует <все-что-угодно>»:

Работа с отсутствующими данными (NULL)

Давайте посмотрим, как быть со столбцами и строками, где нет данных.

Для этого давайте добавим в нашу таблицу users еще один столбец: first_paid_at .

Этот новый столбец будет TIMESTAMP (подобно datetime в других языках) и будет представлять дату и время, когда пользователь впервые заплатил нам за наше приложение. Может, мы хотим послать ему открытку и цветы в честь годовщины.

Мы могли бы стереть нашу таблицу users , введя DROP TABLE users , и пересоздать ее заново, но таким образом мы удалили бы все данные в таблице.

Чтобы изменить таблицу, не стирая ее и не лишаясь данных, можно использовать ALTER TABLE :

Эта команда возвращает результат ALTER TABLE , так что наш запрос ALTER сработал успешно.

Если мы теперь запросим нашу таблицу users , мы заметим, что теперь в ней появился новый столбец без данных:

Наш столбец first_paid_at пуст, и результат нашего psql-запроса показывает, что это пустой столбец. Технически он не пустой: в нем содержится специальное значение, которое psql просто не показывает в выводе — NULL .

NULL это специальное значение в базах данных. Это отсутствие значения, и оно ведет себя не так, как можно было бы ожидать.

Чтобы это продемонстрировать, давайте посмотрим на простой SELECT :

Здесь мы просто выбрали 1 = 1 и 1 = 2 . Как мы и ожидали, результат этих двух предложений — t и f (или TRUE и FALSE ). 1 равен 1, но 1 не равен 2.

Теперь давайте попробуем проделать то же самое с NULL :

Мы могли ожидать, что значением будет FALSE , но на деле возвращается значение NULL .

Чтобы еще лучше визуализировать NULL , давайте при помощи опции \pset посмотрим, как psql отображает NULL-значения:

Если мы запустим этот запрос еще раз, мы увидим в выводе ожидаемый нами NULL :

Итак, 1 не равен NULL , а как насчет NULL = NULL ?

Довольно странно, однако NULL не равен NULL .

NULL лучше представлять себе как неизвестное значение. Равно ли неизвестное значение единице? Мы не знаем, оно же неизвестное. Равно ли неизвестное значение неизвестному значению? Опять же, мы этого не знаем. Это немного лучше поясняет, что такое NULL .

Использование IS NULL и IS NOT NULL

Мы не можем использовать с NULL оператор равенства, но мы можем пользоваться двумя специально созданными для этого операторами: IS NULL и IS NOT NULL .

Эти значения ожидаемы: NULL IS NULL — истина, NULL IS NOT NULL — ложь.

Это все прекрасно и очень интересно, но как это применять на практике?

Что ж, для начала давайте заведем какие-то данные в нашем столбце first_paid_at :

В приведенной выше инструкции UPDATE мы задали значения для столбца first_paid_at у троих разных пользователей: пользователю с ID 1 — текущее время ( NOW() ), пользователю с ID 2 — текущее время минус месяц, а пользователю с ID 3 — текущее время минус год.

Во-первых, давайте найдем пользователей, которые нам уже платили, и пользователей, которые пока этого не делали:

Операторы сравнения при работе с датами и временем

Теперь, когда у нас есть кое-какие данные, давайте используем те же операторы сравнения применительно к новому полю TIMESTAMP .

Попробуем найти пользователей, которые совершили платеж на протяжении последней недели. Для этого мы можем взять текущее время ( NOW() ) и вычесть из него одну неделю при помощи ключевого слова INTERVAL :

Мы также можем использовать другой интервал, например, последние три месяца:

Теперь давайте найдем пользователей, которые совершали платеж в промежутке от одного до шести месяцев назад.

Мы можем скомбинировать наши условия, используя AND , но вместо использования операторов < и > давайте используем ключевое слово BETWEEN :

Проверка существования с использованием EXISTS / NOT EXISTS

Другой способ проверить существование (наличие) значения — использовать EXISTS и NOT EXISTS .

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

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

Это простая таблица. Она содержит только ID, поле для хранения текста поста ( body ) и ссылку на пользователя, который написал этот пост ( user_id ).

Давайте добавим в новую таблицу некоторые данные:

Согласно добавленным данными, у пользователя с ID 1 есть два поста, у пользователя с ID 2 — один пост, у пользователя с ID 3 — тоже один пост.

Чтобы найти пользователей, у которых есть посты, мы можем использовать ключевое слово EXISTS .

EXISTS принимает подзапрос. Если этот подзапрос возвращает что-либо (даже строку со значением NULL ), база данных включит эту строку в результат.

EXISTS проверяет лишь существование строки из подзапроса, ему не важно, что именно содержится в этой строке.

Вот пример выборки пользователей, имеющих посты:

Как и ождилалось, мы получили пользователей с ID 1, 2 и 3.

Наш подзапрос EXISTS проверяет записи в таблице posts, где user_id поста совпадает со столбцом id таблицы users. Мы вернули 1 в нашем SELECT , потому что здесь мы можем вернуть что угодно: база данных просто хочет видеть, что что-то вернулось.

Аналогично, мы можем найти пользователей, у которых нет постов. Для этого нужно заменить EXISTS на NOT EXISTS :

Наконец, мы можем переписать наш запрос и использовать IN или NOT IN вместо EXISTS или NOT EXISTS :

Технически это сработает, но вообще, если вы проверяете существование другое записи, более производительно будет использовать EXISTS . Операторы IN и NOT IN в целом лучше применять для проверки значения в статическом списке, как мы делали ранее:

Поразрядные операторы

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

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

В качестве примера давайте рассмотрим поразрядный оператор «and»: & .

Чтобы осуществить поразрядную операцию, нам сначала нужно преобразовать значения в нашем столбце age из целых чисел в бинарный формат. В данном случае мы использовали ::bit(8) и получили восьмибитовые строки.

Далее мы можем «сложить» результат в бинарном формате с другой строкой в бинарном формате — 11111111 . Поскольку бинарный AND возвращает единицу только если оба бита это единицы, эта добавочная строка делает вывод интересным.

Практически все остальные поразрядные операторы используют тот же формат:

Поразрядный оператор «not» (

) немного отличается. Он применяется к одному термину, так же, как и обычный оператор NOT :

И, наконец, самый полезный из поразрядных операторов: конкатенация.

Этот оператор обычно используется для склейки вместе строк текста. Например, если мы хотим составить вычисленное «полное имя» для пользователей, мы можем воспользоваться конкатенацией:

Здесь мы для создания значения name сконкатенировали (скомбинировали) first_name , пробел ( ' ' ) и last_name .

Заключение

Итак, мы рассмотрели практически все операторы фильтрации, котоыре вам могут понадобиться в работе!

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

От редакции Techrocks: возможно, вам будет интересна еще одна статья того же автора: SQL JOIN: руководство по объединению таблиц.

Читайте также: