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:

How If and Else Works
How if and else works
  • 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'

IF…ELSE with the only numeric value in Boolean Expression

Condition: FALSE

IF (1=2)
PRINT 'IF STATEMENT: CONDITION IS TRUE'
ELSE
PRINT 'ELSE STATEMENT: CONDITION IS FALSE'

IF…ELSE with the only numeric value in Boolean Expression

Assumption: Assume that you have the table as ‘Guru99’ with two columns and four rows as displayed below:

IF…ELSE with the only numeric value in Boolean Expression

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

IF...ELSE with the variable in Boolean Expression

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 the variable in Boolean Expression

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

IF...ELSE with Begin 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...ELSE with Begin 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:

IF statement with No Else

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

IF statement with No Else

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.

Nested IF…Else Statements

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.