SQL IF Vs IIF Function

The primary distinction between IF and IIF functions will be discussed in this session.

SQL IF Function

The IF operator works as a short-circuiting conditional, only evaluating the true or false input based on the conditional's value.

Example 1:IF Operator

Sql-server, Mysql

SELECT IF (5 < 6 ,'True', 'False');

Output: The result will be


The expression to evaluate in this example is 1 2 Because 1 is less than 2, the SELECT statement was executed and the result was True.

Example 2: If the expression is false, nothing will happen.

SELECT IF (3 > 9 ,'True', 'Commands completed successfully');

Output: The result of above query is

Commands completed successfully.

The command was finished successfully, according to SQL Server. Because nothing else was given, nothing else was returned.

SQL IIF Function

IIF is a function that returns a value after evaluating all of its arguments.

The IIF() function is essentially a shorter version of the IF... ELSE statement.

Example : Implement IIF Function

SELECT IIF( 3 > 4, 'Yes', 'No' );

Output: The result of above query will be



Here , we discussed 5 main Conrasts in if Vs iif function mentioned in below table:-

Type - Flow control statement. Type - Logical function.
To run, you specify a SQL statement or statement block. The actual value to return is specified.
The maximum number of nested levels is determined on the amount of memory available. Only up to a maximum of 10 levels can be nested.
The keyword ELSE is optional (i.e. you can choose whether or not to cater for false outcomes). Both a true and a false value are required (i.e. you must cater for false outcomes).
It does not share the CASEexpression's constraints. The IIF() function is really just a shortcut for writing a CASE expression. As a result, it suffers from the same constraints as the CASE expression.