Did you know there was an IIF function in SQL to test conditions? We didn't!
You can use the IIF function in SQL as an alternative to the CASE WHEN statement. We thought we would share this new (for us) discovery with the world!

Posted by Andy Brown on 16 February 2018

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Our latest OMG moment - the IIF function in SQL

So how long have we been teaching SQL for now (at introductory and advanced level)?  Ten years?  More?  And yet none of us apart from Sam knew that you could use the IIF function when writing SQL. 

Thanks to Adrian Dilworth for asking the relevant question on a recent SQL course.  What would we do without our course delegates?

Without the IIF function  - using CASE WHEN

So supposing you want to report on whether films won Oscars or not.  Here's how you could do this if you were so lamentably ignorant as not to know about the IIF function:

SELECT

f.Title,

f.OscarWins,

f.OscarNominations,

-- was this person a winner

CASE

WHEN f.OscarWins > 0 THEN 'Winner'

ELSE 'Loser'

END AS OscarStatus

FROM

Film AS f

And here are the first few rows that this might show:

Films with Oscar status

The first few films, with their all-important Oscar status.

The same thing again, using IIF

You could more easily write the same query using the IIF function:

SELECT

f.Title,

f.OscarWins,

f.OscarNominations,

-- was this person a winner (using IIF)

IIF(

f.OscarWins > 0,

'Winner',

'Loser'

) AS OscarStatus

FROM

Film AS f

The syntax of this function is identical to that of the IF function in Excel.  The 3 arguments are: 1) the condition to test, 2) what to do if it's true and 3) what to do if it's false.

Nesting IIF functions

As in Excel, you can nest IIF functions within each other to test multiple, mutually exclusive possibilities.  For example, the following query would show for each film whether it was an Oscar winner, nominee or loser:

SELECT

f.Title,

f.OscarWins,

f.OscarNominations,

-- was this person a winner, nominee or loser

IIF(f.OscarWins > 0,'Winner',

IIF(f.OscarNominations > 0, 'Nominee','Loser')

) AS OscarStatus

FROM

Film AS f

Once you're testing more than two conditions, you're probably better off writing a CASE WHEN statement instead (or in Excel 2016 or later, the SWITCH function).

This blog has 0 threads Add post