In SQL server, To write if then else in SQL select query we can use
- SELECT CASE statement (In all versions of SQL server)
- 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.
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