How do you use CASE statements in SQL?

In this article

1. Before you start
2. Why interviewers ask this
3. CASE expression from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page is one step of the SQL interview questions guide.

Why interviewers ask this

A CASE question is a quick test of whether a candidate can turn a business rule into a column. The rule might be "label each customer by spend" or "count orders by status". The rule is easy to say, but writing it correctly needs three habits: ordering the conditions, handling missing values, and knowing what happens when nothing matches.

A weak answer lists the syntax and stops. A strong answer says that CASE returns a value, that the first true condition wins, that a missing fallback branch (ELSE) gives NULL, and that a NULL input needs its own branch. A Staff-level answer adds where the expression can sit, such as inside an aggregate or in the sort clause, and which database-specific rules apply, such as how SQL Server picks the output type.

CASE expression from first principles

Searched CASE

A searched CASE is the form built from a condition branch (WHEN), a result marker (THEN) and a closing marker (END), in which every branch holds its own full condition, so it can test ranges, combinations and the missing-value test.

Simple CASE

A simple CASE is the form CASE expression WHEN value THEN result ... END that computes one expression once and compares it with each WHEN value for equality, like a switch statement in C.

In plain words

Think of a sorting clerk with a stack of parcels and a list of rules on a card. The card says: if heavier than ten kilos, left bin; else if addressed abroad, middle bin; else right bin. The clerk reads the card from the top for each parcel and stops at the first rule that fits. The analogy breaks in one place. A parcel always goes somewhere, but a row with no matching rule and no ELSE gets the value NULL, a missing value, rather than an error.

In one precise sentence, a CASE expression is a function from one row to one value, defined by an ordered list of condition and result pairs. The PostgreSQL 18 documentation (read October 2026) calls it a generic conditional expression, similar to if/else statements in other programming languages. The name "case statement" is common in search. The correct term is expression, because it returns a value.

Step 1: Write the CASE skeleton

The searched form has four parts: CASE, one or more WHEN condition THEN result pairs, an optional ELSE result, and END. Input: the values of one row. Output: one value, which you usually give a name with an alias, such as AS band. The first true pair decides the result, and the remaining WHEN clauses are not processed.

SELECT name,
       salary,
       CASE
         WHEN salary IS NULL THEN 'unknown'
         WHEN salary < 50000 THEN 'low'
         WHEN salary < 90000 THEN 'mid'
         ELSE 'high'
       END AS band
FROM employees;

Read it as a column definition: for every row, the database computes band from that row's salary. Nothing is filtered and no rows are added. The PostgreSQL documentation (read October 2026) states that a CASE can be used wherever an expression is valid, so the same skeleton works in SELECT, the filter clause WHERE, the sort clause and inside aggregate functions. You now have the shape; the next step is how the database walks through it.

Step 2: Each WHEN is tested in order, top to bottom

The database tests the WHEN conditions from the top, one at a time, for each row. Input: one row. Output: the first condition that is true, or none. Once one is true, the remaining WHEN clauses are not processed for that row.

The order is part of the meaning when conditions overlap. In the sketch above, a salary of 42,000 satisfies both salary < 50000 and salary < 90000; it gets low only because the low test comes first. Swap those two lines and the low test is never reached for any row, because every salary under 50,000 is also under 90,000. In the illustrative table of the worked example, that single swap moves all five rows below 90,000 into mid and leaves the low band empty, with no error message.¹

The rule of thumb: put the most specific condition first, and the most general last. You now know that CASE is a top-down search; the next step is what it does when the search stops or fails.

Step 3: The first true WHEN wins, and no ELSE gives NULL

The first true condition wins. When a condition is true, the expression returns the matching result and the remaining WHEN clauses are not processed. The PostgreSQL documentation (read October 2026) says this directly: the first true condition wins and later conditions are not processed. With no ELSE and no true condition, the result is NULL, not an error and not zero; the MySQL 8.4 manual (read October 2026) describes the same behaviour.

A subtle effect follows from how conditions treat missing values. A condition that compares NULL with a number is unknown, and CASE only accepts true, so an unknown condition counts as not matched. A row with a missing salary skips every salary < ... test and lands in the ELSE branch. That is why the skeleton above checks salary IS NULL first. In the illustrative table, leaving that check out puts the one missing-salary row into high, giving three high rows instead of two.¹

Write ELSE NULL when NULL is intended.

Step 4: All results must fit one data type

A CASE returns one column, so every result must be convertible to one output type. The PostgreSQL documentation requires this of the result expressions. Microsoft's documentation for SQL Server (updated October 2026) says the expression returns the highest-precedence type among the results and the ELSE result, so mixing a number and text can fail or convert in a way you did not intend.

The practical habit is to make all branches the same kind of value: all text labels, all numbers, or all dates. If you must mix them, cast explicitly. Input: the results of all branches. Output: one column with one type. Step by step, the whole expression is now clear: build the skeleton, test in order, stop at the first true test, fall back to ELSE or NULL, return one type.

1 · Skeleton2 · Order3 · First true wins4 · One typeReplay

Step-by-step description

1. CASE returns one value per row.
2. WHEN tests run top to bottom.
3. No true test and no ELSE gives NULL.
4. All results fit one data type.

