SQL IIF Function

SQL IIF

The SQL IIF function is a shorthand method for writing conditional expressions in SQL Server. It is similar to the CASE statement but provides a more concise syntax. The IIF function evaluates a condition and returns one of two values depending on whether the condition is TRUE or FALSE.

In this tutorial, we will explore the SQL IIF function, its syntax, and examples demonstrating its usage.

---

Syntax of SQL IIF Function

The basic syntax of the IIF function is:

SELECT IIF(condition, true_value, false_value) AS result;

Explanation:

• condition: The logical condition to evaluate.
• true_value: The value returned if the condition is TRUE.
• false_value: The value returned if the condition is FALSE.

---

Step-by-Step Examples Using SQL IIF

1. Using IIF to Classify Student Grades

Let’s create a students table and use the IIF function to classify students as “Pass” or “Fail” based on their marks.

In MySQL:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    marks INT
);

In SQL Server:

CREATE TABLE students (
    id INT PRIMARY KEY IDENTITY(1, 1),
    name VARCHAR(50),
    marks INT
);

Insert sample records:

INSERT INTO students (name, marks)
VALUES 
('Arjun', 85),
('Ram', 40),
('Priya', 55);

Now, we use the IIF function to determine if a student has passed or failed.

SELECT name, marks, IIF(marks >= 50, 'Pass', 'Fail') AS result
FROM students;

Explanation:

• If a student’s marks are 50 or more, the result is 'Pass'.
• If a student’s marks are below 50, the result is 'Fail'.

---

2. Using IIF for Employee Salary Classification

Let’s create an employees table and use the IIF function to classify employees based on their salary.

In SQL Server:

CREATE TABLE employees (
    id INT PRIMARY KEY IDENTITY(1, 1),
    name VARCHAR(50),
    salary DECIMAL(10,2)
);

Insert sample records:

INSERT INTO employees (name, salary)
VALUES 
('Arjun', 75000),
('Ram', 40000),
('Priya', 60000);

Now, we classify employees as “High Salary” or “Low Salary” using IIF.

SELECT name, salary, IIF(salary > 50000, 'High Salary', 'Low Salary') AS salary_status
FROM employees;

Explanation:

• If an employee’s salary is greater than 50,000, they are classified as 'High Salary'.
• Otherwise, they are classified as 'Low Salary'.

---

Conclusion

In this SQL IIF function tutorial, we covered:

1. The syntax of the IIF function.
2. Using IIF for student grade classification.
3. Using IIF for employee salary classification.