SQL Server 2012 introduced a new built-in logical function SQL IIF. It is a shorthand form of writing CASE statement logic or IF-ELSE logic.
We explored Case Statement in SQL in my earlier article. We use a case statement to return a result based on the defined condition. It is similar to an IF..ELSE…THEN statement evaluates an expression and returns the output. The case statement is available in almost all versions of SQL Server.
Let’s explore this SQL IIF function in this article.
SQL IIF Statement overview
We use the following format to write Case statement logic in SQL queries
SELECT CASE Expression When expression1 Then Result1 When expression2 Then Result2 … ELSE Result END
We can write this code using SQL IIF statement syntax as following.
IIF(boolean_expression, true_value, false_value)
- Boolean_expression: The first parameter in SQL IIF statement is a boolean expression. It should be a valid boolean expression else we get an exception
- True_Value: If the boolean_expression is TRUE, it returns value specified in the true_value parameter
- False_Value: If the boolean_expression is FALSE, it returns value specified in the false_value parameter
Let’s explore SQL IIF statement using examples.
Example 1: SQL IIF statement for comparing integer values
In the following example, specified Boolean expression return False ( 2>3 is not TRUE). Due to this, it returns the value specified in the false_value parameter.
|
1 |
SELECT IIF(2 > 3, 'TRUE', 'FALSE' ) |
Similarly, if the condition is TRUE (5>3 is TRUE) so it returns the value of true_value parameter.
|
1 |
SELECT IIF(5 > 3, 'TRUE', 'FALSE' ) |
Example 2: SQL IIF statement with variables
In the following example, we specified two integer variables and assigned values. We use variables for comparison in the IIF statement.
|
1 2 |
DECLARE @A INT = 80, @B INT = 70 SELECT IIF(@A >= @B, 'PASS', 'FAIL' ) |
The specified condition (80>70) is TRUE, so it returns the value PASS.
Example 3: SQL IIF statement to compare two strings
In the following example, we want to compare the string data using SQL IIF statement. We want to know the person liking based on the person name.
|
1 2 |
DECLARE @Person Varchar(100) = 'Raj' SELECT IIF(@Person='Raj','Likes Apple','NA') |
We can specify multiple conditions in SQL IIF statement as well. Let’s define the following condition
- Raj and Mohan both likes Apple
- Else all other persons like orange
|
1 2 |
DECLARE @Person Varchar(100) = 'Raj' SELECT IIF(@Person in('Raj', 'Mohan'),'Likes Apple','Likes Orange') |
The query should return Likes Orange if the person name is not in the IN clause. In the following example, person Vinay does not exist in the variable @Person, so it returns value for a false clause.
Example 4: Nested SQL IIF statement
We can use Nested SQL IIF statement as well to specify multiple conditions
|
1 2 3 4 |
DECLARE @Person VARCHAR(100)= 'Vinay'; SELECT IIF(@Person = 'Raj', 'Likes Apple', IIF(@Person = 'Vinay', 'Likes Orange', 'Person does not exists in the list')) AS Result; |
In this SQL IIF function, it checks for the first condition and if the condition is TRUE, it returns the value. In our case, it is FALSE ( Person Vinay does not match with Raj).It checks for the other condition and returns results if it is TRUE.
If none of the conditions is true, it returns the default false message.
Example 5: SQL IIF statement with a table column
In the previous examples, we either specify values directly in the IIF statement or specified variables for it. Usually, we want to use it with the existing data in a SQL table. We can use the IIF statement with a table column as well.
In the following example, we want to know the supplier name from the PurchaseOrders table in the WideWorldImporters database.
|
1 2 3 4 5 6 7 8 |
DECLARE @SupplierID TINYINT= 1; SELECT DISTINCT @SupplierID AS SupplierID, IIF(@SupplierID = 1, 'A Datum Corporation', IIF(@SupplierID = 2, 'Contoso, Ltd.', IIF(@SupplierID = 3, 'Consolidated Messenger', IIF(@SupplierID = 4, 'Fabrikam, Inc.', 'Humongous Insurance')))) SupplierName FROM [WideWorldImporters].[Purchasing].[PurchaseOrders]; |
If the supplier name does not exist for a particular supplier id, it returns the specified false value in SQL IIF statement.
Example 6: SQL IIF statement with a table column and aggregate function
|
1 2 3 4 5 6 7 8 9 10 |
SELECT SUM(IIF(status = 1, 1, 0)) AS 'Pending', SUM(IIF(status = 2, 1, 0)) AS 'Processing', SUM(IIF(status = 3, 1, 0)) AS 'Rejected', SUM(IIF(status = 5, 1, 0)) AS 'Completed', COUNT(*) AS Total FROM [AdventureWorks2017].[Sales].[SalesOrderHeader] WHERE YEAR(shipdate) = 2014; |
Example 7: SQL IIF statement and data type precedence
We might have different data types for the results. If we have different data types specified in the result conditions, SQL Server gives the highest precedence data type. In the following example, for the false condition(11>19), the result will be the false_value argument, i.e. 100. It should be of e integer data type, but we get the output 40.0 because the other argument float (2.6) is of high precedence than an integer.
|
1 |
SELECT IIF(11 > 19, 2.6, 40) |
You can check the data type precedence in the following image
Example 8: SQL IIF with NULL values
We should be careful in NULL values inside the SQL IIF function.
|
1 2 3 |
SELECT IIF(100 > 99, NULL, NULL) AS Result; |
We cannot specify NULL in both the true and false arguments. We get the following error message.
Msg 8133, Level 16, State 1, Line 1 At least one of the result expressions in a CASE specification must be an expression other than the NULL constant.
In the error message, you can see it gives information about the CASE specification however we used SQL IIF function. We will look at this in the next example.
You should specify at least one of a true or false argument.
|
1 2 |
SELECT IIF(100 > 99, 'Hello', NULL) AS Result; |
The similarity between SQL IIF statement and CASE Statement
In the introduction of SQL IIF Statement, we mentioned that it is a shorthand form of writing CASE statement logic or IF-ELSE logic. Yes, it is true. Internally SQL Server converts the statement into the CASE statement and executes. We can check it using the actual execution plan of a query.
Execute the query from Example 6 with an Actual execution plan.
In the actual execution plan, look at the properties of Compute Scalar. You can see it executes the CASE statement internally.
Conclusion
In this article, we explored the SQL IIF Statement that is a new way of writing CASE statement related conditions. It is available from SQL 2012 onwards. You should explore this to be familiar with this. If you have any comments or questions, feel free to leave them in the comments below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023