В чем разница: ifnull и coalesce в mysql?

Ответ 1

Основное различие между ними состоит в том, что функция IFNULL принимает два аргумента и возвращает первый, если он не NULL, или второй, если первый NULL.

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

SELECT IFNULL('some value', 'some other value');
-> returns 'some value'

SELECT IFNULL(NULL,'some other value');
-> returns 'some other value'

SELECT COALESCE(NULL, 'some other value');
-> returns 'some other value' - equivalent of the IFNULL function

SELECT COALESCE(NULL, 'some value', 'some other value');
-> returns 'some value'

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

ОБНОВЛЕНИЕ: MSSQL выполняет более строгий контроль типов и параметров. Кроме того, он не имеет функции IFNULL, а вместо этого функции ISNULL, которая должна знать типы аргументов. Поэтому:

SELECT ISNULL(NULL, NULL);
-> results in an error

SELECT ISNULL(NULL, CAST(NULL as VARCHAR));
-> returns NULL

Также функция COALESCE в MSSQL требует, чтобы хотя бы один параметр был не нулевым, поэтому:

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
-> results in an error

SELECT COALESCE(NULL, NULL, NULL, NULL, 'first non-null value');
-> returns 'first non-null value'

Ответ 2

Различия в SQL-Server:

• Нет функции IFNULL(), но аналогичный ISNULL()

• ISNULL принимает только 2 параметра, тогда как COALESCE принимает переменное количество параметров

• COALESCE основан на стандарте ANSI SQL, тогда как ISNULL - это собственная функция TSQL

• Проверки для ISNULL и COALESCE также различны. Например, значение NULL для ISNULL преобразуется в int, тогда как для COAELSCE вы должны указать тип. Пример:

  • ISNULL(NULL,NULL): is int.

  • COALESCE(NULL,NULL): выдает ошибку.

  • COALESCE(CAST(NULL as int),NULL): действителен и возвращает int.

• Определение типа данных результирующего выражения - ISNULL использует первый тип параметра, COALESCE следует правилам выражения CASE и возвращает тип значения с наивысшим приоритетом.

Ответ 3

Плюсы COALESCE

• COALESCE - это стандартная SQL-функция.

  Хотя IFNULL является специфичным для MySQL, а его эквивалент в MSSQL (ISNULL) специфичен для MSSQL.

• COALESCE может работать с двумя или более аргументами (на самом деле он может работать с одним аргументом, но в этом случае он бесполезен: COALESCE(a) ≡ a).

  В то время как MySQL IFNULL и MSSQL ISNULL ограниченные версии COALESCE, которые могут работать только с двумя аргументами.

Минусы COALESCE

• Per Документация Transact SQL, COALESCE - это просто синтаксический сахар для CASE и может его оценить аргументов больше, чем раз. Более подробно: COALESCE(a1, a2, …, aN) ≡ CASE WHEN (a1 IS NOT NULL) THEN a1 WHEN (a2 IS NOT NULL) THEN a2 ELSE aN END. Это значительно снижает полезность COALESCE в MSSQL.

  С другой стороны, ISNULL в MSSQL является нормальной функцией и никогда не оценивает его аргументы более одного раза. COALESCE в MySQL и PostgreSQL не оценивает его аргументы более одного раза.

• В этот момент я не знаю, как именно SQL-стандарты определяют COALESCE.

  Как мы видим из предыдущего пункта, фактические реализации в СУРБД различаются: некоторые (например, MSSQL) делают COALESCE для оценки его аргументов более одного раза, некоторые (например, MySQL, PostgreSQL) - не делают.

  c-treeACE, который утверждает, что реализация COALESCE совместима с SQL-92, говорит: "Эта функция не разрешена в Предложение GROUP BY. Аргументы этой функции не могут быть выражением запроса." Я не знаю, действительно ли эти ограничения соответствуют стандарту SQL; большинство фактических реализаций COALESCE (например, MySQL, PostgreSQL) не имеют таких ограничений. IFNULL/ISNULL, как обычные функции, также не имеют таких ограничений.

Резюме

Если вы не сталкиваетесь с определенными ограничениями COALESCE в определенных РСУБД, я бы рекомендовал всегда использовать COALESCE как более стандартный и более общий.

Исключения составляют:

• Длинные вычисленные выражения или выражения с побочными эффектами в MSSQL (как, например, в документации, COALESCE(expr1, …) могут дважды оценивать expr1).
• Использование в GROUP BY или с выражениями запроса в c-treeACE.
• Etc.

Ответ 4

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

ИЗМЕНИТЕ пример в соответствии с комментариями ниже.

Пример: coalesce(null, null, null, 'b*', null, 'null*') возвращает 'b *', и это невозможно сделать с ifnull.

Ответ 5

Этот db2 SQL не будет работать с COALESE, я не увижу извлеченных строк. Поскольку я использовал IFNULL, он работает как ожидалось

select a.mbitno ,a.mbstqt,ifnull(b.apr,0)
from
( 
    select mmstcd,mbstat,mbfaci,mbwhlo,mbitno,mbstqt,MBALQT from libl.mitbal  inner join libl.mitmas on 
    mmcono=mbcono and mmitno=mbitno 
    where mbcono=200 and mbstat in ('20','50') and mmstcd>0  
)  
as a left join 
(
    select mlfaci,mlwhlo,mlitno,mlstas,sum(mlstqt) as APR from libl.mitloc where mlcono=200 and mlstas='2'
    group by mlfaci,mlwhlo,mlitno,mlstas
) 
b on b.mlfaci=a.mbfaci and b.mlwhlo=a.mbwhlo and b.mlitno=a.mbitno 
where a.mbitno in 'GWF0240XPEC' and a.mbstqt>0 and a.mbstqt<>ifnull(b.apr,0)