This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

In this blog

Without the IIF function - using CASE WHEN
The same thing again, using IIF
Nesting IIF functions

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:

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).

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Our latest OMG moment - the IIF function in SQL

Without the IIF function - using CASE WHEN

The same thing again, using IIF

Nesting IIF functions

Head office

London

Manchester