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;