COALESCE and IIF functions, the CASE statement and a secret at the end of the post

Hi Friends,

March has arrived, spring is coming and i am writing this post after a sunny thuesday. Not bad at all! Just a good coffee and it is time for our basic post of today!

We will talk about the COALESCE and IIF functions and the CASE statement. 

Maybe someone has never used them or ...doesn't know the secret at the end of the post!

Hey don't cheat, don't scroll down! please!

COALESCE

The function coalesce return the first not null parameter from the left

For example:

 
SELECT COALESCE(NULL,1,3)       return 1
SELECT COALESCE(‘A’,’B’,’C’)    return ‘A’

 

I like this function because it accepts a variable number of parameters as input. Few functions do this..

In all honesty I would like other functions that behave this way, for example the MAX and MIN...

COALESCE, IIF or CASE?

There are other ways to get the same result as the following query:

 
Select coalesce(g.qttgiai,g.qttimpi,g.qttmani) from products g

 

I can use for example an IIF function

Syntax: IIF(condition = true, <then>, <else>) if the condition is verified it return the <then> part else then <else> part.
 

Infact you can simply though at the coalesce function as a special case of a IIF function where the condition is “my columns” is not null.

 
Select 

  iif(g.qttgiai is not null, g.qttgiai, iif(g.qttimpi is not null, g.qttmani, 0)) 

from products g

Or you can use a CASE statement that is equivalent to an IIF function.

 
Select 

   (case when g.qttgiai is not null then g.qttgiai else 

        (case when g.qttimpi is not null then g.qttimpi else g.qttmani end) 

   end) 

from products g

 

 

Now take a look at the execution plan of the three queries 

Hey what the heck! ... all three queries have the same execution plan!

 

 

And even more!

 

Take a look to property of the “Calcolo scalare”

What do you note? Each COALESCE or IIF function is translated into a CASE. BIN-GO!

 

CONCLUSION

We can use the (convenient) coalesce function, or the (c like) IIF function as for SQL Server they will all be CASE statements. 

Have you enjoyed the secret?

That's all for today, hope you enjoy the post. 

Ask Luke for any question maybe we can learn somethings alltogheter!

Luca

Previous post: Speed up your Inserts and Updates ...after learning how the Write Ahead Logging mechanism works