70-461 – notes for ch. 2 – case, coalesce, isnull, iif, choose

imagep003Simple case tests one expression against various values

Searched case could be unrelated expression

Coalesce returns the first non-null value in a list. It’s like a multi-alternative nvl and is standard SQL

Isnull is more like nvl in that it’s ‘if this is null then use that’, but it’s non-standard.

Nullif would be better called ‘nullifmatching’

iif is ‘if true then this, else that’

choose picks a specified element from a list

Simple case

This is the simplest version ((I was a bit surprised that you can have two fields called ‘nationality’…but on investigation you can do this with a simple alias too:

select nationality, 3 as nationality
from cfc_deduped
where years like ‘2000%’

…works fine
)) :

  select surname, nationality,
  case nationality
    when 'ENG' then 'Engerland'
    when 'ISL' then 'Iceland'
    end as nationality
  from cfc_deduped
  where years like '2000%'

Else is used to give a default when there’s no match

select surname, nationality,
case nationality
  when 'ENG' then 'Engerland'
  when 'ISL' then 'Iceland'
  else 'Somewhere else'
end as nationality
from cfc_deduped
where years like '2000%'

I didn’t realise that in the above example a null ‘nationality’ would fall into the ‘else’ clause.

surname nationality nationality
ALEKSIDZE   NULL    Somewhere else
BOGARDE BEL Somewhere else
COLE    ENG Engerland

Searched case

Simple form

 select surname, league_goals,
   case
   when league_goals = 0 then 'no use'
   when league_goals < 10 then 'did his bit'
   when league_goals < 100 then 'goal machine'
   end as category
 from cfc_deduped
 where years like '2000%'

There’s no reason you shouldn’t mix the column you look at in the when clause, although it wouldn’t often make much sense to do so:

 select surname, league_goals,
   case
   when league_goals = 0 then 'no use'
   when league_goals < 10 then 'did his bit'
   when nationality = 'NED' then 'dutch'
   end as category
 from cfc_deduped
 where years like '2000%'

Coalesce

Returns first non-null element:

select coalesce(null, null, 'Google', 'Vodafone', null) as 'Tax dodgers'

Tax dodgers
-----------
Google

The book says ‘A typical use of COALESCE is to substitute a NULL with something else’, so you’d typically use it as an equivalent of Oracle’s NVL.

i.e.

select surname, coalesce(nationality, '---')
 from cfc_deduped
 where years like '2000%'
 ````

<h3>isnull</h3>
Isnull is a more direct equivalent of nvl, but given coalesce is standard sql and isnull isn't, it's better to use coalesce

<h3>nullif</h3>
Would be better called 'nullifmatching', so

declare @Evil_tax_dodgers varchar(10) = ‘Google’
select nullif(‘Google’, @Evil_tax_dodgers)

...returns

null


If there's no match:

declare @Evil_tax_dodgers varchar(10) = ‘Vodafone’
select nullif(‘Google’, @Evil_tax_dodgers)


...it will return

Google
““

iif

Simpler form of case, for Access compatability.

select iif (1=1, 'Google do be evil', 'Google dont be evil')

If the first bit is true, then the second bit is returned. If not then the third bit.

choose

select choose (3, 'Google', 'Facebook', 'Vodafone', 'Apple') as tax_dodger

….returns the 3rd element, in this case ‘Vodafone’

Advertisements