Coalesce oracle: COALESCE ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Функция COALESCE() в Oracle

В Oracle Database функция COALESCE() возвращает первое ненулевое выражение в списке выражений.

Синтаксис

Синтаксис выглядит следующим образом:

 COALESCE(expr [ expr ]...)

Необходимо передать как минимум два выражения.

Пример

Вот пример для демонстрации:

 SELECT COALESCE(null, 7)
ОТ ДВОЙНОЙ;

Результат:

Вот еще несколько примеров:

 SET NULL 'нулевой';
ВЫБИРАТЬ
    ОБЪЕДИНЯЙТЕ(null, null, 1, 2, 3) КАК "r1",
    ОБЪЕДИНИТЬ(1, ноль, 2, 3) КАК "r2",
    ОБЪЕДИНИТЬ(null, 3, 2, 1) КАК "r3",
    ОБЪЕДИНИТЬ(1, 2, 3, ноль) КАК "r4",
    ОБЪЕДИНЯТЬ (нуль, ноль) КАК "r5"
ОТ ДВОЙНОЙ;

Результат:

 r1 r2 r3 r4 r5
_____ _____ _____ _____ _______
    1 1 3 1 null

Мы видим, что COALESCE() возвращает null , когда все аргументы равны null.

По первой строке SET NULL 'нуль'; , я добавил это, чтобы мой сеанс SQLcl возвращал null всякий раз, когда результат равен нулю.

По умолчанию SQLcl и SQL*Plus возвращают пустое место всякий раз, когда null возникает в результате оператора SQL SELECT .

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

Пример базы данных

Предположим, мы выполняем следующий запрос:

 SET NULL 'нулевой';
ВЫБИРАТЬ
    LOCATION_ID,
    ШТАТ_ПРОВИНЦИЯ
ИЗ МЕСТОПОЛОЖЕНИЙ
ЗАКАЗАТЬ ПО LOCATION_ID ASC
ВЫБЕРИТЕ ТОЛЬКО ПЕРВЫЕ 6 РЯДОВ;

Результат:

 LOCATION_ID STATE_PROVINCE
______________ ___________________
          1000 ноль
          1100 ноль
          1200 Префектура Токио
          1300 ноль
          1400 Техас
          1500 Калифорния

Мы видим, что несколько рядов равны null в столбце STATE_PROVINCE .

Вот снова запрос, за исключением того, что на этот раз мы используем COALESCE() для столбца STATE_PROVINCE :

 SELECT
    LOCATION_ID,
    COALESCE(STATE_PROVINCE, 'Н/Д')
ИЗ МЕСТОПОЛОЖЕНИЙ
ЗАКАЗАТЬ ПО LOCATION_ID ASC
ВЫБЕРИТЕ ТОЛЬКО ПЕРВЫЕ 6 РЯДОВ;

Результат:

 LOCATION_ID COALESCE(STATE_PROVINCE,'N/A')
______________ _________________________________
          1000 н/д
          1100 н/д
          1200 Префектура Токио
          1300 н/д
          1400 Техас
          1500 Калифорния

Таким образом, мы использовали COALESCE() для возврата Н/Д всякий раз, когда встречалось нулевое значение.

COALESCE() vs CASE

Следующее:

 COALESCE(expr1, expr2)

Эквивалентно этому:

 CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

Недопустимый счетчик аргументов

Вызов функции без передачи аргументов приводит к ошибке:

 SELECT COALESCE()
ОТ ДВОЙНОЙ;

Результат:

 Ошибка SQL: ORA-00938: недостаточно аргументов для функции
00938. 00000 - "недостаточно аргументов для функции"

SQL COALESCE, ISNULL, NULLIF в SQL Server, Oracle и PostgreSQL

Автор: Andrea Gnemmi   |
Обновлено: 21 марта 2022 г.   |
Комментарии (2)   | Связанный: Подробнее > Другие платформы баз данных

Проблема

