Request a callback
Email us

Mastering The SQL IF Statement

  • Read Time7 mins

In this article, you will see how to use the SQL IF statement for the conditional execution of your SQL script.

You will see what SQL IF statements are, how to use them in simple and complex situations, and what rules and best practices you should follow while using the SQL IF statements.

IF statements are extremely widely used for conditional logic and covered during our advanced SQL course.

 

What Are SQL If Statements?

SQL IF statements allow you to implement conditions for executing different code blocks in your SQL scripts.

If you are familiar with a programming language, you will already know how to use the IF / Else statements.

The SQL IF statement offers the same functionality in SQL code.

 

Why Use SQL IF Statements?

SQL IF statements allow you to perform a number of functions at the same time.

If you would like, you can implement two entirely different execution paths inside the same file and execute them subject to defined conditions being satisfied.

A simple scenario where you would use an IF statement would be that you want to fetch records from a table if the average value of a table column exceeds a certain threshold, similar to a SQL SELECT statement.

 

Creating A SQL IF Statment

The syntax of the SQL IF Statement is simple. You use the ‘IF’ clause to implement the SQL IF statement as shown in the following code snippet:

 

IF boolean_condition is True
// Do something

 

Here is a more concrete example:

 

DECLARE @Age INT
SET @Age = 18

IF @Age < 20
PRINT ‘Turn Left’

 

If you want to execute multiple statements inside an IF statement, you need to enclose the multiple code lines inside the BEGIN and END clauses, as shown below:

 

IF boolean_condition is True
BEGIN
// Do something
// Do something
END

 

Rules & Best Practice For SQL IF Statements

  1. The IF Statement only evaluates a condition that returns a boolean value.
  2. Multiple statements that are part of an IF block must be enclosed within BEGIN and END statements.
  3. Always try to indent your code, particularly if you have nested IF statements.

 

Simple Examples Of SQL IF Statments

Let’s state by looking at some very simple examples of IF statements.

The following script prints the string ‘Turn Left’ on the console if the @Age variable stores a value less than 20, which in this case is True.

Hence, you will see ‘Turn Left’ printed in the output.

 

DECLARE @Age INT
SET @Age = 18

IF @Age < 20
PRINT ‘Turn Left’

 

Output:

 

some very simple examples of IF statements. image 1

 

As earlier said, in case of multiple statements inside an IF block, you need to use the BEGIN and END clauses, as shown in the script below:

 

DECLARE @Age INT
SET @Age = 18

IF @Age < 20
BEGIN
PRINT ‘Turn Left’
PRINT ‘You get 20% discount’
END

 

Output:

 

some simple examples of IF statements. image 2

 

The SQL IF statement is often complemented by the ELSE statement. You can use the ELSE block if you want to execute an SQL script where the IF statement returns false.

Expanding on our previous example:

 

DECLARE @Age INT
SET @Age = 18

IF @Age > 20
BEGIN
PRINT ‘Turn Left’
PRINT ‘You get 20% discount’
END
ELSE
PRINT ‘Turn Right’

 

Output:

 

some simple examples of IF statements. image 3

 

As with the IF statement, you can execute multiple statements inside an ELSE block, you simply enclose them inside BEGIN and END statements.

Extending our earlier example further:

 

DECLARE @Age INT
SET @Age = 18IF @Age > 20
BEGIN
PRINT ‘Turn Left’
PRINT ‘You get 20% discount’
ENDELSE
BEGIN
PRINT ‘Turn Right’
PRINT ‘You get 30% discount’
END

 

Output:

 

some very simple examples of IF statements. image 4

 

Where you want to implement multiple conditional statements, you can use the IF, ELSE IF and ELSE statements in combination.

For instance, in the following script, the first IF condition checks if the @Age is greater than 20, which returns false.

The SQL compiler then moves to the ELSE IF condition, which checks if the @Age variable is greater than 30.

The ELSE IF condition also returns false.

Therefore, the statement inside the ELSE block executes.

 

DECLARE @Age INT
SET @Age = 18

IF @Age > 20
BEGIN
PRINT ‘Turn Left’
PRINT ‘You get 20% discount’
END

ELSE IF @Age > 30
BEGIN
PRINT ‘Turn Right’
PRINT ‘You get 30% discount’
END

ELSE
BEGIN
PRINT ‘You dont get any discount’
END

 

Output:

 

