CASE expression in PostgreSQL - Step-by-Step Execution

Concept Flow - CASE expression in PostgreSQL

Start Query

↓

Evaluate CASE

↓

Check WHEN condition 1

|Yes↓

Return THEN result 1

No↓

Check WHEN condition 2

|Yes↓

Return THEN result 2

No↓

Check more WHEN conditions or ELSE

↓

Return ELSE result or NULL

↓

End Query with result

The CASE expression checks each WHEN condition in order and returns the THEN result of the first true condition. If none match, it returns ELSE or NULL.

Execution Sample

PostgreSQL

SELECT
  product_name,
  CASE
    WHEN price < 20 THEN 'Cheap'
    WHEN price BETWEEN 20 AND 50 THEN 'Moderate'
    ELSE 'Expensive'
  END AS price_category
FROM products;

This query classifies products into price categories using CASE based on their price.

Execution Table

Step	product_name	price	Check WHEN price < 20	Check WHEN price BETWEEN 20 AND 50	Result returned
1	Pen	10	True	Skipped	Cheap
2	Notebook	30	False	True	Moderate
3	Backpack	60	False	False	Expensive
4	Marker	20	False	True	Moderate
5	Eraser	5	True	Skipped	Cheap

💡 All products evaluated; CASE returns first matching THEN result or ELSE if none match.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5
price	N/A	10	30	60	20	5
price_category	N/A	Cheap	Moderate	Expensive	Moderate	Cheap

Key Moments - 2 Insights

Why does the CASE expression return 'Cheap' for price 10 and skip the second WHEN?

Because the first WHEN condition (price < 20) is true, CASE returns its THEN result immediately and does not check further WHEN conditions, as shown in execution_table row 1.

What happens if none of the WHEN conditions are true and there is no ELSE?

CASE returns NULL if no WHEN condition matches and ELSE is not provided. This is not shown in the sample but is important to remember.

Visual Quiz - 3 Questions

Look at the execution_table, what is the price_category for the product 'Backpack' at step 3?

A Moderate

B Expensive

C Cheap

D NULL

💡 Hint

Check the 'Result returned' column in row 3 of the execution_table.

At which step does the CASE expression return 'Moderate' because price is exactly 20?

A Step 1

B Step 2

C Step 4

D Step 5

💡 Hint

Look at the 'price' and 'Result returned' columns in the execution_table for step 4.

If the ELSE clause was removed, what would be the result for a product priced 60?

A NULL

B 'Expensive'

C 'Moderate'

D 'Cheap'

💡 Hint

Refer to the key_moments explanation about CASE behavior without ELSE.

Concept Snapshot

CASE expression syntax:
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Checks conditions in order, returns first true THEN result.
If none true, returns ELSE or NULL if ELSE missing.

Full Transcript

The CASE expression in PostgreSQL evaluates conditions one by one. It returns the result of the first condition that is true. If no conditions match, it returns the ELSE result or NULL if ELSE is not provided. For example, a query classifies products by price into 'Cheap', 'Moderate', or 'Expensive'. Each product's price is checked against WHEN conditions in order. Once a condition matches, CASE returns its THEN result and stops checking further. This behavior is shown step-by-step in the execution table. Beginners often wonder why CASE stops after the first true condition; this is because CASE is designed to return immediately on the first match. Also, if no conditions match and ELSE is missing, the result is NULL. Understanding this flow helps write clear and predictable CASE expressions.