CASE Expressions

The examples are mostly MySQL-oriented. For a portable NULL check, prefer score IS NULL rather than MySQL's <=> operator or a sentinel value through COALESCE.

Introduction

CASE is SQL’s way of writing if/else logic directly inside a query.

Think about it like this. In any programming language, you write conditions to decide what should happen next. SQL is no different, except instead of controlling program flow, CASE controls what value gets returned in a query.

What is it? CASE lets you check one or more conditions, in order, and return a value based on whichever condition matches first. If nothing matches, it falls back to an ELSE value, or NULL if no ELSE is given.

Why do we need it? Raw data rarely comes labeled the way a business needs it. A products table has prices, not price categories. A sales table has numbers, not pass/fail verdicts. CASE is the tool that turns raw values into business-friendly labels, without needing to pull data out of SQL and process it somewhere else.

Real-world analogy Think of a doorman checking IDs at three different counters based on age. If someone is under 18, they go to counter A. If they are between 18 and 60, they go to counter B. If they are above 60, they go to counter C. The doorman checks conditions top to bottom and sends the person to the first matching counter. CASE works exactly like that doorman.

Business use case A common example is customer segmentation. A business might want to label customers as “New,” “Regular,” or “VIP” based on their order count. Instead of writing separate queries for each label, one CASE expression handles all of it in a single pass.

CASE is an expression, not a statement. That means it returns a value and can be used anywhere a column can be used, inside SELECT, inside WHERE, inside ORDER BY, and even inside aggregate functions.

Dataset Used

This topic uses a few small tables to demonstrate different situations:

• products — product_id, name, price (some prices are NULL)
• rules — rule_id, value (some values are NULL)
• users — user_id, username (some usernames are NULL, some are empty strings)
• employees — emp_id, salary, performance_score (some scores are NULL)
• nodes — node_id, parent_id (used to represent a tree structure)
• sales — item, quantity (used for conditional aggregation)

Each table is small and simple, built specifically to show how CASE behaves with normal values, missing values, and edge cases like empty strings.

Syntax

There are two forms of CASE.

Searched CASE (most commonly used):

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE fallback_value
END

Simple CASE (used for direct equality matching):

CASE column_name
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE fallback_value
END

Every CASE must end with the keyword END. ELSE is optional, but if you skip it and no WHEN condition matches, the result is NULL.

A quick way to remember when to use which form:

Here is a minimal example showing CASE with a plain variable, before we move to full tables:

SET @temp_age = 19;

SELECT
    CASE
        WHEN @temp_age >= 18 THEN 'Adult'
        ELSE 'Not an Adult'
    END AS result;

This query uses a user-defined variable and a CASE expression to apply conditional logic. If @temp_age is 18 or above, it returns ‘Adult’, otherwise it returns ‘Not an Adult’. This is the simplest possible form of CASE, before adding tables or multiple conditions into the picture.

Mental Model

Picture CASE as a checklist that SQL reads from top to bottom, row by row.

For every single row, SQL asks: “Does this row match condition 1? If yes, stop here and return this value.” “If not, does it match condition 2? If yes, stop here.” “If none of the conditions match, use the ELSE value, or return NULL.”

This checklist runs independently for every row in the table. It never looks at multiple rows at once, it only evaluates one row at a time.

How SQL Executes It

Internally, CASE behaves like a sequential filter, not a parallel check.

1. SQL takes one row.
2. It evaluates the first WHEN condition.
3. If TRUE, it immediately returns the THEN value and skips every condition after it.
4. If FALSE or UNKNOWN, it moves to the next WHEN condition.
5. This continues until a match is found or all conditions are exhausted.
6. If nothing matches, ELSE is returned, or NULL if ELSE is missing.

This is why order matters. If you write a broad condition before a specific one, the specific one will never be reached, because SQL stops at the first match.

Example 1 — Price-Based Product Classification

Problem Classify each product into a price category: NULL price should show as “Missing Price,” price 20 or below is “Budget,” price between 21 and 100 is “Mid Range,” and anything above 100 is “Premium.”

