Open In App

IIF() Function in SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

IIF() function judges or evaluates the first parameter and returns the second parameter if the first parameter is true, otherwise, it returns the third parameter. IIF() function used in SQL Server to add if-else logic to queries.IIF is not supported in dedicated SQL pools in Azure Synapse Analytics.

Syntax:

IIF(boolean_value, true_1value, false_value)

Parameters Explanation

The SQL Server IIF() function has three parameters. 

  1. boolean_value – It is a value to be judged. It must be a valid boolean value, or the function will raise an error.
  2. true_value – It is the value to be the result if the boolean_value to true.
  3. false_value – It is the value to be the result if the boolean_value to false.

The IIF() function is similar to a CASE expression

CASE  
   WHEN boolean_expression  
       THEN true_value
   ELSE
       false_value
END

Simple IIF Example

To use the IIF() function to check if 40 < 60 :

Query:

SELECT  IIF(40 < 60, 'True', 'False') AS Result ; 

Output:

True 

Let us assume we have below sample table named “Customer”:

Query:

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, 
LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

img1

 

IIF() within IIF() Functions

To use IIF() function with table column. Below example uses IIF()function within IIF() functions:

Query:

SELECT    
   IIF(Age = 24, 'Selected',  
       IIF(Age =21, 'InProgress',
           IIF(Age =30, 'Rejected',
               IIF(Age =22, 'Selected', NULL)
           )
       )
   ) AS Status,

   COUNT(CustomerName) AS Count
FROM Customer
GROUP BY Age;

Output:

img4

 

IIF() Function with Aggregate Functions

 To use IIF() function with aggregate functions. Below example uses IIF()function with the SUM() function:

Query:

SELECT 
   CustomerName,
   SUM(IIF(Country = 'India', 1, 0)) AS IndianCustomers,
   SUM(IIF(Country = 'Australia', 1, 0)) AS AustralianCustomers,
   SUM(IIF(Country = 'Spain', 1, 0)) AS SpanishCustomers
FROM Customer
GROUP BY CustomerName;

Output:

Here, the IIF() function results in 1 or 0 if the status is matched. The SUM() function results in the number of each status.

img5

 


Last Updated : 14 Apr, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads