Utiliser ISNULL vs utiliser COALESCE pour vérifier une condition spécifique?
je sais que plusieurs paramètres peuvent être passés COALESCE
, mais quand vous voulez pour
vérifiez juste une expression pour voir si il n'existe pas, utilisez-vous un défaut ou est-il préférable d'utiliser ISNULL
à la place?
y a-t-il un gain de performance entre les deux?
9 réponses
ce problème rapporté sur Microsoft Connect révèle quelques différences entre COALESCE
et ISNULL
:
une première partie de notre traitement réécrit
COALESCE( expression1, expression2 )
commeCASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END
. Dans [cet exemple]:COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 )
nous réalisons:
SELECT CASE WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) ELSE 1 END
les étapes ultérieures du traitement de la requête ne comprennent pas que les deux sous-séries ont été a l'origine la même expression, Donc ils exécutent le sous-jeu deux fois...
une solution, bien que je déteste la Suggérer, est de changer
COALESCE
enISNULL
, puisque ce dernier ne duplique pas la sous-commande.
Je ne pense pas, mais COALESCE est dans la norme SQL '92 et soutenu par des bases de données plus différentes. Si vous optez pour la portabilité, n'utilisez pas ISNULL.
il convient de mentionner que la manipulation de type entre les deux peut également faire une différence (voir cet élément de réponse connexe (2) ).
Dire une requête essaie d'utiliser un raccourci pour écrire null comparaison:
select * from SomeTable
where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);
qui est différent de
select * from SomeTable
where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);
parce que dans le premier cas, L'IsNull () force le type à être un peu (donc -1 est converti en true) alors que le second cas va promouvoir les deux à un int.
with input as
(
select convert(bit, 1) as BitOn,
convert(bit, 0) as BitOff,
convert(bit, null) as BitNull
)
select BitOn,
BitOff,
BitNull,
IsNull(BitOn, -1) IsNullBitOn, -- true
IsNull(BitOff, -1) IsNullBitOff, -- false
IsNull(BitNull, -1) IsNullBitNull, -- true, converts the -1 to bit
coalesce(BitOn, -1) CoalesceBitOn, -- 1
coalesce(BitOff, -1) CoalesceBitOff, -- 0
coalesce(BitNull, -1) CoalesceBitNull -- -1
from input;
il y a un commentaire/lien similaire (@Martin Smith) sur la question elle-même.
une chose importante que je ne vois pas explicitement indiqué est que le type de sortie de ISNULL
est similaire à la première expression, mais avec COALESCE
il renvoie le type de données de la valeur de la plus haute priorité.
DECLARE @X VARCHAR(3) = NULL
DECLARE @Y VARCHAR(10) = '123456789'
/* The datatype returned is similar to X, or the first expression*/
SELECT ISNULL(@X, @Y) ---> Output is '123'
/* The datatype returned is similar to Y, or to the value of highest precedence*/
SELECT COALESCE(@X, @Y) ---> Output is '123456789'
Lorsqu'il n'y a qu'une seule condition nulle, ISNULL
aura moins de frais généraux. La différence est probablement négligeable.
cette explication est claire sur coalesce vs isnull
la fonction COALESCE dans SQL renvoie la première expression non-nulle parmi ses arguments. La syntaxe de COALESCE est la suivante:
COALESCE ("expression 1", "expressions 2", ...)
il s'agit de la même affaire que l'énoncé suivant:
SELECT CASE ("column_name")
WHEN "expression 1 is not NULL" THEN "expression 1"
WHEN "expression 2 is not NULL" THEN "expression 2"
...
[ELSE "NULL"]
END
FROM "table_name";
dans SQL Server, la fonction ISNULL( ) est utilisée pour remplacer la valeur nulle par une autre valeur.
select CountryName = ISNULL("columnname", 'INDIA') from Countries
Coalesce retourner la première expression non null où as isnull () est utilisé pour remplacer la valeur null avec notre valeur désirée.
COALESCE fait partie des normes de L'ANSI et sont disponibles dans presque toutes les bases de données.
lors de la décision entre ISNULL v COALESCE il faut prendre soin des paramètres:
- COALESCE détermine le type de sortie sur la base du type de données priorité lorsque, comme avec ISNULL, le type de données n'est pas influencé par les données type de priorité.
-
Examiner la possibilité de suivre les instructions sql
DECLARE @c5 VARCHAR(5); SELECT 'COALESCE', COALESCE(@c5, 'longer name') UNION ALL SELECT 'ISNULL', ISNULL(@c5, 'longer name');
Résultats:
COALESCE longer name
ISNULL longe
cela se produit parce que ISNULL prend le type de données du premier argument, tandis que COALESCE inspecte tous les éléments et choisit le meilleur ajustement (dans ce cas, VARCHAR (11))
pour une explication plus détaillée sur la décision entre COALESCE vs ISNULL, cochez cette case. : https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server /
les NULL
et COALESCE
ne sont pas toujours interchangeables. Il mérite de connaître leurs différences afin de savoir quand il est mieux d'utiliser l'un sur l'autre:
le tableau ci-dessus est une comparaison entre ISNULL
et COALESCE
du livre Exam Ref 70-761 Querying Data with Transact-SQL
écrit par Itzik Ben-Gan.
- nombre des paramètres pris en charge -
2
pourISNULL
vs>2
en utilisantCOALESCE
-
ISNULL
est la propriété exclusive de T-SQL fonctionnalité etCOALESCE
est la norme ISO/ANSI standard SQL -
le type de données du résultat est important. Après avoir lu les notes dans le tableau ci-dessus, cochez les cases suivantes:
DECLARE @x VARCHAR(3) = NULL ,@y VARCHAR(10) = '1234567890'; SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
Le
ISNULL
obtient le type de données du premier argument car il est le notNULL
littéral. Il estVARCHAR(3)
et est un résultat, le deuxième argument données est coupé pour le match. AvecCOALESCE
le type de données si la priorité la plus élevée est utiliser.DECLARE @x VARCHAR(8) = '123x5' ,@y INT = 123; SELECT ISNULL(@x, @y) AS [ISNULL]; SELECT COALESCE(@x, @y) AS [COALESCE];
le
ISNULL
est retourner le type de données du premier argument, alors que dansCOALESCE
nous obtenons erreur, comme leINT
a la plus haute priorité et le la conversion de la valeur du premier argument enINT
échoue. -
la nullité du résultat peut aussi être importante. Exemple:
DECLARE @x VARCHAR(3) = NULL ,@y VARCHAR(3) = NULL; DROP TABLE IF EXISTS [dbo].[DataSource01]; SELECT ISNULL(10, 20) AS [C1] ,ISNULL(@x, 'text') AS [C2] ,ISNULL(@x, @y) AS [C3] INTO [dbo].[DataSource01]; DROP TABLE IF EXISTS [dbo].[DataSource02]; SELECT COALESCE(10, 20) AS [C1] ,COALESCE(@x, 'text') AS [C2] ,COALESCE(@x, @y) AS [C3] INTO [dbo].[DataSource02];
vérifions la propriété
Nullable
de chaque colonne:utilisant
COALESCE
nous avons uneNOT NULL
propriété de la colonne fixée àYes
, seulement lorsque toutes les entrées sont non nulles. -
selon la norme SQL, l'expression
COALESCE
se traduit par:CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
si le résultat exécution du sous-jeu dans la clause quand n'est pas NULL, SQL Server l'exécute une seconde fois dans la clause THEN. En d'autres termes, dans un tel cas, il exécute deux fois. Seulement si le résultat de l'exécution dans la clause quand est NULL, SQL Server ne exécuter la sous-requête de nouveau, plutôt renvoie le RESTE de l'expression. Si lors de l'utilisation de sous-séries, la fonction ISNULL a une performance avantage.
dans COALESCE on peut utiliser des expressions multiples, il retournera une valeur qui n'est pas nulle et se produit en premier... par exemple
DECLARE @Value1 INT, @Value2 INT, @Value3 INT, @Value4 INT
SELECT @Value2 = 2, @Value4 = 4
SELECT COALESCE(@Value1, @Value2, @Value3, @Value4)
SELECT COALESCE(@Value1, @Value4, @Value3, @Value2)
et dans ISNULL si l'expression null retournera le second paramètre fourni, et bien sûr vous pouvez vérifier seulement pour une expression...
donc si vous voulez cochez l'expression multiple et sélectionnez d'abord not null parmi eux, puis utilisez coalesce sinon aller pour ISNULL