How do you write SQL case when with multiple conditions?

In this article

1. Before you start
2. Why interviewers ask this
3. Case when with multiple conditions 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

Case expressions are one of the core tools in the SQL interview questions guide.

Why interviewers ask this

A case expression is the way SQL does if, else-if, else, so a candidate who cannot write one cleanly cannot label, bucket or flag data. Interviewers use it because small mistakes show up fast: a wrong branch order, a missing fallback, or a null that slips through.

A weak answer lists the syntax and stops. A strong answer says that the branches run in order, that the first true one wins, that a missing else branch gives null, and that a test against null needs a null test. A Staff-level answer also knows where a case expression can sit (a select list, a filter clause, inside an aggregate) and why a guard around a constant or an aggregate does not fully protect against division by zero. The same skill appears whenever you build a conditional total, which the conditional total page covers. For the wider basics of the statement, see how to use case statements in SQL.

Case when with multiple conditions from first principles

Searched case expression

A searched case expression is a SQL expression that tests a list of boolean conditions in order and returns the result that belongs to the first condition that is true, or the else result when none is. It is SQL's way to write if, else-if, else inside a query.

Simple case expression

A simple case expression is a SQL expression that computes one value once and compares it for equality with each value listed in its branches, returning the result of the first equal one. It cannot test ranges, AND, OR or null tests.

In plain words

Think of a bouncer with a list of rules: "if you are on the staff list, go in; else if you have a ticket, go in; else stay out." The bouncer reads the rules top to bottom. The bouncer acts on the first one that fits. The analogy breaks in one place. A bouncer can see that a rule is unclear. SQL treats an unknown test as "not true" and moves on without warning.

In one precise sentence, a case expression is a single value computed per row by trying conditions in order. The PostgreSQL documentation describes it as a generic conditional expression, similar to if and else statements in other programming languages. The same page says it can be used wherever an expression is valid.

Step 1: One branch is a test and a result

The smallest useful form has one branch and a fallback. The syntax in the PostgreSQL documentation opens the expression with a case keyword, lists branches and an optional fallback, and closes it with an end keyword:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

A concrete query with one branch and a fallback:

SELECT name,
       CASE WHEN salary > 100000 THEN 'senior'
            ELSE 'other'
       END AS band
FROM employees;

Input: one row. The database tests salary > 100000; if it is true, the column shows 'senior', otherwise 'other'. Output: one value per row, never a changed row. The closing keyword ends the expression, and the alias after it names the new column. You now know the shape; the next step handles several branches.

Step 2: Order the branches so the first true one is the one you mean

Multiple conditions most often means several branches, and the database tests them from top to bottom. The current documentation says that once a condition is true, its result is the value of the expression and the remainder of the case expression is not processed. Branches are never combined; the first true one wins.

That makes order matter whenever conditions overlap. Write the narrow test first:

CASE WHEN salary > 100000 THEN 'senior'   -- narrow test first
     WHEN salary > 50000  THEN 'mid'      -- broader test second
     ELSE 'junior'
END

Swap the first two branches and every salary above the high limit also passes the broad test, so 'senior' can never be returned and no error warns you. The worked example below shows the effect on real rows. Rule of thumb: sort branches from most specific to most general.

Step 3: Combine tests inside one branch with AND, OR and parentheses

Multiple conditions can also mean several tests inside a single branch. Tests inside a branch combine with logical operators such as AND and OR. The current documentation says each condition is an expression that returns a boolean result, so anything that yields true or false can sit there, including AND, OR, NOT, range tests, list-membership tests and null tests.

CASE WHEN dept = 'Sales' AND rating >= 4 THEN 'focus'
     WHEN dept = 'Eng' OR rating >= 4    THEN 'review'
     ELSE 'standard'
END

Use parentheses whenever you mix AND with OR, because AND binds more tightly than OR (see the operator precedence table) and a reader cannot see your intent otherwise. The two conditions below differ:

WHEN (a OR b) AND c
WHEN a OR (b AND c)

Each branch is still one condition, so the order rule from Step 2 still applies across branches.

Step 4: Else and null decide what happens when nothing matches

If no condition is true, the value is the else result, and the PostgreSQL documentation states that when the else part is omitted and no condition is true, the result is null. It is not an empty string, not zero and not an error.

"Not true" includes "unknown". Ordinary comparisons return null, meaning unknown, when either input is null; the PostgreSQL comparison-operator page shows that comparing a number with null yields null and says not to write = NULL. A branch whose test comes out unknown is skipped exactly like a false one, so a row with a missing value silently falls to a later branch or to the else result. To catch missing values on purpose, put a null-test branch where you want it. Every result must also convert to one output type, as the current documentation requires.

1 · One branch2 · Order3 · Combine tests4 · Else and nullReplay

Step-by-step description

1. A branch is a test and a result.
2. The first true branch wins.
3. Logical operators join tests in one branch.
4. No true branch gives the else value or null.

• A branch is a test and a result.
• Branches run in order and the first true one wins.
• Tests inside a branch combine with logical operators.
• With no true branch and no else, the result is null.

