2 Ways To Write IF THEN ELSE In SQL SELECT Query In SQL Server


In SQL server, To write if then else in SQL select query we can use

  1. SELECT CASE statement (In all versions of SQL server)
  2. SELECT IIF logical function (From SQL server 2012 )

We will take an example Employee table which has columns EmpId, EmpName, Experience, Salary, Gender. Now we want to divide employees based upon their experience and salary.

If employee experience is more than 5 years consider them as “Senior Dev” Or “Junior Dev”. (if experience > 5)

And additionally, we can consider employees having a salary greater than 1000USD as also “Senior Dev” (if experience > 5 OR Salary > 10000)

Now we will use case statement and IIF function to select such employees.

Table of Contents

Method:1 Using Select Case to write if else then in select query example:

To add an additional column position based upon the employee’s experience (column greater than 5)

That means IF experience > 5 THEN “Senior Dev” ELSE “Junior Dev”.

SELECT CASE
       WHEN experience > 5  THEN "Senior Dev" ELSE "Junior Dev"
       END as Position, *
FROM Employee;

And additionally, we can add one more criteria “If salary greater than 1000USD” as shown below

 SELECT CASE      
        WHEN experience > 5  
            THEN "Senior Dev"
        WHEN salary > 1000
            THEN "Senior Dev" 
        ELSE "Junior Dev"        
   END as Position, * 
FROM Employee;

The above SQL query executes the below pseudo code

IF
 experience > 5 OR salary > 1000
THEN
 RETURN 'Senior Dev'
ELSE
 RETURN 'Junior Dev'
END

A case statement should have an END statement in SQL server.

If then else sql server

If then else sql server

Method 2: Using IIF Logical function to write if else then in select query example:

IIF is syntactic sugar for writing a CASE expression which introduced in SQL server 2012.

We can replace above IF THEN ELSE case statement to

SELECT
IIF(experience > 5 OR salary > 1000,'Senior Dev','Junior Dev')
AS Position, * FROM Employee

 

Wait before leaving. why can’t you follow me on twitter or be a friend on Facebook or linkedn to get in touch with me.

Spread the word

Get free link to download 900+ Material Icons
Avatar
Arunkumar Gudelli

I am One among a million Software engineers of India. I write beautiful markup.I make the Web useful.

Related