Assumed table: products

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  price      INT NULL
);

INSERT INTO products (product_id, name, price) VALUES
  (1,  'Laptop Sleeve',        18),
  (2,  'Wireless Mouse',       32),
  (3,  'Keyboard',             55),
  (4,  'Monitor',              150),
  (5,  'USB Cable',            8),
  (6,  'Webcam',               60),
  (7,  'HDMI Cable',           20),
  (8,  'Mechanical Keyboard',  120),
  (9,  'Sticker Pack',         0),
  (10, 'Gift Card',            19),
  (11, 'Mystery Box',          NULL),
  (12, 'Refurb Monitor',       61);

SQL Query

SELECT
  product_id,
  name,
  price,
  CASE
    WHEN price IS NULL THEN 'Missing Price'
    WHEN price <= 20 THEN 'Budget'
    WHEN price > 20 AND price <= 100 THEN 'Mid Range'
    WHEN price > 100 THEN 'Premium'
  END AS price_category
FROM products;

Explanation SQL checks each product row by row. It first checks if price is NULL, since NULL needs to be handled before any numeric comparison (comparing NULL with numbers never returns TRUE). Then it checks the numeric ranges in increasing order.

Output

Why it works Placing the NULL check first prevents wrong results. If the NULL check came after the range checks, the Mystery Box row would silently return NULL for price_category instead of the intended label, since NULL <= 20 evaluates to UNKNOWN, not TRUE.

Example 2 — Boolean Evaluation with CASE

Problem CASE can evaluate conditions and return explicit results such as PASS or FAIL. For each row in a rules table, decide: if the value is NULL, return “Missing.” Else if the value is 40 or above, return “PASS.” Otherwise, return “FAIL.”

Assumed table: rules

CREATE TABLE rules (
  rule_id INT PRIMARY KEY,
  value   INT
);

INSERT INTO rules (rule_id, value) VALUES
  (1, 75),
  (2, 40),
  (3, 60),
  (4, NULL),
  (5, 95),
  (6, 59),
  (7, 0),
  (8, 61);

SQL Query

SELECT
  rule_id,
  value,
  CASE
    WHEN value IS NULL THEN 'Missing'
    WHEN value >= 40 THEN 'PASS'
    ELSE 'FAIL'
  END AS verdict
FROM rules;

Explanation This is a straightforward threshold check. The NULL check comes first so that missing values are never mistakenly evaluated against the numeric threshold. Once NULLs are handled, the remaining rows are split into PASS or FAIL using a single boolean condition, with ELSE catching everything below the threshold.

Output

Why it works Notice that rule_id 7 has a value of 0, and it correctly returns “FAIL,” not “Missing.” This is the key distinction CASE is built to handle: 0 is a real, valid value, while NULL represents the total absence of a value. Because the NULL check is written as its own WHEN condition using IS NULL, 0 is never confused with missing data.

Example 3 — Understanding NULL Comparisons

Problem Before writing more complex CASE logic involving NULL, it’s worth seeing directly how SQL evaluates comparisons involving NULL. In SQL, comparisons don’t result only in TRUE or FALSE, they can also return UNKNOWN.

SQL Query

SELECT
  NULL = NULL     AS eq_null_null,     -- NULL (unknown)
  NULL <=> NULL   AS nullsafe_eq,      -- 1 (true)
  NULL = 5        AS eq_null_5,        -- NULL (unknown)
  NULL <=> 5      AS nullsafe_eq_5,    -- 0 (false)
  (NULL >= 60)    AS ge_null_60;       -- NULL (unknown)

Explanation Each column in this query demonstrates a different NULL comparison:

• NULL = NULL does not return TRUE, the result is UNKNOWN (shown as NULL)
• NULL <=> NULL uses MySQL’s NULL-safe equality operator, which correctly returns TRUE (1) because it treats NULL as a comparable value instead of an unknown one
• NULL = 5 is UNKNOWN, since you cannot compare an unknown value to a known one using standard equality
• NULL <=> 5 returns FALSE (0), because the NULL-safe operator can definitively say NULL is not equal to 5
• NULL >= 60 is UNKNOWN, for the same reason as the first comparison

