SQL IIF Function

IIF returns a value from two values based on a boolean expression.

IIF is a shorthand for a CASE expression.

Example

#

This example returns one of two values by checking a boolean expression.
If true, the first value is returned, otherwise the second value.

SELECT IIF(15 % 2 = 0, 'Even', 'Odd') AS Result
Result:  1 record
Result
Odd

Alternatively, the modulus operator (%) can also test whether a number (15) is even or odd.


Syntax

Syntax of the IIF function.

IIF(boolean-expression, true-value, false-value)

boolean-expression -- a boolean expression to be evaluated. If this is not a boolean expression, an error is returned.

true-value -- a value or column to return if the boolean-expression result to true.

false-value -- a value or column to return if the boolean-expression result to false.

More Examples

IIF. Categorize.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products and categorize them as cheap or expensive. Sort by product name.
SELECT ProductName, UnitPrice,
       IIF(UnitPrice < 50, 'Cheap', 'Expensive') AS Category
  FROM Product
 ORDER BY ProductName 
Result:  78 records
ProductName Unit Price Category
Alice Mutton 39.00 Cheap
Aniseed Syrup 10.00 Cheap
Boston Crab Meat 18.40 Cheap
Camembert Pierrot 34.00 Cheap
Carnarvon Tigers 62.50 Expensive
Chai 18.00 Cheap

IIF. Check for NULL.

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List suppliers with contact information. Use fax, but for those without fax use phone number instead.
SELECT CompanyName, 
       IIF(Fax IS NOT NULL,
           Fax, 'Phone: ' + Phone) AS Contact
  FROM Supplier
Result:  29 records
CompanyName Contact
Exotic Liquids Phone: (171) 555-2222
New Orleans Cajun Delights Phone: (100) 555-4822
Grandma Kelly's Homestead (313) 555-3349
Tokyo Traders Phone: (03) 3555-5011
Cooperativa de Quesos 'Las Cabras' Phone: (98) 598 76 54

You may also like



Guides


vsn 3.1