Unnecessary CASE statement SQL-L043
Style
Minor
CASE statements are used in SQL to perform conditional logic. They return a value based on whether a condition is true or false. The WHEN condition within the COALESCE function can be used to achieve the same result in a more concise and idiomatic way.
For example, instead of writing:
SELECT CASE
WHEN column1 IS NULL THEN column2
ELSE column1
END
FROM table;
You can write:
SELECT COALESCE(column1, column2)
FROM table;
Both statements return the value of column1 if it is not NULL, otherwise, they return the value of column2.
Bad practice
SELECT
CASE
WHEN fab > 0 THEN true
ELSE false
END AS is_fab
FROM fancy_table;
-- This rule can also simplify CASE statements
-- that aim to fill NULL values.
SELECT
CASE
WHEN fab is null THEN 0
ELSE fab
END AS fab_clean
FROM fancy_table;
-- This also covers where the case statement
-- replaces NULL values with NULL values.
SELECT
CASE
WHEN fab is null THEN null
ELSE fab
END AS fab_clean
FROM fancy_table
Recommended
SELECT
COALESCE(fab > 0, false) AS is_fab
FROM fancy_table;
-- To fill NULL values.
select
COALESCE(fab, 0) AS fab_clean
FROM fancy_table;
-- NULL filling NULL.
SELECT fab AS fab_clean
FROM fancy_table