Output

Why it works This example is not meant to solve a business problem, it exists to build the mental model needed for every other example in this document. IS NULL is the correct way to check for NULL values in standard SQL. In MySQL, <=> is a NULL-safe equality operator that safely compares values even when NULL is involved, but it is MySQL-specific and does not exist in every database.

Example 4 — Normalizing Usernames (NULL vs Empty String)

Problem For each row, check the username column and return “Not Filled” when username IS NULL, “Empty” when username is an empty string '', and the actual username otherwise. This is a very common data cleaning step when datasets contain both NULL values and empty strings.

Assumed table: users

CREATE TABLE users (
  user_id  INT PRIMARY KEY,
  username VARCHAR(60) NULL,
  INDEX idx_users_username (username)
);

INSERT INTO users (user_id, username) VALUES
  (1, 'Pro_User'),
  (2, 'guest123'),
  (3, 'PRO_member'),
  (4, 'ProXUser'),
  (5, 'pro_user99'),
  (6, ' Pro_User '),
  (7, ''),
  (8, NULL);

SQL Query

SELECT
  user_id,
  username,
  CASE
    WHEN username <=> NULL THEN 'Not Filled'
    WHEN username = '' THEN 'Empty'
    ELSE username
  END AS username_display
FROM users;

Explanation This query uses the NULL-safe equality operator (<=>) to check for NULL, since a plain = NULL comparison would never work, as shown in Example 3. Once NULL is ruled out, the next check looks specifically for an empty string, which is a completely different case from NULL even though both can look “blank” in a spreadsheet. Anything that is neither NULL nor empty falls through to ELSE and returns the actual username.

Output

Why it works Row 8 (NULL) and Row 7 (empty string) return two different labels, which is exactly the point of this example. If this query had only checked username = '' without a separate NULL check, row 8 would have incorrectly fallen through to ELSE and displayed nothing meaningful, since NULL = '' also evaluates to UNKNOWN, not TRUE.

Example 5 — Bonus Calculation Using CASE and COALESCE

Problem Compute a bonus for each employee with a fallback for missing performance data. If performance_score is NULL, bonus is 0. If score is 90 or above, bonus is 20% of salary. If score is 75 or above, bonus is 10% of salary. Otherwise, bonus is 5% of salary.

Assumed table: employees

CREATE TABLE employees (
  emp_id            INT PRIMARY KEY,
  salary            INT NOT NULL,
  performance_score INT NULL
);

INSERT INTO employees (emp_id, salary, performance_score) VALUES
  (101, 50000, 90),
  (102, 45000, NULL),
  (103, 60000, 74),
  (104, 70000, 75),
  (105, 80000, 89),
  (106, 0,     92),
  (107, 52000, 100),
  (108, 50000, 0),
  (109, 45000, 85),
  (110, 40000, NULL);

SQL Query

SELECT
  emp_id,
  salary,
  performance_score,
  CASE
    WHEN COALESCE(performance_score, -1) = -1 THEN 0
    WHEN performance_score >= 90 THEN salary * 0.20
    WHEN performance_score >= 75 THEN salary * 0.10
    ELSE salary * 0.05
  END AS bonus_amount
FROM employees
ORDER BY emp_id;

Explanation COALESCE is used here to safely handle missing scores. Instead of writing a separate IS NULL check, COALESCE replaces NULL with -1, a value that can never occur naturally, and then checks against it. This is a common pattern for combining NULL handling with CASE logic in one clean step.

Output

Why it works The row with a 0 performance score (emp_id 108) still gets a bonus, because 0 is a valid number, not a missing value. Only rows where the score is genuinely NULL get zero bonus. This distinction is exactly why the NULL check needs to be separate from the numeric range checks. Note also that emp_id 106 has a salary of 0, so even with a high performance score, 20% of 0 is still 0, which is correct arithmetic, not a bug.

