Comparing IF, IIF, and CASE
Tags: mssql, programming, learning, sql
Published: 2023-05-31 17:01:00

In this post, we'll compare and contrast the usage of `IF`, `IIF`, and `CASE` in MSSQL, which are essential for writing dynamic and conditional SQL statements.

## IF vs IIF

The `IF` statement and the `IIF` function serve different purposes and have slightly different syntaxes.

|                  | IF Statement                                                  | IIF Function                                                          |
|------------------|---------------------------------------------------------------|-----------------------------------------------------------------------|
| Use Case         | Used for conditional branching in T-SQL, allowing you to execute different code blocks depending on whether a condition is true or false. | Returns one of two values, depending on whether a condition is true or false. This function essentially performs the same function as an `IF...ELSE` statement, but in a more concise syntax. |
| Syntax           | `IF condition BEGIN... END`                                   | `IIF ( boolean_expression, true_value, false_value )`                 |
| Null handling    | The `IF` statement handles `NULL` according to the conditions specified within the statement. If a condition compares with `NULL`, it won't yield a `TRUE` or `FALSE` but a `NULL`. | If the `boolean_expression` is `NULL`, the `IIF` function returns `NULL`. |
| Scope            | Can be used in batch or stored procedures to dictate program flow. | Primarily used in a `SELECT` statement for row by row processing. However, it can also be used in a `WHERE` or `ORDER BY` clause. |
| Functionality    | Executes a set of statements based on the evaluation of a condition. | Evaluates a boolean expression and returns one of two values based on whether the expression resolves to `true` or `false`. |

## CASE vs IIF

The `CASE` expression and `IIF` function also serve different purposes.

|                  | CASE Expression                                               | IIF Function                                                          |
|------------------|---------------------------------------------------------------|-----------------------------------------------------------------------|
| Use Case         | Returns a result based on the evaluation of a set of conditions. It's like a series of `IF` statements in a single SQL command. | Returns one of two values, depending on whether a condition is true or false. |
| Syntax           | `CASE WHEN condition THEN result [WHEN...ELSE...] END`       | `IIF ( boolean_expression, true_value, false_value )`                 |
| Null handling    | Similar to `IF`, `CASE` handles `NULL` according to the conditions specified in the `CASE` statement. A comparison with `NULL` is neither `true` nor `false`, but `NULL`. | If the `boolean_expression` contains `NULL`, `IIF` returns `NULL`. |
| Scope            | Can be used in any statement or clause that allows a valid expression, such as `SELECT`, `UPDATE`, `DELETE`, or `SET` statement. | Primarily used in a `SELECT` statement for row by row processing. However, it can also be used in a `WHERE` or `ORDER BY` clause. |
| Functionality    | Evaluates a list of conditions and returns one of multiple possible result expressions. | Evaluates a boolean expression and returns one of two values based on whether the expression resolves to `true` or `false`. |
| Flexibility      | Can evaluate multiple conditions, making it more flexible for complex conditional logic. | Can only evaluate one condition, limiting its use to simpler conditional logic. |

It's important to remember that while `IIF` is functionally similar to using a `CASE` expression, `IIF` can only evaluate a single condition, while `CASE` can handle multiple

## Understanding Statements, Expressions, and Functions

In addition to the comparison between `IF`, `IIF`, and `CASE` discussed above, it's also important to understand the differences between statements, expressions, and functions in MSSQL.

### Statements

A SQL statement is a unit of execution in SQL. It performs a particular task or action in the database. Statements generally perform actions like creating or modifying database objects, inserting, updating, modifying or querying data, or controlling transactions and program flow.

For example, `SELECT`, `INSERT`, `UPDATE`, `DELETE`, and `CREATE` are all SQL statements. The `IF` in MSSQL is a control flow statement used for conditional branching.

### Expressions

An expression is a combination of symbols—like constants, variables, operators, and function calls—that the programming language interprets and computes to produce another value. Essentially, an expression is a piece of code that produces a value when it's evaluated.

In SQL, an expression can be as simple as a single number or string. It can also be more complex, like a mathematical operation that involves multiple columns of a table, or a function call that manipulates and transforms data.

The `CASE` in SQL is an expression because it produces a value based on the evaluation of conditions. Similarly, the `IIF` function is also an expression.

### Functions

Functions in SQL are a subtype of expressions because they also produce a value when called. However, functions encapsulate more complex operations that can be invoked using a specific syntax. Functions usually take in parameters, perform certain operations, and then return a result.

MSSQL provides many built-in functions to perform operations like string manipulation, date and time calculation, mathematical computations, and more. Functions can be used wherever expressions are allowed.

The `IIF` is a built-in function in MSSQL. It takes three arguments, evaluates the first one, and based on the result (true or false), it returns either the second or third argument.

In summary, while statements, expressions, and functions can often be used to perform similar tasks in SQL, they are used in different ways and have different roles in the SQL language. Understanding these differences can help you write more flexible, dynamic, and efficient SQL code.