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.