This page has 0 threads | Add post

Conditions in SQL - IF / ELSE

Written by Andy Brown

In this tutorial

Syntax of a simple IF statement
Multiple IF conditions using ELSE
The importance of indentation
Example - using EXISTS to see if records exist
Example - using variables to compare two counts
CASE WHEN is nearly always better

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:

-- test if a condition is true

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:

-- test if a condition is true

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:

-- test if a condition is true

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:

-- test if any one of 3 conditions is true

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:

-- print different messages according to

-- the day of the week

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:

-- print different messages according to

-- the day of the week

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:

-- see if there are any films winning

-- 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:

-- see if there are more Oscar-winners or long films

DECLARE @LongFilms int

DECLARE @OscarWinners int

-- find number of 3 hour plus films

SET @LongFilms = (

SELECT COUNT(*) FROM tblFilm

WHERE FilmRunTimeMinutes >=180

)

-- find number of films winning Oscars

SET @OscarWinners = (

SELECT COUNT(*) FROM tblFilm

WHERE FilmOscarWins > 0

)

-- show which is more

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 different messages according to

-- the day of the week

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:

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.

Previous
tutorial

CTEs and derived tables

Previous
part

Data Types in T-SQL for SQL Server

Top of page

Next
part

Writing Loops in SQL

Next
tutorial

Trapping errors and debugging

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Programming in SQL

Conditions in SQL - IF / ELSE

Syntax of a simple IF statement

Multiple IF conditions using ELSE

The importance of indentation

Example - using EXISTS to see if records exist

Example - using variables to compare two counts

CASE WHEN is nearly always better

Head office

London

Manchester