some very simple examples of IF statements. image 5

 

More Complex SQL IF Statments

1. NESTED IF STATEMENTS

You can implement an IF Statement inside another IF statement. This is known as a nested IF statement.

The outer IF statement in the script below first checks if the @Age variable contains a value greater than 50, which returns true.

The code control then moves inside the outer IF statement, where it encounters another IF statement which checks if the @Age variable is greater than 65.

This also returns true. Therefore the statements inside the inner IF block are executed.

 

DECLARE @Age INT
SET @Age = 67

IF @Age > 50
BEGIN
IF @Age > 65
BEGIN
PRINT ‘You get 50% discount’
PRINT ‘Get your voucher from the counter’
END
ELSE
BEGIN
PRINT ‘You get 30% discount’
PRINT ‘Get your voucher online’
END  
END

 

Output:

 

some complex examples of sql if statements. image 1
2. USING OR & AND STATEMENTS

You can implement logical OR and AND conditions to evaluate combined boolean conditions with an IF statement.

For instance, the IF statement in the script below returns true if the value of the @Age variable is greater than 20 or the @Gender variable contains the string ‘Male’.

Since the second condition is true, the OR operator returns true, and the statements inside the IF block are executed.

 

DECLARE @Age INT, @Gender VARCHAR(50)
SET @Age = 18
SET @Gender = ‘Male’

IF @Age > 20 or @Gender = ‘Male’
BEGIN
PRINT ‘Turn Left’
PRINT ‘Exit from Red Door’
END
ELSE
BEGIN
PRINT ‘Turn Right’
PRINT ‘Exit from Green door’
END

 

Output:

 

some complex examples of sql if statements. image 2

Similarly, in the following case, the AND operator returns false, and therefore the statements after the ELSE block execute.

 

DECLARE @Age INT, @Gender VARCHAR(50)
SET @Age = 18
SET @Gender = ‘Male’

IF @Age > 20 and @Gender = ‘Male’
BEGIN
PRINT ‘Turn Left’
PRINT ‘Exit from Red Door’
END
ELSE
BEGIN
PRINT ‘Turn Right’
PRINT ‘Exit from Green door’
END

 

Output:

 

some complex examples of sql if statements. image 3

Let’s see a more concrete example of IF statements.

The script below fetches data from different tables of the Northwind database based on the table name stored in the @table variable.

 

USE Northwind

DECLARE @table VARCHAR(50)

SET @table = ‘Customers’

IF @table = ‘Products’
SELECT * FROM Products

ELSE IF @table = ‘Orders’
SELECT * FROM Orders

ELSE
SELECT * FROM Categories

 

Output:

 

some complex examples of sql if statements. image 4

 

Using A SQL IF Statment In A Stored Procedure

Finally, you can also use an SQL IF statement inside a stored procedure.

The following script defines a stored procedure named ‘spSelectTable’, which accepts the table name as a parameter value.

The stored procedure returns the data from the matched table inside the Northwind database.

 

USE Northwind

GO

CREATE PROC spSelectTable (@TableName VARCHAR(50))
AS
BEGIN
IF @TableName = ‘Products’
SELECT * FROM Products
ELSE
SELECT * FROM Categories
END,

 

For instance, the following script tries to fetch the records from the ‘ABC’ table. In this case, the ‘spSelectTable’ stored procedure returns data from the table name specified in the ELSE block, i.e., ‘Categories.’

 

EXEC spSelectTable @TableName = ‘ABC’

 

Output:

 

useing an SQL IF statement inside a stored procedure. image 1

 

On the other hand, the following script returns data from the ‘Products’ table.

 

EXEC spSelectTable @TableName = ‘Products’

 

Output:

 

useing an SQL IF statement inside a stored procedure. image 2

 

Conclusion

The SQL IF statement is extremely handy, particularly when you want to separate logical flows in your SQL script.

In this article, you saw how to execute an SQL IF Statement in SQL Server.

In addition to the IF statements, you can also use CASE statements for implementing conditional logic in your SQL script.

CASE statements are often preferred over standard IF statements since they are ANSI standards, which makes them portable to other databases.

A good way to learn and practise this and many other SQL functions is through SQL learning apps accessible through your iPhone or Android phone. Give it a go!

 

Related Articles

Introduction To SQL Joins

Temporary Tables In SQL

Backup Strategies For SQL Databases

 

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.


He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.


He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.

Related Articles