OR and CASE expressions

Concepts covered: Conditional Logic

OR widens the filter

AND keeps a row only when every condition is true. OR keeps a row when at least one condition is true, so adding an OR condition can only grow the result and never shrink it. For each row the engine evaluates every condition to TRUE, FALSE or NULL, combines those values with the table below, and keeps the row when the final value is TRUE.

A TRUE on either side settles the question, which is why TRUE OR NULL is TRUE. FALSE OR NULL stays NULL, and WHERE drops a row whose combined value is NULL as readily as one whose value is FALSE. The orders table below has 7 rows, one of them with a NULL amount, and the query keeps refunded orders plus anything over 500.

SELECT
  order_id,
  customer,
  status,
  amount
FROM orders
WHERE status = 'refunded'
  OR amount > 500

Three rows survive. Order 1001 passes on amount alone, 1002 passes on status, and 1004 passes on both, which still counts once. Order 1006 sits at exactly 500, and 500 > 500 is FALSE, so it drops. Order 1007 carries a NULL amount: the first condition returns FALSE, the second returns NULL, and FALSE OR NULL is NULL, so the row is gone without any warning.

When every branch of an OR compares the same column with =, IN says the same thing in fewer characters: status IN ('refunded', 'pending') is status = 'refunded' OR status = 'pending' with the repetition removed. OR earns its place when the branches test different columns, like status and amount above, or use different operators.

CASE picks a value per row

A CASE expression walks a list of WHEN conditions from top to bottom and returns the THEN value of the first one that is TRUE. When none is TRUE it returns the ELSE value, and when there is no ELSE it returns NULL. CASE is an expression, so it can stand anywhere a column or literal can: in the SELECT list, in ORDER BY, and inside WHERE. The animation labels each order with a priority tier.

SELECT
  order_id,
  amount,
  CASE
    WHEN amount >= 500 THEN 'high'
    WHEN amount >= 100 THEN 'medium'
    ELSE 'low'
  END AS priority
FROM orders

Order 1004 has an amount of exactly 500. The first branch tests amount >= 500, which is TRUE, so the row receives 'high' and the second branch is never evaluated for it. Order 1003 at 240 fails the first test, passes amount >= 100, and receives 'medium'. Orders 1002 and 1005 fail both tests and fall through to the ELSE.

Branch order carries the logic. Every amount that satisfies amount >= 500 also satisfies amount >= 100, so with the 'medium' branch written first, 720 and 500 would both be labelled 'medium' and 'high' would never appear. Write the narrowest condition first, or make each condition exclusive with explicit bounds such as amount >= 100 AND amount < 500, which then work in any order.

Two shapes of CASE

The form above is the searched CASE: each WHEN holds a full condition. The simple CASE names one expression once, CASE status WHEN 'refunded' THEN 1, and tests it against each WHEN value with =. The simple form reads cleanly for a fixed list of labels but cannot express a range, and since NULL = NULL is never TRUE, it can never match a NULL. The query below uses it to sort orders by how urgently they need a look.

SELECT
  order_id,
  status,
  amount
FROM orders
ORDER BY
  CASE status
    WHEN 'refunded' THEN 1
    WHEN 'pending' THEN 2
    ELSE 3
  END,
  order_id

Refunded orders surface first, then the pending one, then the rest, with order_id breaking ties inside each group. Plain ORDER BY status would sort alphabetically as pending, refunded, shipped, putting the orders that need a refund check in the middle of the list. CASE in ORDER BY gives you any sort you can describe with conditions.

This CASE labels each order by amount. Which label does order 1004, with an amount of 500, receive?

CASE
  WHEN amount >= 100 THEN 'medium'
  WHEN amount >= 500 THEN 'high'
  ELSE 'low'
END

• 'high', because 500 satisfies the condition written for it
• 'medium', because the first TRUE branch wins
• 'low', because 500 falls on a boundary
• NULL, because the branches overlap

About this lesson

This section is part of the Filtering: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.