ibitz Database Backup Software logodatabase backup software

screenshot of the ibitz Database Backup Software in action

Boolean logic instead of CASE WHEN clause

Here, here and here I have written about the CASE WHEN clause and its application in real life. We could use other functions with the same task or we could use boolean operators.

Programmers use this approach for decades. It's simple to use and often outperforms ternary operators and control flow operators (recently this tendency come to naught).

Let's look at an example. We want to select order's positions. If the position's status equals to 0 we want the price lower than $300. If position's stats is equals to 1 take positions with price lower than $400. With CASE WHEN clause, we will use this query:

SELECT *  FROM positions  WHERE price <    CASE status_id      WHEN 0 THEN 300      WHEN 1 THEN 400    END

If we want to use boolean logic instead we should follow next rules:

1) conditions for price and status_id combined by AND operator;

2) major condition blocks combined by OR operator.

In this way query will be look like this:

SELECT *  FROM positions  WHERE    status_id = 0 AND price < 300 OR    status_id = 1 AND price < 400

The advantage of boolean logic to a CASE WHEN clause is query execution speed. In case of boolean logic RDBMS could easily manage the query execution plan and indexes.

Of course, this trick work in both directions. If you have a lot of different conditions for price and haven't any time limits. Than for the sake of readability you can convert complex condition with OR and AND to the clear CASE version :)