webentwicklung-frage-antwort-db.com.de

Verwenden Sie ISNULL oder COALESCE, um eine bestimmte Bedingung zu überprüfen?

Ich weiß, dass mehrere Parameter an COALESCE übergeben werden können, aber wenn Sie nur einen Ausdruck überprüfen möchten, um festzustellen, ob er nicht vorhanden ist, verwenden Sie einen Standardwert, oder ist es eine bessere Methode, ISNULL stattdessen?

Gibt es einen Leistungsgewinn zwischen den beiden?

69
JBone

Dieses Problem wurde in Microsoft Connect gemeldet zeigt einige Unterschiede zwischen COALESCE und ISNULL:

ein früher Teil unserer Verarbeitung schreibt COALESCE( expression1, expression2 ) als CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [diesem Beispiel]:

COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )

wir erzeugen:

SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END

Spätere Phasen der Abfrageverarbeitung verstehen nicht, dass die beiden Unterabfragen ursprünglich derselbe Ausdruck waren. Sie führen die Unterabfrage also zweimal aus.

Eine Problemumgehung ist, obwohl ich es ungern vorschlage, COALESCE in ISNULL zu ändern, da letztere die Unterabfrage nicht dupliziert.

55
onedaywhen

Ich denke nicht, aber COALESCE ist im SQL '92-Standard und wird von mehr verschiedenen Datenbanken unterstützt. Wenn Sie sich für Portabilität entscheiden, verwenden Sie nicht ISNULL.

24
GolezTrol

In COALESCE können Sie mehrere Ausdrücke haben, wobei Sie wie in ISNULL nur überprüfen können ein Ausdruck

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )
11
otti

Erwähnenswert ist, dass die Typbehandlung zwischen den beiden auch einen Unterschied machen kann (siehe dieser verwandte Antwortpunkt (2) ).

Angenommen, eine Abfrage versucht, eine Verknüpfung zum Schreiben eines Nullvergleichs zu verwenden:

select * from SomeTable
 where IsNull(SomeNullableBitField, -1) != IsNull(SomeOtherNullableBitField, -1);

das ist anders als

select * from SomeTable
 where coalesce(SomeNullableBitField, -1) != coalesce(SomeOtherNullableBitField, -1);

Im ersten Fall erzwingt IsNull (), dass der Typ ein Bit ist (also wird -1 in true konvertiert), während im zweiten Fall beide Werte in ein int umgewandelt werden.

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;

Zu der Frage selbst gibt es einen ähnlichen Kommentar/Link (@Martin Smith).

7
crokusek

Eine wichtige Sache, die ich nicht explizit sehe, ist, dass der Ausgabetyp von ISNULL dem ersten Ausdruck ähnlich ist, aber mit COALESCE den Datentyp des Werts mit der höchsten Priorität zurückgibt.

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'
5
BICube

Diese Erklärung gibt Aufschluss über das Zusammenwachsen mit dem Nichts

Die COALESCE-Funktion in SQL gibt den ersten Nicht-NULL-Ausdruck unter den Argumenten zurück. Die Syntax für COALESCE lautet wie folgt:

 COALESCE ("expression 1", "expressions 2", ...)

Es ist dasselbe wie die folgende CASE-Anweisung:

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";

In SQL Server wird die Funktion ISNULL () verwendet, um den Wert NULL durch einen anderen Wert zu ersetzen.

select CountryName = ISNULL("columnname", 'INDIA') from Countries

Coalesce gibt den ersten Nicht-Null-Ausdruck zurück, wobei as isnull () verwendet wird, um den Nullwert durch unseren gewünschten Wert zu ersetzen.

COALESCE ist Teil der ANSI-Standards und in fast allen Datenbanken verfügbar.

