Post
Ask Question

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.


Similar Articles