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

True

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.

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:-

IF	IIF
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 `CASE`expression'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.