T-SQL CASE Clause: How to Specify WHEN NULL

The CASE statement in T-SQL (Transact-SQL) is a powerful conditional expression that allows you to execute different actions based on specific conditions. It’s commonly used in queries to return values based on logic that involves comparisons. When working with NULL values in T-SQL, the CASE statement becomes particularly useful.

This article will explain how to handle NULL values in the CASE clause, including how to specify WHEN NULL and best practices for managing NULL comparisons in your SQL queries.

What is the CASE Statement in T-SQL?

The CASE statement is used for conditional logic in T-SQL queries. It can be thought of as an IF-THEN-ELSE logic that evaluates a set of conditions and returns a corresponding result. There are two types of CASE expressions in T-SQL:

1. Simple CASE Expression: Evaluates a single expression and compares it to a set of values.
   sql

   CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE result3
END

2. Searched CASE Expression: Evaluates multiple conditions (boolean expressions) and returns a result based on the first condition that is true.
   sql

   CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
END


Handling NULL in the CASE Expression

In SQL, NULL represents an unknown or missing value. Special care must be taken when comparing NULL because NULL is not equal to anything, not even NULL itself. For this reason, typical equality checks do not work as expected with NULL. To handle NULL values properly within a CASE statement, you need to use IS NULL or IS NOT NULL rather than the standard equality operators.

1. Using WHEN NULL in a CASE Statement

In the CASE expression, when you want to check if a value is NULL, you cannot use the typical equality operator (=). Instead, you should use IS NULL or IS NOT NULL. Here’s how you can handle NULL values:

Example 1: Checking if a Column is NULL

If you want to check if a column value is NULL and return a specific result, you can use a CASE expression with IS NULL.

In this example:

• If the Salary column contains NULL, the result will be ‘No Salary Information’.
• Otherwise, the actual salary value will be returned, converted to a string.

Example 2: Checking if a Value is NOT NULL

Similarly, if you want to check whether a value is not NULL, you can use IS NOT NULL.

In this case:

• If the Salary column is not NULL, the result will be ‘Has Salary’.
• If it is NULL, the result will be ‘No Salary’.

2. Using CASE with Multiple Conditions Involving NULL

You can also use a CASE expression with multiple conditions to perform more complex logic involving NULL values.

Example 3: Multiple Conditions with NULL Check

In this query:

• If the Bonus column is NULL, the result will be ‘Bonus Data Missing’.
• If the Bonus is greater than 1000, the result will be ‘High Bonus’.
• If neither of the above conditions is true, the result will be ‘Standard Bonus’.

3. CASE with NULL and Aggregates

You can use the CASE statement to handle NULL values when performing aggregate functions such as COUNT, SUM, or AVG. Since NULL values are typically ignored by aggregate functions, you may need to handle them explicitly.

Example 4: Counting NULL and Non-NULL Values

To count NULL values in a column, you can use CASE in conjunction with COUNT.

Here:

• The NullSalaryCount counts the number of NULL values in the Salary column.
• The NonNullSalaryCount counts the number of non-NULL values in the Salary column.

Best Practices for Working with NULL in CASE

1. Avoid Using = with NULL: Do not use the equality operator (=) to compare a column with NULL. Always use IS NULL or IS NOT NULL for NULL comparisons.
2. Use ELSE for Default Behavior: It’s good practice to always include an ELSE clause in your CASE statements, especially when handling NULL. This ensures that your query handles all possible cases.
3. Check for Both NULL and Non-NULL Values: When designing CASE statements that involve NULL values, it’s common to check for both NULL and non-NULL conditions. This ensures that all potential cases are accounted for.

The CASE statement in T-SQL is a valuable tool for implementing conditional logic within queries, and it is especially useful for handling NULL values. Since NULL is treated uniquely in SQL, using IS NULL and IS NOT NULL in your CASE expressions is essential for accurate results. By understanding how to properly specify WHEN NULL in CASE clauses, you can effectively handle missing or unknown data and ensure that your queries return meaningful results.