COALESCE Function in TSQL

Question

Can someone explain how the COALESCE function in TSQL works   The syntax is as follows     COALESCE x  y    The MSDN document on this function is pretty vague

User · Answer

Here is a simple query containing coalesce -

select * from person where coalesce(addressId, ContactId) is null.

It will return the persons where both addressId and contactId are null.

coalesce function

takes least two arguments.
arguments must be of integer type.
return the first non-null argument.

e.g.

coalesce(null, 1, 2, 3) will return 1.
coalesce(null, null) will return null.

User · Answer

I m not sure why you think the documentation is vague   It simply goes through all the parameters one by one  and returns the first that is NOT NULL   COALESCE NULL  NULL  NULL  1  2  3    gt  1   COALESCE 1  2  3  4  5  NULL    gt  1   COALESCE NULL  NULL  NULL  3  2  NULL    gt  3   COALESCE 6  5  4  3  2  NULL    gt  6   COALESCE NULL  NULL  NULL  NULL  NULL  NULL    gt  NULL   It accepts pretty much any number of parameters  but they should be the same data-type     If they re not the same data-type  they get implicitly cast to an appropriate data-type using data-type order of precedence    It s like ISNULL   but for multiple parameters  rather than just two   It s also ANSI-SQL  where-as ISNULL   isn t

User · Answer

I ve been told that COALESCE is less costly than ISNULL  but research doesn t indicate that   ISNULL takes only two parameters  the field being evaluated for NULL  and the result you want if it is evaluated as NULL   COALESCE will take any number of parameters  and return the first value encountered that isn t NULL     There s a much more thorough description of the details here http   www mssqltips com sqlservertip 2689 deciding-between-coalesce-and-isnull-in-sql-server

User · Answer

declare  store table  store id varchar 300   insert into  store  values   aa     bb     cc   declare  str varchar  4000  select  str   concat  str     store id  from  store select  str

User · Answer

Simplest definition of the Coalesce   function could be      Coalesce   function evaluates all passed arguments then returns the value of the first instance of the argument that did not evaluate to a NULL    Note  it evaluates ALL parameters  i e  does not skip evaluation of the argument s  on the right side of the returned NOT NULL parameter   Syntax   Coalesce arg1  arg2  argN       Beware   Apart from the arguments that evaluate to NULL  all other  NOT-NULL  arguments must either be of same datatype or must be of matching-types  that can be  implicitly auto-converted  into a compatible datatype   see examples below   PRINT COALESCE NULL    str-   1     x    --returns  str-1  works as all args  excluding NULLs  are of same VARCHAR type  --PRINT COALESCE NULL   text    3   3     --ERROR  passed args are NOT matching type   can t be implicitly converted  PRINT COALESCE NULL  3  7 0 2  1 99       --returns 3 0  works fine as implicit conversion into FLOAT type takes place  PRINT COALESCE NULL   1995-01-31    str   --returns  2018-11-16   works fine as implicit conversion into VARCHAR occurs   DECLARE  dt DATE   getdate   PRINT COALESCE NULL   dt   1995-01-31    --returns today s date  works fine as implicit conversion into DATE type occurs   --DATE comes before VARCHAR  works   PRINT COALESCE NULL   dt   str        --returns  2018-11-16   works fine as implicit conversion of Date into VARCHAR occurs   --VARCHAR comes before DATE  does NOT work   PRINT COALESCE NULL   str    dt       --ERROR  passed args are NOT matching type  can t auto-cast  str  into Date type    HTH

User · Answer

Here is the way I look at COALESCE   and hopefully it makes sense     In a simplistic form      Coalesce FieldName   Empty    So this translates to   If  FieldName  is NULL  populate the field value with the word  EMPTY    Now for mutliple values     Coalesce FieldName1  FieldName2  Value2  Value3   If the value in Fieldname1 is null  fill it with the value in Fieldname2  if FieldName2 is NULL  fill it with Value2  etc   This piece of test code for the AdventureWorks2012 sample database works perfectly  amp  gives a good visual explanation of how COALESCE works   SELECT Name  Class  Color  ProductNumber  COALESCE Class  Color  ProductNumber  AS FirstNotNull FROM Production Product

User · Answer

There is a lot more to coalesce than just a replacement for ISNULL   I completely agree that the official  documentation  of coalesce is vague and unhelpful  This article helps a lot   http   www mssqltips com sqlservertip 1521 the-many-uses-of-coalesce-in-sql-server

[sql] COALESCE Function in TSQL

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql