Believe it or not, SQL Server has a ternary operator like many other computer languages out there. Like other ternary operators, it can shorten the syntax of a very simple decision structure to just one line of code.
The SQL Server ternary operator is the IIF function. Here is the syntax:
IIF(<boolean-expression>, <return value if true>, <return value if false>)
Let’s look at a very simply example. Here’s the content of a VehicleInventory table:
Let’s say we wanted to create a new derived column that outlines if the vehicle has ‘High Miles‘ or ‘Low Miles‘. We’ll say a vehicle has high miles if its mileage is over 100,000. We can do this using a fairly easy CASE expression:
However, the same result set could be accomplished with less code by using the IIF function:
Nothin’ to it!
The IIF function is meant to be a simplified, shorthand way of writing a very simple CASE expression.
Next Steps:
I hope you found this quick tutorial helpful!
Of course, the IIF function is meant to be a substitute for a very simple CASE expression with only one condition and two possible return values. If you need to write something more complex, you’ll want to just use the standard CASE expression. Make sure you check out the full tutorial on the CASE expression to understand how it works:
SQL Server CASE Statement: A How-To Guide
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