Worked example

Five illustrative employees show all four steps, and each label below was computed by running the rules in code.¹ The first query uses the salary bands from Step 2, once in the wrong order and once in the right order. The second uses the two-test branches from Step 3.

Only Ana's label changes, because only her salary passes both tests. The count per label is shown below.¹

Text description of the diagram

• With the broad test first, three of five employees are labeled mid and none senior.
• With the narrow test first, one employee is senior and two are mid.

Now the combined tests of Step 3. Ana (Sales, rating 5) passes the first branch and gets focus. Cy (Eng) and Di (Eng, rating 4) fail the first branch and pass the second through the department test, so both get review.

Ben (Sales, rating null) is the instructive row: his first test is true AND unknown, which is unknown, and his second is false OR unknown, which is also unknown, so no branch is true and he lands in the else result with standard. Eve (Ops, rating null) also lands in standard, by a different route: her first test is false AND unknown, which is false, because one false side makes AND false; her second is false OR unknown, which is unknown.¹

Two more checks from the same data. A case expression with one branch testing for a rating of at least 4 and no else part returns null for Ben, Cy and Eve, which is three of five rows.¹ And a simple case that compares the rating with null labels all five rows has value, because comparing with null never gives true; a searched case with a null test correctly labels two rows.¹

-- simple case: never matches null, so all five rows say 'has value'
CASE rating WHEN NULL THEN 'none' ELSE 'has value' END

-- searched case with a null test: labels the two missing ratings
CASE WHEN rating IS NULL THEN 'none' ELSE 'has value' END

Trade-offs and edge cases

The simple form is shorter, but the searched form is more general. The PostgreSQL documentation says the simple form computes its first expression and compares it with each value until one is equal, so it only tests equality.

Text description of the diagram

• The simple form tests equality only.
• The searched form accepts any boolean condition.
• A simple case never matches null.

Use the simple form for mapping codes to labels and the searched form for everything else. Four edge cases matter in practice.

• Overlapping conditions. Order from most specific to most general, as in Step 2.
• Mixed result types. Text in one branch and a number in another can force a conversion or an error, so keep every result the same type.
• Division by zero. A guard on a column value works row by row, but the expression evaluation rules warn that the rule is not ironclad: a constant 1/0 in an arm that never runs can fail at planning time, and an aggregate inside a case expression is computed before the case decides. Making the divisor null with the null-if function avoids both.
• Other places. A case expression also works in a sort clause, as in sorting rows, and inside aggregates, as in a conditional total.

CASE WHEN b <> 0 THEN a / b END          -- guard on a column value: safe row by row
CASE WHEN min(x) > 0 THEN avg(1/x) END   -- aggregate runs first: can still fail
a / NULLIF(b, 0)                         -- null divisor instead of an error

Other database engines add their own limits and extra procedural forms; this page cites PostgreSQL, so check your engine's documentation before relying on a detail not covered here.

How to answer it in an interview

The spoken structure: say what a branch is, say the first true branch wins, then name the fallback and the null trap.

Common mistakes

• "I put the general condition first." (wrong) The broad test catches rows meant for later branches. (right) "I order branches from the most specific to the most general."
• "Without an else part I get an empty value or an error." (wrong) The result is null. (right) "No true condition and no else part returns null."
• "A simple case comparing with null finds the missing ratings." (wrong) Comparing with null never gives true. (right) "I use a null test in a searched case."
• "A case guard always prevents division by zero." (wrong) A constant 1/0 can fail at planning time, and aggregates are computed before the case decides. (right) "A guard works on column values, but not around constants or aggregates."
• "A case block is a statement that runs steps, like an if statement." (wrong) In a query it is an expression that returns one value. (right) "It returns a value, so it can sit anywhere an expression can."

Follow-up questions

How do you use case when with multiple conditions in SQL?

Write one when-condition-then-result branch per outcome, put the most specific branch first, add an else part, and close with the end keyword. Combine tests inside a branch with AND, OR and parentheses.

Can you use AND and OR inside one branch?

Yes. A condition only has to return a boolean result, so any combination of tests works. Add parentheses when mixing the two, because AND is evaluated before OR.

What happens if no branch condition is true?

The else result is returned, and if there is no else part the result is null. Rows whose test is unknown behave the same as rows whose test is false.

Does case when stop at the first true condition?

Yes: the remaining branches are not processed once a condition is true. The caveat is that constant subexpressions and aggregates inside it can be evaluated first.

Can a case expression return different data types?

Only if the result expressions can be converted to a single output type. Keep every branch the same type to avoid surprises.

What if I need to relabel many stored rows with one case expression?

Put the case expression in an update statement and run it inside a transaction so you can roll it back if the labels look wrong; transaction control language explains how.

Sources

1. PostgreSQL Documentation: Conditional Expressions (current version) — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
2. PostgreSQL Documentation: Expression Evaluation Rules (Section 4.2.14) — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
3. PostgreSQL Documentation: Comparison Functions and Operators (current version) — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
4. PostgreSQL Documentation: Lexical Structure, operator precedence (current version) — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026