Иногда возникает необходимость оценить, имеет ли переменная, выражение или столбец
нет связанного значения, так что оно равно NULL. Функции COALESCE() и ISNULL()
может быть использован
для этого и рассмотрим примеры из SQL Server, Oracle и PostgreSQL.

Решение

В этом руководстве мы рассмотрим различные синтаксис и альтернативы
COALESCE() и ISNULL(), а также их альтернативы в SQL Server, Oracle и
ПостгресSQL.

В этом руководстве по SQL будет использоваться учебная база данных Chinook, доступная в нескольких форматах СУБД. Chinook — это симуляция магазина цифровых медиа с демонстрационными данными.
Загрузите нужную вам версию и весь код для вставки данных.

Пример синтаксиса функции SQL Server

В базах данных SQL у нас есть выражение COALESCE() и функция ISNULL() в
чтобы оценить выражение NULL в хранимых процедурах, SELECT с WHERE
логика предложения, SQL DELETE и скрипты. Этот код имеет некоторые отличия, которые мы
исследовать в следующих примерах.

Функция SQL Server COALESCE

Прежде всего, давайте рассмотрим пример SQL-запроса с COALESCE(). Предполагать
что нам нужно вернуть данные из таблицы Customer и если столбец Company равен NULL,
нам нужно вернуть конкретное значение.

 SELECT объединить (компания, «Нет компании») как компанию
ОТ Заказчика

Это довольно типичный пример функции SQL COALESCE!

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

Давайте продемонстрируем еще один пример добавления колонки факса. Если существует ценность для компании,
он покажет, что, если нет и существует значение для факса, он покажет это, и если
не будет отображаться «Нет компании» в следующем примере.

 ВЫБЕРИТЕ объединение (компания, факс, «Нет компании») как Компания
ОТ Заказчика

Пока все в порядке, номер факса ставится, если компания нулевая или «Нет компании»
если оба столбца пусты, но что произойдет, если мы попытаемся добавить другие данные результата
тип, такой как целое число с данными varchar?

 ВЫБЕРИТЕ объединение (компания, факс, «Нет компании», 1) как Компания
ОТ Заказчика

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

Функция SQL Server ISNULL

Посмотрим, как ведет себя ISNULL() на тех же примерах, главное отличие
что с этой функцией у нас может быть только два аргумента.

 SELECT isnull(company,'No Company') as Company
ОТ Заказчика

Пока это то же самое, теперь давайте посмотрим на изменение второго аргумента и использование
целое число, как в примере COALESCE().

 SELECT isnull(company,1) as Company
ОТ Заказчика

На этот раз это работает, потому что ISNULL(), будучи функцией, получает
тип данных результата из первого аргумента, в нашем случае столбцы компании, который является nvarchar
и поэтому целочисленное значение 1 может быть преобразовано в этот тип данных.

Пример синтаксиса функции Oracle

В Oracle есть две функции, выполняющие одну и ту же работу: COALESCE() и NVL().
последняя является старой проприетарной функцией PL/SQL, которая ведет себя как ISNULL() в SQL.
Сервер.

Функция Oracle COALESCE

Попробуем те же примеры.

 SELECT объединить (компания, «Нет компании») как компанию
ОТ CHINOOK.Клиент;

Ничего не отличается от того же выражения в SQL Server, давайте посмотрим, как функция COALESCE() ведет себя в Oracle в отношении типа данных результата (да
в Oracle это функция, а не выражение, как в SQL Server).

Попробуем
те же примеры, что и в SQL Server.

 ВЫБЕРИТЕ объединение (компания, факс, «Нет компании») как Компания
ОТ Чинук.Клиент;

То же поведение, что и в SQL Server, теперь давайте добавим число и посмотрим.

 ВЫБЕРИТЕ объединение (компания, факс, «Нет компании», 1) как Компания
ОТ Чинук.Клиент;

Почти такая же ошибка, но с разницей. Поскольку функция предполагает
результатом будет тип данных CHAR, добавление типа данных NUMBER возвращает ошибку,
поэтому в этом случае тип данных результата — это не число, а CHAR, поэтому он первый
аргумент, а не последний, что противоположно SQL Server.

