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?

65
demandé sur Cœur 2011-09-14 01:40:21

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 ) comme CASE 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 en ISNULL , puisque ce dernier ne duplique pas la sous-commande.

51
répondu onedaywhen 2017-12-16 12:54:52

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.

22
répondu GolezTrol 2015-10-23 13:51:28

Dans FUSIONNER vous pouvez avoir plusieurs expressions, où, comme dans le ISNULL , vous pouvez cocher une seule expression

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )
10
répondu otti 2014-03-13 16:21:17

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.

4
répondu crokusek 2017-05-23 11:54:38

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'
4
répondu BICube 2017-09-01 16:33:55

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.

2
répondu James Johnson 2011-09-13 21:53:51

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:

  1. 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é.
  2. 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 /

2
répondu Nayas Subramanian 2017-05-27 14:19:09

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:

enter image description here

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.


  1. nombre des paramètres pris en charge - 2 pour ISNULL vs >2 en utilisant COALESCE
  2. ISNULL est la propriété exclusive de T-SQL fonctionnalité et COALESCE est la norme ISO/ANSI standard SQL
  3. 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];
    

    enter image description here

    Le ISNULL obtient le type de données du premier argument car il est le not NULL littéral. Il est VARCHAR(3) et est un résultat, le deuxième argument données est coupé pour le match. Avec COALESCE 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];
    

    enter image description here

    enter image description here

    le ISNULL est retourner le type de données du premier argument, alors que dans COALESCE nous obtenons erreur, comme le INT a la plus haute priorité et le la conversion de la valeur du premier argument en INT échoue.

  4. 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:

    enter image description here

    enter image description here

    utilisant COALESCE nous avons une NOT NULL propriété de la colonne fixée à Yes , seulement lorsque toutes les entrées sont non nulles.

  5. 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.

1
répondu gotqn 2017-10-05 06:59:10

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

-2
répondu Ranadeera Kantirava 2014-04-16 12:05:45