Example 6 — Node Classification in a Tree Structure

Problem Classify each node in a hierarchy as ROOT, INTERNAL, or LEAF. A node is ROOT if it has no parent. It is INTERNAL if it has at least one child. It is LEAF if it has no children.

Assumed table: nodes

CREATE TABLE nodes (
  node_id   INT PRIMARY KEY,
  parent_id INT NULL,
  INDEX idx_nodes_parent_id (parent_id)
);

INSERT INTO nodes (node_id, parent_id) VALUES
  (1,  NULL),
  (2,  1),
  (3,  2),
  (4,  1),
  (5,  4),
  (6,  4),
  (7,  6),
  (8,  NULL),
  (9,  8),
  (10, 8),
  (11, 10),
  (12, 10);

SQL Query

SELECT
    node_id,
    parent_id,
    CASE
        WHEN parent_id IS NULL THEN 'ROOT'
        WHEN NOT EXISTS (
            SELECT 1
            FROM nodes n2
            WHERE n1.node_id = n2.parent_id
        ) THEN 'LEAF'
        ELSE 'INTERNAL'
    END AS node_type
FROM nodes n1;

Explanation This example shows that CASE conditions are not limited to simple comparisons, they can contain entire subqueries. The first WHEN checks if a node has no parent, marking it ROOT. The second WHEN uses a correlated subquery (NOT EXISTS) to check whether any other row in the table lists the current node as its parent. If no such row exists, the node has no children and is a LEAF. Anything that is neither ROOT nor LEAF must have both a parent and at least one child, so it falls through to INTERNAL.

Output

Why it works Because the correlated subquery runs once per row (it references n1.node_id from the outer query), each node gets checked individually against the full table to see if it appears anywhere as a parent_id. This is a good example of CASE being combined with a subquery to answer a structural question, not just a value-based one.

Example 7 — Conditional Aggregation

Problem CASE can be used inside aggregate functions to split one metric into multiple labeled columns. Using a sales table, calculate the total quantity sold for apples and the total quantity sold for oranges, side by side, in a single row.

Assumed table: sales

CREATE TABLE sales (
  item     VARCHAR(30) NOT NULL,
  quantity INT NULL
);

INSERT INTO sales (item, quantity) VALUES
  ('apple',  10),
  ('orange', 7),
  ('apple',  5),
  ('banana', 3),
  ('mango',  2),
  ('apple',  NULL),
  ('orange', NULL);

SQL Query

SELECT
  SUM(CASE WHEN item = 'apple'  THEN COALESCE(quantity, 0) ELSE 0 END) AS apples_sold,
  SUM(CASE WHEN item = 'orange' THEN COALESCE(quantity, 0) ELSE 0 END) AS oranges_sold
FROM sales;

Explanation Here, CASE is placed inside SUM instead of SELECT directly. For each row, the CASE checks if the item matches the target fruit. If it does, it contributes its quantity (with COALESCE guarding against NULL quantities) to the running total. If it doesn’t match, it contributes 0, meaning it has no effect on that particular sum. This pattern effectively creates a filtered sum without needing a WHERE clause or multiple queries.

Output

Why it works Without conditional aggregation, getting these two totals side by side would normally require two separate queries or a GROUP BY with a pivot-style transformation. COALESCE inside the CASE ensures that rows with a NULL quantity (like the apple and orange rows with NULL) don’t break the SUM or get treated as contributing an unknown amount, they simply add 0.

Deep Dive

Internal behavior CASE evaluates conditions strictly in order and stops at the first TRUE condition. It never checks conditions after a match is found, even if a later condition also happens to be true. This makes CASE similar to an if/else-if chain in general-purpose programming languages, not a switch statement that checks every branch.

