IIF in Microsoft SQL Server

IIF is a logical function, stands for "Immediate If" and allows us to return one of two values depending on whether a condition is true or false.

It is similar to the CASE statement but is more concise for simple conditional evaluations.

Syntax: IIF(condition, true_value, false_value)

Condition: The expression that is evaluated. If it evaluates to TRUE, the function returns true_value; otherwise, it returns false_value.
True_value: The value to return if the condition evaluates to TRUE.
False_value: The value to return if the condition evaluates to FALSE.

Example.

SELECT IIF(1 = 1, 'True', 'False') AS Result;

Result

True

Example in MS SQL SERVER: Consider an Employee table in an organization

Create Table

create table tbl_Employee
(
emp_id int not null primary key identity(1,1),
emp_Name varchar(200),
emp_Salary numeric(10,2),
emp_Department varchar(150),
emp_Hiring_Date date
)

Insert Sample Data

INSERT INTO tbl_Employee(emp_Name, emp_Salary, emp_Hiring_Date)
SELECT 'Sreenath', 80000, '2000-01-01'
union
SELECT 'Adarsh', 90000, '2010-01-01'
union
SELECT 'Sarath', 100000, '2020-01-01'

View Table Data

SELECT * FROM tbl_Employee

emp_id	emp_Name	emp_Salary	emp_Department	emp_Hiring_Date
1	Adarsh	90000	NULL	01-01-2010
2	Sarath	100000	NULL	01-01-2020
3	Sreenath	80000	NULL	01-01-2000

Simple Conditional Check

SELECT emp_Name, IIF(emp_Salary > 90000, 'High', 'Low') AS SalaryLevel
FROM tbl_Employee
ORDER BY emp_id

emp_Name	SalaryLevel
Adarsh	Low
Sarath	High
Sreenath	Low

In this example, the query evaluates the emp_Salary field for each employee. If the salary is greater than 90,000, it returns 'High'; otherwise, it returns 'Low'.

Handling NULL Values

SELECT emp_Name, IIF(ISNULL(emp_Department, '') = '', 'No Department', emp_Department) AS Department_Name
FROM tbl_Employee

emp_Name	Department_Name
Adarsh	No Department
Sarath	No Department
Sreenath	No Department

This example checks if the emp_Department field is NULL or empty. If it is, it returns 'No Department'; otherwise, it returns the actual department name.

Combining with other Functions

SELECT emp_Name, IIF(emp_Hiring_Date < '2015-01-01', 'Veteran', 'Newcomer') AS EmployeeStatus
FROM tbl_Employee

emp_Name	EmployeeStatus
Adarsh	Veteran
Sarath	Newcomer
Sreenath	Veteran

The IIF function is used to label employees as 'Veteran' if they were hired before January 1, 2015, and 'Newcomer' if they were hired after that date.

IIF Advantages

Ease of Use: It simplifies conditional logic in SQL queries.
Concise: The IIF function makes the code more readable and shorter compared to using a CASE statement for simple conditions.

IIF Limitations

Compatibility: The IIF function is specific to certain SQL implementations, such as Microsoft SQL Server. It may not be available or may be implemented differently in other SQL databases.
Complexity: For more complex conditional logic, the CASE statement might be more appropriate as it is more flexible and can handle multiple conditions.

The IIF function in SQL is a powerful tool for simplifying conditional expressions, making your queries easier to read and write when dealing with straightforward true/false logic. For more complex scenarios, the CASE statement remains a more versatile option.

The query file is attached for your reference.