Oracle NVL()

Теперь попробуем функцию NVL(). Во многом это похоже на ISNULL().
в SQL Server он имеет только два аргумента и возвращает первый, отличный от NULL.

Тот же пример, что и раньше.

 ВЫБЕРИТЕ NVL (компания, «Нет компании») в качестве компании
ОТ Чинук.Клиент;

Опять же, точно так же, как в SQL Server, теперь пример с использованием числа как
второй аргумент.

 ВЫБЕРИТЕ NVL (компания, 1) как компанию
ОТ Чинук.Клиент;

И снова такое же поведение, как и в SQL Server.

Oracle NVL2()

В Oracle у нас также есть NVL2(), который принимает три
параметры, давайте посмотрим на пример.

 ВЫБЕРИТЕ NVL2 (компания, факс, «Нет компании») в качестве компании
ОТ Чинук.Клиент;

И мы пытаемся снова использовать другой тип данных.

 ВЫБЕРИТЕ NVL2 (компания, факс, 1) в качестве компании
ОТ Чинук.Клиент;

И снова, с NVL2() ошибка не возвращается, но делается точно такой же пример
с ОБЪЕДИНЕНИЕМ().

 ВЫБЕРИТЕ объединение (компания, факс, 1) как Компания
ОТ Чинук. Клиент;

Возвращает ту же ошибку, что и раньше.

Пример синтаксиса функции PostgreSQL

Для выполнения той же условной функции в PostgreSQL у нас есть только
стандартная функция SQL COALESCE().

 SELECT объединить («Компания», «Нет компании») как компанию
ОТ «КЛИЕНТА»

Точно так же, как в SQL Server и Oracle.

Сначала попробуем следующее со значением VARCHAR.

 SELECT объединить ("Компания", "Факс", "Нет компании") как компанию
ОТ «КЛИЕНТА»

Очевидно, с теми же результатами, что и в предыдущих примерах.

Давайте попробуем с типом данных результата INTEGER.

 SELECT объединяется («Компания», «Факс», «Нет компании», 1) как компания
ОТ «КЛИЕНТА»

И снова с тем же результатом ошибки, на этот раз с сообщением, что varchar и
Целочисленные типы данных не могут быть сопоставлены. Обратите внимание, что ошибка возвращается из
COALESCE() во всех трех СУБД, даже если последний аргумент никогда не будет возвращен,
поскольку очевидно, что «Нет компании» НЕ НУЛЕВОЕ!

NULLIF Примеры функций

Во всех трех используемых СУБД присутствует другое выражение/функция.
с NULL, и это NULLIF(). Эта последняя функция имеет два аргумента и возвращает
NULL, если оба аргумента равны, в противном случае возвращается первый аргумент. Давайте
просмотрите несколько примеров!

SQL-сервер

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

 ;с инвойсом1 как
  (выберите invoiceid,customerid, total, ROW_NUMBER() over (partition by CUSTOMERid order by invoicedate ASC) as row_num
   из фактуры)
,счет2 как
  (выберите invoiceid,customerid, total, ROW_NUMBER() over (partition by CUSTOMERid order by invoicedate ASC) as row_num
   из фактуры)
 
ВЫБЕРИТЕ invoice1.invoiceid,
   инвойс1.customerid,
   Имя+' '+Фамилия как клиента,
   инвойс1.итого,
   nullif(invoice1.total,invoice2.total) as Equal_Total_Prev_Invoice
ИЗ накладной1
ВНУТРЕННЕЕ ПРИСОЕДИНЕНИЕ к клиенту по адресу customer.customerid=invoice1. customerid
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ инвойс2 по инвойсу1.CustomerId=invoice2.CustomerId и инвойсу1.row_num-1=invoice2.row_num
ГДЕ nullif(invoice1.total,invoice2.total) равно null
ЗАКАЗАТЬ ПО инвойсу1.CustomerId