NULL handling This is the part that trips up most beginners. In SQL, comparisons involving NULL do not return TRUE or FALSE, they return UNKNOWN. So price = NULL will never be TRUE, even when price actually is NULL. This is why IS NULL and IS NOT NULL must be used to check for NULL, instead of the equality operator.

Some databases support a NULL-safe equality operator (<=> in MySQL) that returns TRUE or FALSE even when comparing NULLs, but this behavior is database-specific and should be used carefully, since not every database supports it, and some use different syntax (IS NOT DISTINCT FROM in PostgreSQL, for example).

Empty strings are also a common trap. An empty string '' is not the same as NULL. A username column with an empty string is not “missing,” it technically has a value, just an empty one. These need separate WHEN conditions, as shown in Example 4.

Performance considerations CASE itself is lightweight and does not add meaningful overhead for row-by-row evaluation. However, when CASE is used inside WHERE clauses or JOIN conditions, it can sometimes prevent the database from using indexes efficiently, because the optimizer may not be able to simplify the condition the way it can with plain column comparisons. When CASE is only used in SELECT to generate a label, this concern does not apply.

When CASE includes a correlated subquery, as in the node classification example, performance depends heavily on the size of the table and whether the referenced column is indexed. Indexing parent_id, for instance, makes the NOT EXISTS check much faster on large tables.

Database behavior Most relational databases (MySQL, PostgreSQL, SQL Server, Oracle) follow the same CASE evaluation rules defined in the SQL standard. Minor differences exist in NULL-safe operators and some function names, but the core CASE logic behaves identically across databases.

Real-world Use Cases

• E-commerce: Categorizing products into price tiers, similar to Example 1
• HR systems: Calculating bonuses or ratings based on performance thresholds, similar to Example 5
• Data cleaning pipelines: Normalizing messy fields like usernames, statuses, or categories that contain NULLs, empty strings, or inconsistent casing, similar to Example 4
• Reporting dashboards: Conditional aggregation, splitting one metric into multiple columns without needing separate queries, similar to Example 7
• Hierarchical systems: Classifying records in a tree structure as root, internal, or leaf nodes, often used in org charts, comment threads, or category trees, similar to Example 6
• Validation and QA systems: Marking records as PASS, FAIL, or Missing based on threshold rules, similar to Example 2

Review Tips

Interviewers commonly test CASE alongside these ideas:

• “Write a query to bucket users into age groups.” — Tests basic searched CASE.
• “Why did my CASE expression return NULL when I expected a category?” — Tests understanding of ELSE being optional and NULL comparison behavior.
• “How would you calculate the count of two different categories in a single row?” — Tests conditional aggregation using SUM(CASE WHEN …).
• “What is the difference between NULL = NULL and IS NULL?” — Tests understanding of UNKNOWN in three-valued logic.
• “How would you distinguish between a NULL value and an empty string in a text column?” — Tests awareness that NULL and ’’ are not the same thing.
• “Rewrite this nested CASE using a simpler approach.” — Tests whether you understand when Simple CASE or COALESCE is cleaner than a full Searched CASE.

A strong answer always mentions that CASE stops at the first matching condition, and that NULL needs special handling because standard comparison operators do not work with it.

Common Mistakes to Avoid

Mistake 1: Checking NULL with = instead of IS NULL

Why it happens: Beginners assume NULL behaves like a normal value in comparisons.

Wrong:

CASE WHEN price = NULL THEN 'Missing Price' ELSE 'Has Price' END

Correct:

CASE WHEN price IS NULL THEN 'Missing Price' ELSE 'Has Price' END

Mistake 2: Writing broad conditions before specific ones

Why it happens: Order in CASE is often assumed to not matter, but it does.

Wrong:

CASE
  WHEN price > 20 THEN 'Mid Range'
  WHEN price > 100 THEN 'Premium'
END

Here, “Premium” is never reached because anything above 100 already satisfies price > 20 first.

Correct:

CASE
  WHEN price > 100 THEN 'Premium'
  WHEN price > 20 THEN 'Mid Range'
END

Mistake 3: Forgetting ELSE and being surprised by NULL results

