SQL Server IF…ELSE Condition Statement: T-SQL Select Query Example
Why do you need Conditional Statements?
Conditional statements in the SQL server help you to define different logics and actions for different conditions. It allows you to perform different actions based on conditions defined within the statement. In real life, you perform many actions dependent on the outcome of some other activity or situation.
Some real-time examples of SQL case statement are:
- If it rains tomorrow, I will plan on a road trip.
- If flight tickets are less than $400 from my city, then I will go on vacation in Europe, else I will prefer some nearby tourist spot.
Here, you can see that one action, like Road trip above, is conditionally dependent on the outcome of another activity which is “whether it will rain or not tomorrow!”
Similarly, MS SQL also provides the capability to execute T-SQL statement conditionally.
IF… Else statement in SQL Server
In MS SQL, IF…ELSE is a type of Conditional statement.
Any T-SQL statement can be executed conditionally using IF… ELSE.
Below figure explains IF ELSE in SQL server:
- If the condition evaluates to True, then T-SQL statements followed by IF condition in SQL server will be executed.
- If the condition evaluates to False, then T-SQL statements followed by ELSE keyword will be executed.
- Once, either IF T-SQL statements or ELSE T-SQL statement is executed, then other unconditional T-SQL statements continues execution.
IF… Else Syntax and rules in SQL
Syntax:
IF <Condition> {Statement | Block_of_statement} [ ELSE {Statement | Block_of_statement}]
Rules:
- The condition should be Boolean Expression, i.e., condition results in Boolean value when evaluated.
- IF ELSE statement in SQL can conditionally handle a single T-SQL statement or block of T-SQL statements.
- Block of statement should start with keyword BEGIN and close with keyword END.
- Using BEGIN and END helps SQL server to identify statement block that needs to be executed and separate it from rest of the T-SQL statements which are not part of IF…ELSE T-SQL block.
- ELSE is optional.
IF…ELSE with the only numeric value in Boolean Expression.
Condition: TRUE
IF (1=1) PRINT 'IF STATEMENT: CONDITION IS TRUE' ELSE PRINT 'ELSE STATEMENT: CONDITION IS FALSE'
Condition: FALSE
IF (1=2) PRINT 'IF STATEMENT: CONDITION IS TRUE' ELSE PRINT 'ELSE STATEMENT: CONDITION IS FALSE'
Assumption: Assume that you have the table as ‘Guru99’ with two columns and four rows as displayed below:
We will use ‘Guru99’ table in further examples
IF…ELSE with the variable in Boolean Expression.
Condition: TRUE
DECLARE @Course_ID INT = 4 IF (@Course_ID = 4) Select * from Guru99 where Tutorial_ID = 4 ELSE Select * from Guru99 where Tutorial_ID != 4
Condition: FALSE
DECLARE @Course_ID INT = 4 IF (@Course_ID != 4) Select * from Guru99 where Tutorial_ID = 4 ELSE Select * from Guru99 where Tutorial_ID != 4
IF…ELSE with Begin End
Condition: TRUE
DECLARE @Course_ID INT = 2 IF (@Course_ID <=2) BEGIN Select * from Guru99 where Tutorial_ID = 1 Select * from Guru99 where Tutorial_ID = 2 END ELSE BEGIN Select * from Guru99 where Tutorial_ID = 3 Select * from Guru99 where Tutorial_ID = 4 END
Condition: FALSE
DECLARE @Course_ID INT = 2 IF (@Course_ID >=3) BEGIN Select * from Guru99 where Tutorial_ID = 1 Select * from Guru99 where Tutorial_ID = 2 END ELSE BEGIN Select * from Guru99 where Tutorial_ID = 3 Select * from Guru99 where Tutorial_ID = 4 END
IF statement with No Else
You can use an IF statement in SQL without an ELSE part. Remember we stated that the ELSE part is optional. For example:
DECLARE @Course_ID INT = 2 IF (@Course_ID <=2) Select * from Guru99 where Tutorial_ID = 1
It prints the following:
Executing the false condition will give no output. Consider the following query
DECLARE @Course_ID INT = 2 IF (@Course_ID <=0) Select * from Guru99 where Tutorial_ID = 1
The result it
Nested IF…Else Statements
Unlike other programming languages, you cannot add an ELSE IF statement within an IF ELSE condition in SQL. This is why you can nest IF ELSE in SQL query statements. It is demonstrated below:
DECLARE @age INT; SET @age = 60; IF @age < 18 PRINT 'underage'; ELSE BEGIN IF @age < 50 PRINT 'You are below 50'; ELSE PRINT 'Senior'; END;
- In this example, the code will print underage if the value of @age is below 18.
- If not, the ELSE part will be executed. The ElSE part has a nested IF…ELSE.
- If the value of @age is below 50, this will print You are below 50. If none of these conditions is true, the code will print Senior.
Summary
- Variables are the object which acts as a placeholder.
- Block of statement should start with keyword BEGIN and close with keyword END.
- Else is optional to use in IF… ELSE statement
- Nesting SQL IF ELSE condition in another IF…ELSE statement is also possible.