Mastering The SQL IF Statement
Contents
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
- The IF Statement only evaluates a condition that returns a boolean value.
- Multiple statements that are part of an IF block must be enclosed within BEGIN and END statements.
- 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:
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:
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:
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
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:
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:
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:
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:
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:
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:
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:
On the other hand, the following script returns data from the ‘Products’ table.
EXEC spSelectTable @TableName = ‘Products’
Output:
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
Backup Strategies For SQL Databases
