Using CASE in WHERE Clause

sql

best-practices

logical-operators

sql-optimization

byAlex Kataev·Sep 26, 2024

In SQL, a CASE statement inside a WHERE clause provides a mechanism for conditional logic, adjusting the filter conditions dynamically.

This gives you a brief introduction:

SELECT * FROM employees
WHERE salary > CASE
                 WHEN experience < 5 THEN 50000
                 WHEN experience >= 5 THEN 75000
                 ELSE 60000
               END;

In this snippet, the CASE statement alters the salary threshold depending on experience, deciding which entries are selected.

OR To The Rescue

A simplified approach using OR may sometimes yield identical results to CASE, but with less head-scratching and considerably fewer key strokes:

SELECT auditors 
FROM coffee_break
WHERE (qty_donuts_consumed < 12 AND mood = 'Happy') OR qty_donuts_consumed >= 12; -- More donuts, not necessarily happier auditors!

This snippet ditches the CASE in favour of a clearer, sleeker and noticeably less verbose set of logical operators.

Note on the Use of CASE

The CASE with values, not conditions

Beware of a common pitfall; a CASE statement produces values, not conditions. Confusing the two can spell disaster, raining down errors or, worse still, wrong results. Test, rinse, repeat!

Syntax variations across systems

Each database system, Oracle, MySQL, you name it, has its own syntax quirks. Master the art of refactoring, optimizing for your specific database, transforming complex CASE expressions into delightful, streamlined SQL poetry.

Testing and improving readability

Make sure to rigorously test and review your logic. And remember: aliases are your friends, offering you and others a lifeline in untangling complex queries. As they say, "no one ever got fired for writing readable code".

All About Logic

Direct is beautiful

With just a little creativity, you can replace a hairy CASE structure with a direct, dazzling condition, all without losing clarity or brevity:

SELECT * FROM jokes
WHERE category = 'safe' AND (
    (rating = 'funny' AND audience_age IS NULL) OR-- Joke's so funny, age doesn't matter
    (rating <> 'funny') -- Or it's just safe, but not necessarily funny
);

AND is your secret ingredient

Going AND-heavy will keep your WHERE clause looking shipshape and overstuffed CASE expressions at bay, all while keeping your SQL melodies singing beautifully.

Simplicity via IN

IN can be a great ally, defeating armies of OR conditions. Just make sure it stays within the command of the query's logic:

SELECT * FROM customers
WHERE region IN ('East', 'West', 'North', 'South'); -- Who needs a compass when you have SQL?

Wise use of the excluded middle

Some conditions cover all possible values, rendering CASE statements unnecessary, a truth universally acknowledged by those in possession of this SQL wisdom:

SELECT product_id, stock_level
FROM inventory
WHERE stock_level = 0 OR stock_level > 0; -- Stock exists or doesn't. Schrödinger's warehouse?

These shortcuts return all products with no regard to stock level. You're welcome, fellow SQL-ers!

References

explain-codes / Sql / Using CASE in WHERE Clause

Linked

If statement in select (ORACLE)



How to compare strings in SQL ignoring case?



'if' in 'SELECT' statement - How to Manipulate Output Based on Column Values



Mysql SELECT IF statement with OR



Insert Data Into Temp Table with Query



How do I use properly CASE..WHEN in MySQL



Best way to check for "empty or null value"



OR To The Rescue Note on the Use of CASE All About Logic References