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:
Here is a more concrete example:
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:
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.
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:
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:
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:
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.
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.
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.
Similarly, in the following case, the AND operator returns false, and therefore the statements after the ELSE block execute.
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.
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.
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.’
On the other hand, the following script returns data from the ‘Products’ table.
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!