How to use IF...THEN logic in SQL server
Posted by: AJ Welch
SQL Server has a unique capability of allowing you to execute real-time programmatic logic on the values within your query. Based on those logical evaluations, you can generate various values as part of the returned data set.
Using the CASE statement
This is most easily accomplished in all versions of SQL Server using the
CASE
statement, which acts as a logical
IF...THEN...ELSE
expression and returns various values depending on the result.
In this example below, we want to return an additional
locale
column that specifies whether our book takes place in Middle-earth or regular old Earth.
SELECT
CASE
WHEN
books.title = 'The Hobbit'
THEN
'Middle-earth'
WHEN
books.primary_author = 'Tolkien'
THEN
'Middle-earth'
ELSE
'Earth'
END AS locale,
books.*
FROM
books
Before we examine the special
CASE
aspect of this statement, let’s temporarily remove the
CASE
to notice that this is an extremely simple
SELECT
statement on the surface:
SELECT
books.*
FROM
books
Therefore, let’s examine how the
CASE
section is structured and what logical behavior we’re performing.
CASE
WHEN
books.title = 'The Hobbit'
THEN
'Middle-earth'
WHEN
books.primary_author = 'Tolkien'
THEN
'Middle-earth'
ELSE
'Earth'
END AS locale
To begin, we of initialize the
CASE
statement then specify under which conditions (
WHEN
) our
CASE
statement should evaluate a result. In this example, we’re examining the
books.title
and
books.primary_author
; if either fit our Tolkien-esque theme,
THEN
we return the value ‘Middle-earth.’ If neither fields match our search, we instead return the value of ‘Earth.’
To rearrange the logic as a psuedo-code
IF...THEN...ELSE
statement, we’re simply asking SQL to evaluate:
IF
title == 'The Hobbit' OR
primary_author == 'Tolkien'
THEN
RETURN 'Middle-earth'
ELSE
RETURN 'Earth'
END
Finally, it is critical to remember that a
CASE
statement must always be appended at the end with a matching
END
statement. In the above example, we’re also renaming the resulting value that is returned to
locale
, though that is certainly optional.
Using the IIF function
If you are using a more modern version of SQL, it is useful to know that SQL Server 2012 introduced the very handy
IIF
function.
IIF
is a shorthand method for performing an
IF...ELSE/CASE
statement and returning one of two values, depending on the evaluation of the result.
Restructuring our above example to use
IIF
is quite simple.
SELECT
IIF(
books.title = 'The Hobbit' OR books.primary_author = 'Tolkien',
'Middle-earth',
'Earth')
AS locale,
books.*
FROM
books
With an
IIF
function, we largely replace a lot of the syntactical sugar from the
CASE
statement with a few simple comma-seperators to differentiate our arguments.
All told, both
CASE
and
IIF
get the same job done, but if given the choice,
IIF
will generally be much simpler to use.