SQL Difference Between IIF Vs CASE Function
In this post, we'll go through the definitions of the
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.
|1||MySql All-in-One For Dummies||180|
|2||The SQL Programming Language||185|
|3||Postgre Sql Server Programming||105|
|4||SQL For Microsoft Access||170|
Within IIF() functions, the following example employs the IIIF() function:
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
|MySql All-in-One For Dummies||180||less|
|The SQL Programming Language||185||more|
|Postgre Sql Server Programming||105||less|
|SQL For Microsoft Access||170||less|
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
SQL Server CASE Statements are divided into two categories:
WHENkeyword 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
THENkeyword, once it has found the match. If no match is found, the default expression is returned by the SQL Server Case statement.
Searched SQL Case Statement:This comes in handy for making more powerful and sophisticated comparisons. The
WHENkeyword 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
THENkeyword.If no match is found, the default expression specified in the
ELSEblock is used.
Example 1: Let's look at Employee Table to check condition
|3||Vinoth Kumar||Male||Delhi||Web Design||7000|
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
|Name||Gender||Rename of Gender|
SQL Difference Between IIF And CASE Functions
There are 5 distinguish between IIF & CASE function:-
|You may easily replace the IIF function with a CASE expression. Simultaneously||The IIF function can be used to replace a CASE expression that returns one of two values.|
|In SQL Server 2012, IIF was introduced.||SQL Server 2008 introduces CASE.|
|This function is used to return one of the two values.||Returns one value from a set of two or more.|
|It takes three arguments.||It has access to two statements.|
|The second argument will be returned as a result if the IIF statement returns TRUE. Otherwise, the output of third argument will be displayed.||If the condition is TRUE, result expression will be returned, followed by the