The Secret of COALESCE vs IIF/IFF and CASE in SQL

Handling Null Values in SQL: A Closer Look

Let's imagine, like in school we imagined, lol! we have a table called "employees" with columns "name", "email", and "phone". We want to retrieve the first available contact information for each employee, whether it's their email or phone number. If both are null, we'll display a default message "No contact info".

Here's how each of the three approaches (`COALESCE`, IFF/`IIF`, and CASE) work behind the scenes, and which one is more optimized:

1. IFF/IIF (Less Optimized than COALESCE)

SELECT name, IFF(email IS NOT NULL, email, IFF(phone IS NOT NULL, phone, 'No contact info')) AS contact_info
FROM employees;        

Execution Process:

1. Retrieve a row from the "employees" table.

2. Evaluate the condition (`email IS NOT NULL`) in the outer IFF function.

- If true, return the value of email and move to the next row.

- If false, evaluate the next IFF function.

3. Evaluate the condition (`phone IS NOT NULL`) in the nested IFF function.

- If true, return the value of phone and move to the next row.

- If false, return the default value 'No contact info'.

4. Repeat steps 2-3 for each row in the table.

2. CASE (Least Optimized for Simple Scenarios)

SELECT name,
CASE
WHEN email IS NOT NULL THEN email
WHEN phone IS NOT NULL THEN phone
ELSE 'No contact info'
END AS contact_info
FROM employees;        

Execution Process:

1. Retrieve a row from the "employees" table.

2. Evaluate the first condition (`email IS NOT NULL`).

- If true, return the value of email and move to the next row.

- If false, move to the next condition.

3. Evaluate the second condition (`phone IS NOT NULL`).

- If true, return the value of phone and move to the next row.

- If false, move to the ELSE clause.

4. If both conditions are false, return the default value 'No contact info'.

5. Repeat steps 2-4 for each row in the table.

3. COALESCE

SELECT name, COALESCE(email, phone, 'No contact info') AS contact_info
FROM employees;        

Execution Process:

1. Retrieve a row from the "employees" table.

2. Evaluate the first expression (`email`) in the COALESCE function.

- If email is not null, return its value and move to the next row.

- If email is null, move to the next expression.

3. Evaluate the second expression (`phone`).

- If phone is not null, return its value and move to the next row.

- If phone is also null, move to the next expression.

4. If both email and phone are null, return the default value 'No contact info'.

5. Repeat steps 2-4 for each row in the table.

Which is more optimized?

IFF/IIF is:

- IFF/IIF requires more conditional checks compared to COALESCE for simple null handling scenarios.

- The nested IFF/IIF functions add more complexity to the execution plan.

CASE:

- CASE requires evaluating each condition sequentially until it finds a true condition or reaches the ELSE clause.

- This sequential evaluation can be less efficient than COALESCE or IFF/IIF for basic null handling scenarios.

- CASE statements are more powerful but also more complex and less optimized for simple use cases.

COALESCE :

- COALESCE has a simple execution plan and requires fewer conditional checks than IFF/IIF or CASE.

- It stops evaluating expressions as soon as it finds the first non-null value, making it efficient for basic null handling.

- Many database engines have specific optimizations for the COALESCE function.

Conclusion:

For simple null handling scenarios like this, COALESCE is generally the most optimized choice due to its straightforward execution plan and fewer conditional checks. IFF/IIF is less optimized than COALESCE but more optimized than CASE statements. CASE statements are the most flexible option but also the least optimized for basic null handling scenarios.

Still it's important to note that the actual performance can vary depending on the database engine, the complexity of the query, and the data size.

For more complex scenarios or critical performance requirements, it's always a good practice to benchmark and test the different approaches in the specific environment to determine the most optimal solution.

Please provide your feedback on my thoughts! Thanks

Shoaib Rahman