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
SELECT CASEstatement (In all versions of SQL server)
SELECT IIFlogical 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.
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 function 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
So If you are using SQL server 2012 above it’s best to use
IIF function to write if else then in SQL select query.