SQL Difference Between IIF Vs CASE Function

In this post, we'll go through the definitions of the IIF and CASE functions, as well as the contrasts between them.

SQL IIF Function

SQL Server 2012 introduced the SQL IIF function, a new built-in logical function.

The SQL Server IIF can be thought of as a shortcut for IF Else and CASE statements.

The IIF function in SQL Server will take three arguments.

The Boolean expression, which returns true or false, is the first parameter.

The second parameter will be returned as a result if the IIF statement returns TRUE. Otherwise, the output of the third argument will be returned.

Example: We have table Book with Price column, utilise the IIF() function.

Within IIF() functions, the following example employs the IIIF() function:

Sql-server,Sqlite,Ms access

SELECT Name, Price, IIF(Price >180, 'MORE', 'LESS')
FROM Book;

In above table we have 4 values but we get an output which book price have below 180 that is indicate in word Less otherwise it will be More.

Output: The output of above query is

SQL CASE Function

CASE function in SQL are analogous to control flow statements (something like IF ELSE ).

This statement returns the result set after evaluating the series of conditional expressions provided in WHEN.

SQL Server CASE Statements are divided into two categories:

1. Simple Statement:

   The WHEN keyword is used to compare the input expression to a series of test expressions in the SQL simple case expression. It will return the corresponding result expression, followed by the THEN keyword, once it has found the match. If no match is found, the default expression is returned by the SQL Server Case statement.

2. Searched SQL Case Statement:

   This comes in handy for making more powerful and sophisticated comparisons. The WHEN keyword is followed by a series of Boolean expressions, and the SQL Server Case statement evaluates the conditions within them.If the condition is TRUE, result expression will be returned, followed by the THEN keyword.If no match is found, the default expression specified in the ELSE block is used.

Example 1: Let's look at Employee Table to check condition

Consider the following query, which selects all fields from the Faculty table. We'd want to provide the values in the Gender field in a more accessible style because they're single character values (M/F).

SELECT Name, Gender,
CASE Gender
 WHEN 'Male' THEN 'M'
 WHEN 'Female' THEN 'F' 
END
FROM Employee

Output:The result of above query is

SQL Difference Between IIF And CASE Functions

There are 5 distinguish between IIF & CASE function:-