Discussion :

[xev47]

Bonjour,

Je souhaite tester si COLONNE1 et COLONNE2 sont vides ou contiennent un espace ' '

J'ai testé cette requête mais j'ai une erreur.
C'est apparemment le IS NULL qui pose problème car si je le remplace par 'abc' ça fonctionne.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
UPDATE MA_TABLE_TEST SET COLONNE1 =
( CASE COLONNE2
WHEN 'VAR1' THEN 
CASE WHEN COLONNE3 in (IS NULL,' ') THEN 'OK1'
ELSE 'OK2'
END
WHEN 'VAR2' THEN
CASE WHEN COLONNE3 in (IS NULL,' ') THEN 'OK3'
ELSE 'OK4'
END
END
)

Pourriez-vous m'aider svp ?

Merci

[Invité]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CASE WHEN COLONNE3 IS NULL OR COLONNE3 = ' ' THEN 'OK1'

[xev47]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CASE WHEN COLONNE3 IS NULL OR COLONNE3 = ' ' THEN 'OK1'

Merci !
Ça fonctionne maintenant

[StringBuilder]

Tu peux aussi utiliser cette syntaxe :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
CASE WHEN coalesce(COLONNE3, ' ') = ' ' THEN 'OK1'

L'avantage c'est que tu peux indexer coalesce(COLONNE3, ' ') et du coup la condition devient sargable.

[olibara]

Dans le cas present ISNULL suffit et est sans doute meme plus performant :-)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CASE WHEN ISNULL(COLONNE3, ' ') = ' ' THEN 'OK1'

Comme ça tu as deux possibilités
L'avantage que peut avoir le COALESCE c'est qu'il permet les parametres en cascade

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

COALESCE(COLONNE3,COLONNE2,COLONNE1,' ')=' '

Le premier non NULL à gagné :-)

[Invité]

Dans le cas present ISNULL suffit et est sans doute meme plus performant :-)

Faudrait argumenter un peu plus, surtout pour la performance, parce que pour moi, c'est blanc bonnet et bonnet blanc.

[StringBuilder]

Faudrait argumenter un peu plus, surtout pour la performance, parce que pour moi, c'est blanc bonnet et bonnet blanc.

"On" m'a déjà dit (SQLPro faisait partie du "on") la même chose.

Le ISNULL(), spécifique à SQL Server, serait plus optimisé et parfois changerait même complètement le plan d'exécution.
J'avoue ne pas comprendre non plus comment SQL Server peut ne pas considérer COALESCE(arg1, arg2) comme un simple alias de ISNULL(arg1, arg2).

Je suppose, mais sans en être complètement certain, qu'il existe un ou plusieurs cas où les deux fonctions ne font pas la même chose :
- mixité des types
- résultat forcément non null
- autre ?

L'exemple ci-dessous (avec SQL Server 2016 Express) montre effectivement une différence de comportement avec les arguments NULL :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
select isnull(1, 2);
select isnull(null, null);
select isnull(1, 'a');
 
select coalesce(1, 2);
select coalesce(null, null);
select coalesce(1, 'a');

Msg 4127, Level 16, State 1, Line 6
Au moins un des arguments de COALESCE doit être une expression qui n'est pas la constante NULL.

Le ISNULL(null, null) ne pose pas de problème pourtant. Visiblement, COALESCE() a un type de retour "not null".

Pourtant, l'exemple suivant passe sans problème :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
declare @a int;
declare @b int;
set @a = null;
set @b = null;
select coalesce(@a, @b);

Donc il faut au moins un argument littéral non null, mais si c'est une variable (ou une colonne, j'imagine) ça passe sans problème avec que des null...
Très étrange...

Ah, ou alors j'ai compris ! C'est une histoire de type !

