Rule: Do Not Specify Redundant ELSE NULL in a CASE WHEN Statement

Rule Code: ST01

Name: else_null

Overview

In SQL, the CASE WHEN statement allows conditional logic. By default, if no conditions are met in a CASE WHEN statement and no ELSE clause is provided, the result will be NULL. Therefore, explicitly specifying ELSE NULL is redundant and should be avoided. Omitting the redundant ELSE NULL simplifies the query and improves readability, while still achieving the same result.

Explanation

Anti-pattern: Specifying Redundant ELSE NULL

Including ELSE NULL at the end of a CASE WHEN statement adds unnecessary complexity to the query. Since the default behavior of CASE WHEN is to return NULL when none of the conditions are met, explicitly specifying ELSE NULL provides no additional value and makes the query harder to read.

Example of Redundant `ELSE NULL` in `CASE WHEN` (Anti-pattern):

select case
         when foo.value > 10 then
           'high'
         else
           null
       end value_description
  from foo;

In this example, the ELSE NULL clause is redundant because the CASE WHEN statement would return NULL by default if none of the conditions are satisfied.

Best Practice: Omit Redundant ELSE NULL

For better readability and simpler queries, omit the ELSE NULL clause. The CASE WHEN statement will naturally return NULL when no conditions are met, making the ELSE NULL unnecessary.

Refactored Example Without Redundant `ELSE NULL` (Best Practice):

select case
         when foo.value > 10 then
           'high'
       end value_description
  from foo;

In this refactored example, the ELSE NULL clause is omitted, simplifying the query without changing the behavior.

Conclusion

Avoiding the redundant ELSE NULL in CASE WHEN statements improves the readability and conciseness of SQL queries. The default behavior of returning NULL makes the ELSE NULL clause unnecessary, so omitting it results in cleaner, more maintainable code.

Groups:

• all

• structure