SQL Server IF…ELSE Condition Statement: T-SQL Select Example

Why do you need Conditional Statements?

Conditional statements in SQL Server help you define different logic and actions for different conditions. They let you perform different actions based on the conditions defined within the statement. In real life, you perform many actions that depend on the outcome of some other activity or situation.

Some real-time examples of a conditional statement are:

• If it rains tomorrow, I will plan a road trip.
• If flight tickets are less than $400 from my city, then I will go on vacation in Europe; otherwise, I will prefer a nearby tourist spot.

Here, you can see that one action, like the road trip above, is conditionally dependent on the outcome of another activity, which is “whether it will rain or not tomorrow.” Similarly, MS SQL Server also provides the capability to execute a 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. The figure below explains how the IF and ELSE branches work in SQL Server:

• If the condition evaluates to True, then the T-SQL statements that follow the IF condition in SQL Server are executed.
• If the condition evaluates to False, then the T-SQL statements that follow the ELSE keyword are executed.
• Once either the IF T-SQL statements or the ELSE T-SQL statement is executed, the other unconditional T-SQL statements continue 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 a Boolean expression, i.e., the condition results in a Boolean value when it is evaluated.
• An IF…ELSE statement in SQL can conditionally handle a single T-SQL statement or a block of T-SQL statements.
• A block of statements should start with the keyword BEGIN and close with the keyword END.
• Using BEGIN and END helps SQL Server identify the statement block that needs to be executed and separate it from the rest of the T-SQL statements that are not part of the IF…ELSE T-SQL block.
• ELSE is optional.

IF…ELSE with Only a Numeric Value in Boolean Expression

In this first example, the Boolean expression uses only numeric values. Consider the condition below, which is always true because 1 equals 1.

Condition: TRUE

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

Running the query with the true condition (1=1) prints the IF branch message:

Condition: FALSE

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

With the false condition (1=2), the ELSE branch runs instead:

Assume that you have a table named ‘Guru99’ with two columns and four rows, as displayed below. We will use this ‘Guru99’ table in the examples that follow.

IF…ELSE with a Variable in Boolean Expression

Instead of constant numbers, the Boolean expression can compare a variable. The following example declares an integer variable and tests its value.

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

Because @Course_ID equals 4, the IF branch runs and returns the row where Tutorial_ID is 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

Here the condition is false, so the ELSE branch runs and returns every row where Tutorial_ID is not 4:

IF…ELSE with BEGIN…END

When a branch must run more than one statement, wrap the statements in a BEGIN…END block. The example below runs two SELECT statements in each branch.

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

Since @Course_ID is less than or equal to 2, the IF block runs both SELECT statements inside 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

This time the condition is false, so the ELSE block executes its two SELECT statements:

IF Statement with No ELSE

You can use an IF statement in SQL without an ELSE part, because 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 single row:

Executing the false condition gives no output. Consider the following query:

DECLARE @Course_ID INT = 2

IF (@Course_ID <=0)
	Select * from Guru99 where Tutorial_ID = 1

The result is empty, as shown below:

Nested IF…ELSE Statements

Unlike some other programming languages, SQL Server does not provide an ELSE IF statement inside an IF…ELSE condition. Instead, you can nest one IF…ELSE inside another, as 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;

For an @age of 60, the nested IF…ELSE prints the following result:

• The code prints ‘underage’ if the value of @age is below 18.
• If not, the ELSE part is executed. The ELSE part contains a nested IF…ELSE.
• If the value of @age is below 50, this prints ‘You are below 50’. If none of these conditions is true, the code prints ‘Senior’.

FAQs