Il faut au moins une variable typée (donc différence de la valeur littérale "null" qui n'est pas typée) pour que coalesce sache quel type retourner.
Pourtant, passer des arguments de type différent ne le fait pas sauter...
Étrange...

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
declare @a int;
declare @b varchar;
set @a = 1;
set @b = 'a';
select coalesce(@a, @b);
select isnull(@a, @b);

Ça marche

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
declare @a int;
declare @b varchar;
set @a = 1;
set @b = null;
select coalesce(@a, @b);
select isnull(@a, @b);

Ca marche aussi

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
declare @a int;
declare @b varchar;
set @a = null;
set @b = 'a';
select coalesce(@a, @b);
select isnull(@a, @b);

Ca plante pour isnull aussi bien que pour coalesce...

J'avoue ne pas comprendre la logique profonde du truc...

[Invité]

Merci bien de l'explication !
Il faudrait que MS fasse un gros ménage dans les fonctions SQL Server, il y a tellement de vielles fonctions bancales toujours en activité.
C'est là qu'Oracle a encore une longueur d'avance avec des librairies bien plus solides et plus fournies mais pas non plus complètement au dessus de tout soupçon - j'ai déjà débuggé et corrigé une de leur fonction...

[SQLpro]

Tu rigoles ou quoi ? C'est farci de fonctions redondantes propriétaires imbitables à commencer par les TO_machin..... !

A +

[aieeeuuuuu]

bonjour,

Le ISNULL(), spécifique à SQL Server, serait plus optimisé et parfois changerait même complètement le plan d'exécution.

C'est en effet le cas.

J'avoue ne pas comprendre non plus comment SQL Server peut ne pas considérer COALESCE(arg1, arg2) comme un simple alias de ISNULL(arg1, arg2).

Justement parce que les deux fonctions ne sont pas tout à fait identiques même si elles adressent le même besoin.

Je suppose, mais sans en être complètement certain, qu'il existe un ou plusieurs cas où les deux fonctions ne font pas la même chose :
- mixité des types
- résultat forcément non null
- autre ?

Le type de retour n'est en effet pas le même.
- ISNULL renvoie le type du premier argument
- COALESCE renvoie le type de l'argument ayant la plus forte précédence :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
DECLARE @a VARCHAR(3)
DECLARE @b VARCHAR(4) = 'toto'
 
SELECT COALESCE(@a, @b) AS r_coalesce, ISNULL(@a, @b) as r_isnull

==>

r_coalesce	r_isnull
toto	        tot

Il existe également un différence de comportement notable :
Avec un COALESCE, les expressions sont interprétées une fois pour tester la nullité, et une deuxième fois pour renvoyer la valeur non NULL, ce qui n'est pas vraiment génant quand on teste une simple colonne, mais peut le devenir quand on fait appel a une sous requête, ou une fonction couteuse :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
CREATE FUNCTION UDF_TEST ()
RETURNS INT
AS
BEGIN
DECLARE @x int = 0;
	WHILE (@x < 1000000) SET @x += 1;
	RETURN 0;
 
END

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT ISNULL(dbo.udf_test(), 0)

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 452*ms, temps écoulé = 587*ms.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COALESCE(dbo.udf_test(), 0)

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 889*ms, temps écoulé = 1239*ms.

Cela peut même donner des résultats inattendus :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE FUNCTION UDF_TEST (@i VARCHAR(50))
RETURNS INT
AS
BEGIN
 
	RETURN CASE WHEN @i like '[0-8]%' THEN NULL ELSE 1 END;
 
END

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COALESCE(dbo.udf_test(NEWID()), 0)

peut parfois renvoyer :

NULL

Alors qu'un ISNULL renverra toujours 0 ou 1;

[StringBuilder]

Merci beaucoup pour cette explication parfaitement limpide

Reste à comprendre pourquoi COALESCE est aussi mal implémenté (car autant pour le type de retour, je comprends la différence, entre l'implémentation historique non standard, et l'implémentation normée, autant le coup de la double évaluation dénote une grave anomalie d'optimisation !)

Et surtout, comme indiqué avec l'appel à une fonction non déterministe, le résultat de COALESCE est tout simplement :
- faux (puisque ça ne ramène pas la valeur initiale, mais la valeur calculée une seconde fois)
- illogique (puisque ça peut ramener NULL alors que c'est justement fait pour l'éviter !)

Mon intégrisme du "tout normé" en prend un coup Je crois que les ISNULL() vont fleurir un peu partout dans mon code

[aieeeuuuuu]

non, en fait ce n'est ni faux, ni illogique. ça correspond juste à une certaine vérité avec une certaine logique

COALESCE n'est ni plus ni moins qu'une forme abrégée de CASE (au même titre que NULLIF) :
COALESCE(A,B,C,...) est un équivalent de CASE WHEN A IS NOT NULL THEN A ELSE COALESCE(b,c,...) END.

dés lors, le comportement devient en effet logique, au même titre que SELECT NEWID(), NEWID() renvoie deux valeurs différentes.

D'ailleurs, on n'obtiendra plus de NULL si l'on écrit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT COALESCE(val, 0)
FROM (SELECT dbo.udf_test(NEWID()) AS val) T

Dans ce cas en effet, la fonction ne sera appelée qu'une fois pour la table dérivée.

[aieeeuuuuu]

Donc il faut au moins un argument littéral non null, mais si c'est une variable (ou une colonne, j'imagine) ça passe sans problème avec que des null...
Très étrange...

Ah, ou alors j'ai compris ! C'est une histoire de type !

Non, là aussi c'est une histoire de norme.
Comme COALESCE est en fait un CASE, il n'est pas admis de ne spécifier que des NULL comme résultat. COALESCE(NULL,NULL) est donc rejeté au même titre que :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT 
	CASE 
		WHEN 1 = 2 THEN NULL
		WHEN 1 = 1 THEN NULL
	END