Why it happens: Developers assume unmatched rows will show something meaningful by default.

Wrong:

CASE WHEN status = 'A' THEN 'Active' END

Any row where status is not ‘A’ silently becomes NULL.

Correct:

CASE WHEN status = 'A' THEN 'Active' ELSE 'Inactive' END

Mistake 4: Treating empty strings the same as NULL

Why it happens: Both look “empty” but they are stored and evaluated differently.

Wrong:

CASE WHEN username IS NULL THEN 'Empty' END

This misses rows where username is an actual empty string ''.

Correct:

CASE
  WHEN username IS NULL THEN 'Not Filled'
  WHEN username = '' THEN 'Empty'
  ELSE username
END

Mistake 5: Using CASE when a simpler function would do the job

Why it happens: Overusing CASE for straightforward NULL replacement.

Wrong:

CASE WHEN performance_score IS NULL THEN 0 ELSE performance_score END

Correct:

COALESCE(performance_score, 0)

COALESCE is cleaner and more readable for simple fallback logic. CASE should be reserved for actual conditional branching.

Mistake 6: Forgetting that 0 is not the same as NULL

Why it happens: Both can look like “nothing” at a glance, especially when scanning a table quickly.

Wrong:

CASE WHEN performance_score = 0 THEN 0 WHEN performance_score IS NULL THEN 0 ELSE ... END

Combining these into one condition without understanding the difference can hide real data issues, since a score of 0 might mean “evaluated and failed,” while NULL means “not evaluated at all.” These represent very different business situations and usually need to stay separate in reporting, even if they end up with the same output value.

Correct: Keep the WHEN conditions for 0 and NULL separate, as shown in Example 5, even if they currently produce the same result, since business rules can change independently for each case.

Further Questions

• “What would happen if you removed the ELSE clause from this query?”
• “How would you rewrite this CASE expression using COALESCE or IFNULL where possible?”
• “Can you use a CASE expression inside a WHERE clause? What are the tradeoffs?”
• “How would this query behave differently in PostgreSQL versus MySQL, specifically around NULL-safe comparisons?”
• “How would you optimize the node classification query if the nodes table had a million rows?”

Similar Problems

• COALESCE and IFNULL for simpler NULL fallback logic
• IF() function (MySQL-specific) as a shorthand for two-branch CASE expressions
• Conditional aggregation combined with GROUP BY for per-group breakdowns
• Recursive CTEs, which are often the next step after node classification when you need full ancestor/descendant paths, not just one level

Key SQL Concepts Learned

Key Takeaways

• CASE is SQL’s if/else logic, used as an expression inside a query, not a standalone statement
• SQL checks WHEN conditions top to bottom and stops at the first match
• ELSE is optional, but skipping it means unmatched rows return NULL
• NULL cannot be checked using =, always use IS NULL or IS NOT NULL
• Empty strings and NULL are two different things and need separate handling
• Condition order matters, broader conditions placed too early can block more specific ones
• CASE combined with aggregate functions (like SUM) enables conditional aggregation, splitting one metric into multiple labeled columns
• CASE conditions can include subqueries, not just simple comparisons, useful for structural checks like tree classification
• COALESCE is a simpler alternative when the only goal is replacing NULL with a default value
• CASE performs best when used in SELECT for labeling, and should be used carefully inside WHERE or JOIN conditions where it may affect index usage

Pattern Recognition

When should you immediately think of CASE?

• Need to turn a raw value into a business label → CASE
• Need a threshold-based PASS/FAIL or similar verdict → CASE (Searched)
• Need to map fixed codes to readable text (like status codes) → CASE (Simple)
• Need to split one aggregate into multiple conditional totals → SUM(CASE WHEN …)
• Need to tell apart NULL from an empty string or from 0 → CASE with separate IS NULL and equality checks
• Need a fallback default only, nothing conditional → COALESCE instead of CASE
• Need to classify rows based on their relationship to other rows (like tree structure) → CASE with a correlated subquery