Итак, здесь мы использовали два CTE с оконными функциями, чтобы получить точное
предыдущий счет и сравнить его со следующим, а затем использовать SELECT NULLIF()
для сравнения итогов и возврата NULL, если они равны.

Хорошее объяснение
оконных функций для трех СУБД можно найти здесь:

Функции окна SQL в SQL Server, Oracle и PostgreSQL.

Оракул

Давайте рассмотрим тот же пример в Oracle со следующим запросом:

 со счетом1 как
 (выберите invoiceid,customerid, total, ROW_NUMBER() over (partition by customerid order by invoicedate asc) as row_num
  от chinook.invoice)
,счет2 как
 (выберите invoiceid,customerid, total, ROW_NUMBER() over (partition by customerid order by invoicedate asc) as row_num
  от chinook.invoice)
 
ВЫБИРАТЬ
   инвойс1. invoiceid,
   инвойс1.customerid,
   Имя||' '||Фамилия клиента,
   инвойс1.итого,
   nullif(invoice1.total,invoice2.total) as Equal_Total_Prev_Invoice
ИЗ накладной1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ chinook.customer на customer.customerid=invoice1.customerid
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ инвойс2 по инвойсу1.CustomerId=invoice2.CustomerId и инвойсу1.row_num-1=invoice2.row_num
ГДЕ nullif(invoice1.total,invoice2.total) равно null
ЗАКАЗАТЬ ПО инвойсу1.CustomerId;

Точно такое же поведение.

PostgreSQL

Наконец, давайте посмотрим, как это работает на PostgreSQL.

 с инвойсом1 как
 (выберите «InvoiceId», «CustomerId», «Total», ROW_NUMBER() над (раздел по «CustomerId» в порядке возрастания «InvoiceDate») как row_num
   из "Счет")
,счет2 как
 (выберите «InvoiceId», «CustomerId», «Total», ROW_NUMBER() над (раздел по «CustomerId» в порядке возрастания «InvoiceDate») как row_num
  из "Счет")
 
ВЫБЕРИТЕ счет-фактуру1."InvoiceId",
   инвойс1."идентификатор клиента",
   "Имя"||' '||"Фамилия" как клиент,
   счет1. "Всего",
   nullif(invoice1."Total",invoice2."Total") as "Equal_Total_Prev_Invoice"
ИЗ накладной1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ "Клиент" на "Клиент"."CustomerId"=invoice1."CustomerId"
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ инвойс2 на инвойс1."CustomerId"=invoice2."CustomerId" и инвойс1.row_num-1=invoice2.row_num
ГДЕ nullif(invoice1."Total",invoice2."Total") равно null
ЗАКАЗАТЬ ПО СЧЕТУ1."CustomerId";

Опять то же самое поведение.

Заключение

В этом руководстве по SQL мы рассмотрели функции SQL (язык структурированных запросов) COALESCE(), ISNULL(),
NULLIF() и как они работают в SQL Server, Oracle и PostgreSQL. Есть и другие
способы проверки
Значения NULL, такие как предложение IS NULL и другие сложные функции в Oracle.

Следующие шаги

Как обычно ссылки на официальную документацию:
- SQL-сервер:
  Microsoft COALESCE (Transact-SQL)
- Оракул:
  Oracle ОБЪЕДИНЯЕТСЯ
- PostgreSQL:
  Условные выражения PostgreSQL
Некоторые ссылки на другие советы относительно сравнения COALESCE() и ISNULL() или NULL:
- ОБЪЕДИНЕНИЕ SQL-функции
- Выбор между COALESCE и ISNULL в SQL Server
- Некоторые сложные ситуации при работе с SQL Server NULL
- Использование функции SQL ISNULL()
- Обзор выражения SQL Server CASE
- Примеры выражений SQL Server T-SQL CASE
- Функция CONCAT и CONCAT_WS в SQL Server
- Функция SQL Server CONCAT
- Примеры SUBSTRING SQL Server
- Функция SUBSTRING SQL Server
- Примеры функций подстроки SQL с T-SQL, R и Python
- Ознакомьтесь со Справочным руководством по SQL для строковых функций