Quelle est la différence entre ifnull et coalesce dans mysql?

select ifnull(null,'replaces the null')
-->replaces the null

select coalesce(null,null,'replaces the null')
-->replaces the null

Dans les deux clauses, la principale différence est le passage de l'argument. Pour ifnull c'est deux paramètres et coalesce 2 ou 3 Nous pouvons passer,sauf que Avons-nous une autre différence entre ces deux? Et comment cela diffère dans MSSql.

42
demandé sur Richard 2013-08-30 12:37:23

5 réponses

La principale différence entre les deux est que IFNULL fonction prend deux arguments et retourne la première, si ce n'est pas NULL, ou le second si le premier est NULL.

COALESCE la fonction peut prendre deux paramètres ou plus et renvoie le premier paramètre non nul, ou NULL si tous les paramètres sont nuls, par exemple: 

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'

UPDATE: MSSQL effectue une vérification de type et de paramètre plus stricte. En outre, il n'a pas IFNULL fonction mais plutôt ISNULL fonction, qui doit connaître les types d'arguments. Par conséquent: 

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

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

Aussi la fonction COALESCE dans MSSQL nécessite au moins un paramètre non null, donc: 

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'
60
répondu Aleks G 2018-05-16 17:11:59

Avantages de COALESCE

  • COALESCE est la fonction SQL standard .

    Alors que IFNULL est spécifique à MySQL et son équivalent dans MSSQL (ISNULL) est spécifique à MSSQL.

  • COALESCE peut travailler avec deux ou plusieurs arguments (en fait, il peut fonctionner avec un seul argument, mais il est assez inutile dans ce cas: COALESCE(a)≡a).

    Alors que les IFNULL de MySQL et les ISNULL de MSSQL sont des versions limitées de {[0] } qui peuvent fonctionner avec deux arguments seulement.

Inconvénients de COALESCE

  • Par Transact documentation SQL, COALESCE est juste un sucre de syntaxe pour CASE et peut évaluer ses arguments plus d'une fois. Plus en détail: COALESCE(a1, a2, …, aN)≡CASE WHEN (a1 IS NOT NULL) THEN a1 WHEN (a2 IS NOT NULL) THEN a2 ELSE aN END. Cela réduit considérablement l'utilité de COALESCE dans MSSQL.

    D'autre part, ISNULL dans MSSQL est une fonction normale et n'évalue jamais ses arguments plus d'une fois. {[0] } dans MySQL et PostgreSQL, ni n'évalue ses arguments plus que lorsque.

  • À ce stade, Je ne sais pas exactement comment les normes SQL définissent COALESCE.

    Comme nous le voyons au point précédent, les implémentations réelles dans le SGBDR varient: certains (par exemple MSSQL) font COALESCE pour évaluer ses arguments plus d'une fois, certains (par exemple MySQL, PostgreSQL) - ne le font pas.

    C-treeACE, qui prétend que son implémentation COALESCE est compatible SQL-92 , dit: "Cette fonction n'est pas autorisée dans une clause GROUP BY. Les Arguments de cette fonction ne peuvent pas être une requête expression."Je ne sais pas si ces restrictions sont vraiment dans SQL-standard; la plupart des implémentations réelles de COALESCE (par exemple MySQL, PostgreSQL) n'ont pas de telles restrictions. IFNULL/ISNULL, comme les fonctions normales, n'ont pas de telles restrictions, soit.

Reprendre

Sauf si vous rencontrez des restrictions spécifiques de COALESCE dans un SGBDR spécifique, je vous recommande de toujours utiliser COALESCE comme plus standard et plus générique.

Les exceptions sont:

  • expressions longues ou expressions avec des effets secondaires dans MSSQL (comme, selon la documentation, COALESCE(expr1, …) peut évaluer expr1 deux fois).
  • utilisation dans GROUP BY ou avec des expressions de requête dans c-treeACE.
  • etc.
8
répondu Sasha 2018-05-16 17:08:09

Différences dans SQL-Server:

  • Il n'y a pas de IFNULL() fonction similaire, mais un ISNULL()

  • ISNULL prend seulement 2 Paramètres alors que COALESCE prend un nombre variable de paramètres

  • COALESCE est basé sur la norme ANSI SQL alors que ISNULL est une fonction TSQL propriétaire

  • Les Validations pour ISNULL et COALESCE sont également différentes. Par exemple, NULL valeur pour ISNULL est converti en int, alors que pour COAELSCE Vous devez fournir un type. Ex:

    • ISNULL(NULL,NULL) : est de type int.

    • COALESCE(NULL,NULL) : va jeter une erreur.

    • COALESCE(CAST(NULL as int),NULL) : est valide et renvoie int.

  • Détermination du type de données de l'expression résultante - ISNULL utilise le premier type de paramètre, COALESCE suit les règles d'expression CASE et renvoie le type de valeur avec la priorité la plus élevée.

4
répondu praba 2013-08-30 09:10:43

ifnull ne peut remplacer qu'une valeur nulle du premier paramètre. Alors que {[1] } peut remplacer n'importe quelle valeur par une autre valeur. Avec coalesce dans SQL standard, vous pouvez avoir de nombreux paramètres transformant de nombreuses valeurs.

Modifiez l'exemple en fonction des commentaires ci-dessous.

Exemple: coalesce(null, null, null, 'b*', null, 'null*') renvoie 'b *' et il n'est pas possible de faire avec ifnull.

0
répondu jboi 2013-08-30 10:20:48

Ce SQL db2 ne fonctionnera pas avec COALESE, Je ne verrai aucune ligne récupérée. Depuis que J'ai utilisé IFNULL, cela fonctionne comme prévu 

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)
-1
répondu user8546027 2017-09-01 05:37:47