bei der Entscheidung zwischen ISNULL und COALESCE sind folgende Parameter zu beachten:

  1. COALESCE bestimmt den Typ der Ausgabe basierend auf der Datentyppriorität, wobei der Datentyp wie bei ISNULL nicht von der Datentyppriorität beeinflusst wird.
  2. Betrachten Sie die folgenden SQL-Anweisungen

    DECLARE @c5 VARCHAR(5);
    SELECT 'COALESCE', COALESCE(@c5, 'longer name')
    UNION ALL
    SELECT 'ISNULL',   ISNULL(@c5,   'longer name');
    

Ergebnisse:

COALESCE longer name
ISNULL   longe

Dies liegt daran, dass ISNULL den Datentyp des ersten Arguments verwendet, während COALESCE alle Elemente überprüft und die beste Anpassung auswählt (in diesem Fall VARCHAR (11)).

Ausführlichere Erklärungen zur Entscheidung zwischen COALESCE und ISNULL finden Sie hier: https://www.mssqltips.com/sqlservertip/2689/deciding-between-coalesce-and-isnull-in-sql-server/

2

Wenn es nur eine Nullbedingung gibt, hat ISNULL weniger Overhead. Der Unterschied ist jedoch wahrscheinlich vernachlässigbar.

2
James Johnson

Die NULL und COALESCE sind nicht immer austauschbar. Es verdient, ihre Unterschiede zu kennen, um zu wissen, wann es besser ist, den einen über den anderen zu verwenden:

enter image description here

Die obige Tabelle ist ein Vergleich zwischen ISNULL und COALESCE aus dem Exam Ref 70-761 Querying Data with Transact-SQL - Buch von Itzik Ben-Gan.


  1. Anzahl unterstützter Parameter - 2 Für ISNULL vs >2 Bei Verwendung von COALESCE
  2. ISNULL ist eine proprietäre T-SQL-Funktion und COALESCE ist ein ISO/ANSI-SQL-Standard
  3. Der Datentyp des Ergebnisses ist wichtig. Überprüfen Sie die folgenden Fälle, nachdem Sie die Hinweise in der obigen Tabelle gelesen haben:

    DECLARE @x VARCHAR(3)  = NULL
           ,@y VARCHAR(10) = '1234567890';
    
    SELECT ISNULL(@x, @y) AS [ISNULL], COALESCE(@x, @y) AS [COALESCE];
    

    enter image description here

    Das ISNULL ruft den Datentyp des ersten Arguments ab, da es nicht das NULL -Literal ist. Es ist VARCHAR(3) und als Ergebnis werden die Daten des zweiten Arguments entsprechend zugeschnitten. Mit COALESCE den Datentyp, wenn die höchste Priorität verwendet wird.

    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

    Das ISNULL gibt den Datentyp des ersten Arguments zurück, während in COALESCE ein Fehler auftritt, da das INT die höchste Priorität hat und die Konvertierung des ersten Argumentwerts in INT schlägt fehl.

  4. Die Nichtigkeit des Ergebnisses kann ebenfalls wichtig sein. Beispielsweise:

    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];
    

    Lassen Sie uns die Nullable -Eigenschaft jeder Spalte überprüfen:

    enter image description here

    enter image description here

    Mit COALESCE haben wir eine NOT NULL - Eigenschaft der Spalte auf Yes gesetzt, nur wenn alle Eingaben nicht nullfähig sind.

  5. Gemäß dem SQL-Standard wird der Ausdruck COALESCE übersetzt in:

    CASE WHEN (<subquery>) IS NOT NULL THEN (<subquery>) ELSE 0 END
    

    Wenn das Ergebnis der Ausführung der Unterabfrage in der WHEN-Klausel nicht NULL ist, führt SQL Server es in der THEN-Klausel ein zweites Mal aus. In einem solchen Fall wird es also zweimal ausgeführt. Nur wenn das Ergebnis der Ausführung in der WHEN-Klausel NULL ist, führt SQL Server die Unterabfrage nicht erneut aus, sondern gibt den ELSE-Ausdruck zurück. Wenn Sie also Unterabfragen verwenden, hat die ISNULL-Funktion einen Leistungsvorteil.

1
gotqn