Conditions in SQL - IF / ELSE
All you programmers out there who have been following this tutorial are
probably salivating now at the prospect of getting stuck into some proper
programming. Salivate not, because ...
IF conditions are not that commonly used in SQL; if you find
yourself littering your SQL with conditions, you're probably doing something
wrong. You could do worse than to book a place on one of our introductory
or advanced SQL courses, which we now offer online
also.
Notwithstanding the hint above, there is a place for IF
statements in SQL. Let's first have a look at the syntax, then consider
some examples.
Syntax of a simple IF statement
The way you write an IF statement in SQL is as follows:
IF (condition true)
DO SOMETHING
To make it clearer what's going on (and to allow you to run a number of
different statements if a condition is true), use BEGIN and
END as bookmarks:
IF (condition is true)
BEGIN
DO ONE THING
DO ANOTHER THING
END
My advice would be always to use BEGIN
/ END, especially if you come
from a VB background. Oh, and resist that temptation to put the word
THEN after the condition!
Multiple IF conditions using ELSE
Suppose you want to say: if one thing is true, do this; otherwise, do that.
For this, you'll need to use the ELSE keyword. The syntax
is:
IF (condition is true)
BEGIN
DO THING A
DO THING B
END
ELSE
BEGIN
DO THING C
DO THING D
DO THING E
END
If you want to test more than two conditions, you're going to have to nest
your IF statements. For example:
IF (condition is true)
BEGIN
DO THING A
END
ELSE
IF (condition 2 is true)
BEGIN
DO THING B
END
ELSE
BEGIN
DO THING C
END
It's probably worth adding that in all my SQL programming career I've never
built this complex an IF statement; you should be
using CASE statements instead!
The importance of indentation
The following is valid SQL: it will print a suitable message according to the
day of the week:
|
Day of week |
Number |
Message |
|
Saturday, Sunday |
7, 1 |
It's the weekend! |
|
Friday |
6 |
It's Friday ... |
|
Any other day |
2 to 5 |
It's a weekday ... / Time to work ... |
Here's the SQL without indentation - it's nigh on impossible to read:
IF DatePart(weekday,GetDate()) IN (1,7)
PRINT 'It''s the weekend!'
ELSE
IF DatePart(weekday,GetDate()) = 6
PRINT 'It''s Friday ...'
ELSE
BEGIN
PRINT 'It''s a weekday ...'
PRINT 'Time to work ...'
END
If we add proper indentation, everything suddenly becomes much clearer:
IF DatePart(weekday,GetDate()) IN (1,7)
PRINT 'It''s the weekend!'
ELSE
IF DatePart(weekday,GetDate()) = 6
PRINT 'It''s Friday ...'
ELSE
BEGIN
PRINT 'It''s a weekday ...'
PRINT 'Time to work ...'
END
OK - having looked at the syntax of the IF statement, let's
look at some examples.
Example - using EXISTS to see if records exist
The following query prints out a suitable message if SQL Server can find
films which won ten or more Oscars:
IF EXISTS
(
SELECT * FROM tblFilm
WHERE FilmOscarWins >= 10
)
BEGIN
PRINT 'Double-digit Oscar winners found'
END
Example - using variables to compare two counts
The following example creates two variables to hold:
- the number of films lasting 3 hours or more; and
- the number of films winning at least 1 Oscar.
The SQL then compares the two numbers, and prints a suitable message:
DECLARE @LongFilms int
DECLARE @OscarWinners int
SET @LongFilms = (
SELECT COUNT(*) FROM tblFilm
WHERE FilmRunTimeMinutes >=180
)
SET @OscarWinners = (
SELECT COUNT(*) FROM tblFilm
WHERE FilmOscarWins > 0
)
IF @LongFilms > @OscarWinners
PRINT 'More long films'
ELSE
IF @LongFilms <>
PRINT 'More Oscar winners'
ELSE
PRINT 'Same number of each'
CASE WHEN is nearly always better
If at all possible, use CASE WHEN instead of an IF
to test multiple conditions, as it creates SQL which is much easier to
read (and write). Here's how you could have written some of the queries
above. First, the day of the week example:
PRINT
CASE DatePart(weekday,GetDate())
WHEN 1 THEN 'It''s the weekend!'
WHEN 7 THEN 'It''s the weekend!'
WHEN 6 THEN 'It''s Friday ...'
ELSE 'It''s a weekday ...'
END
You could write the last statement of the long films / Oscar winners query
above as:
PRINT
CASE
WHEN @LongFilms > @OscarWinners THEN 'More long films'
WHEN @LongFilms < @oscarwinners="">THEN 'More Oscar winners'
ELSE 'Same number of each'
END
In short, if you're using an IF statement, then there's a
good chance you should be using CASE WHEN instead.