• A CASE expression tests its WHEN conditions in order.
• The remaining WHEN clauses are not processed.
• With no ELSE and no true condition the result is NULL.

Worked example

The table below is illustrative: eight employees, one with no salary recorded. The bands are low under 50,000, mid under 90,000 and high otherwise.¹ The query is the one from Step 1.

Text description of the diagram

• With the IS NULL branch first, the bands hold 2, 3, 2 and 1 rows.
• A row with a missing salary falls into the ELSE band.

With the IS NULL branch first, the bands hold 2, 3, 2 and 1 rows: one row is unknown and two rows are high. With only an ELSE they hold 2, 3 and 3, because the missing salary is counted as high, giving three high rows. A row with a missing salary falls into the ELSE band when there is no IS NULL branch. Two more computed checks show the rules from the steps.

With the conditions swapped so that salary < 90000 comes before salary < 50000, the counts become zero low, five mid, two high and one unknown.¹ And in the documentation's tiny example, a column a holding illustrative values 1, 2 and 3 is mapped by CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'other' END to one, two and other; the simple form CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END gives the same three outputs.¹

CASE also turns into a counter. Inside SUM, the expression SUM(CASE WHEN salary < 50000 THEN 1 ELSE 0 END) adds one for each low-salary row, which gives 2 on this table.¹ The guide to undefined and undefined covers conditional totals in full. Microsoft's documentation shows the same idea with MAX, written MAX(CASE WHEN ... ELSE NULL END), inside an aggregate.

Trade-offs and edge cases

Searched against simple. The simple form is shorter when you compare one column with fixed values, but it only tests equality. It cannot express a range such as salary < 50000. Because a comparison with NULL is never true, CASE x WHEN NULL never matches a missing value; use WHEN x IS NULL in the searched form.

Text description of the diagram

• A searched CASE holds a full condition in each WHEN.
• A simple CASE compares one expression for equality.

Division by zero. A CASE evaluates only the subexpressions it needs, so CASE WHEN x <> 0 THEN y / x ELSE NULL END avoids dividing by zero for the rows where x is zero. The PostgreSQL documentation calls this principle "not ironclad": a constant expression such as 1/0 can be evaluated while the query is planned and fail before any row is read. Microsoft's documentation also notes caveats for aggregates inside CASE, so guard with the data, not with trust in evaluation order.

Shorthand. COALESCE(a, b) returns the first non-null argument, and nullif(a, b) returns NULL when the two are equal; both are shorter than the equivalent CASE.

Nesting and readability. A CASE can sit inside another CASE (SQL Server allows only 10 levels of nesting), but long nests are hard to review. Prefer one flat CASE with ordered conditions; the guide to SQL case when with multiple conditions shows how to combine tests. Where a CASE sits in the sort clause, the guide to sorting with undefined explains the sort it feeds.

How to answer it in an interview

The spoken structure: say what CASE returns, then the order rule, then the ELSE and NULL behaviour, then the type rule.

Common mistakes

• "CASE is a statement that runs every branch." (wrong) It is an expression and stops at the first true condition. (right) "It returns one value and skips the remaining branches."
• "If nothing matches, I get an error, or zero." (wrong) Without ELSE the result is NULL. (right) "I write ELSE so the fallback is deliberate."
• "Order of the WHEN lines does not matter." (wrong) It matters whenever conditions overlap, as the swapped bands show. (right) "I list the most specific test first."
• "CASE x WHEN NULL THEN ... catches missing values." (wrong) Equality with NULL is never true. (right) "I use WHEN x IS NULL in the searched form."

Follow-up questions

What happens if no CASE condition matches?

The result is NULL when there is no ELSE, as the PostgreSQL documentation states. With an ELSE, that branch's result is returned.

What is the difference between simple and searched CASE?

Simple CASE computes one expression and compares it with each WHEN value for equality. Searched CASE evaluates a full condition in each WHEN, so it handles ranges, combined conditions and IS NULL.

Can you use CASE in WHERE or ORDER BY?

A CASE expression works in WHERE and ORDER BY, because it can be used wherever an expression is valid. It can build a custom sort key in ORDER BY or a condition in WHERE. The guide to sorting with undefined shows how sorting then works.

How do you count rows by a condition with CASE?

Wrap the CASE in an aggregate: SUM(CASE WHEN condition THEN 1 ELSE 0 END) counts the matching rows. See the guide to undefined and undefined for variants.

Can CASE prevent division by zero?

Usually, because a CASE skips branches it does not need, but the documentation says this is not ironclad, so also avoid constant divisions by zero and filter where you can.

Does CASE change data?

No. A CASE only computes a value. Changing stored data is the job of data-changing statements, whose results are made permanent with transaction control. More SQL questions are in the SQL interview guide.

Sources

1. PostgreSQL 18 Documentation, 9.18 Conditional Expressions — PostgreSQL Global Development Group, 13 Aug 2026
2. CASE (Transact-SQL) - SQL Server — Microsoft Learn, 19 Oct 2026
3. MySQL 8.4 Reference Manual, Flow Control Functions — Oracle (MySQL), 26 Oct 2026