SQL Queries With CASE: Conditional Logic in a Query

Short answer

• A SQL query with CASE uses the CASE expression to return a different value for each row, based on conditions tested in order.
• The searched form is CASE WHEN condition THEN result WHEN condition THEN result ELSE result END. The simple form is CASE expression WHEN value THEN result ELSE result END.
• Use the searched form for ranges, comparisons, and more than one column. Use the simple form only to compare one expression for equality against several values.
• CASE stops at the first branch that is true. Put the most specific condition first and the most general condition last.
• If no WHEN branch matches and there is no ELSE, the result is NULL. Write an ELSE whenever a missing result would be confusing.
• CASE does not change stored data. It changes only the value returned by that query, unless you deliberately use it inside an UPDATE statement.
• Never test for NULL with = NULL inside a CASE. Use WHEN column IS NULL THEN, because a comparison with NULL does not evaluate to true.

A SQL query often needs to say: if this value falls in this range, show this label; if that column is empty, show a fallback; if this status code means shipped, show the word Shipped instead of the code. That row by row decision is exactly what CASE is for. It lets a query return different values for different rows without changing the stored data and without writing a separate query for each case.

CASE is sometimes called a statement in casual tutorials, but in a query it is an expression. An expression produces a value, so CASE can appear anywhere a value is allowed: in the SELECT list, inside a WHERE condition, inside an ORDER BY key, inside a GROUP BY key, and inside an aggregate function. This guide stays with that query form.

SHORT-ANSWER BOX

The Sample Table Used Throughout This Guide

Every example uses the same small orders table, so every result can be checked by hand. Status codes are deliberately short, because real tables often store codes rather than full words. Here P means pending, S means shipped, C means cancelled, and X means an unexpected code that is not in the known list. The X row is there on purpose: it exposes a missing ELSE or an assumption that every code is already known.

What CASE Returns, Row by Row

Think of CASE as a question asked once for each row. For order 101, a CASE that tests total_amount might ask: is 45 at least 500? No. Is it at least 100? No. So it falls through to ELSE and returns Small. For order 103, the same CASE asks the same questions about 520 and stops at the first true branch, returning Large. The query does not run a separate query per row. It evaluates the expression for each row as that row is processed.

PostgreSQL documentation describes CASE as a generic conditional expression, similar to if and else statements in other programming languages, and states that CASE clauses can be used wherever an expression is valid. Wherever you could write a column name or a calculation, you can usually write a CASE expression instead, provided the result type makes sense for that place.

The first match rule controls ordering. CASE does not collect every true branch. It returns the result of the first WHEN whose condition is true and does not process the rest. PostgreSQL documentation states that if a condition result is true, the value of the CASE expression is the result that follows the condition, and the remainder is not processed. MySQL documentation gives the same first match rule for its CASE operator, and SQLite documentation likewise describes lazy, or short circuit, evaluation. Ordering therefore decides the answer whenever branches overlap.

Searched CASE: The Flexible Form

The searched form is the form to learn first. Each WHEN holds a full condition, which may compare different columns, use ranges, use AND and OR, or test for NULL.

SELECT order_id, total_amount,
    CASE
        WHEN total_amount >= 500 THEN 'Large'
        WHEN total_amount >= 100 THEN 'Medium'
        WHEN total_amount IS NOT NULL THEN 'Small'
        ELSE 'Unknown amount'
    END AS order_size
FROM orders;

For order 103, total_amount is 520, so the first branch is true and the result is Large. For order 102, total_amount is 120, so the first branch is false, the second branch is true, and the result is Medium. For order 101, total_amount is 45, so the first two branches are false, the third branch is true, and the result is Small. For order 106, total_amount is NULL, so the first two comparisons do not evaluate to true, the IS NOT NULL test is false, and ELSE returns Unknown amount.

Notice the order of the first two branches. If the Medium branch came first, every order of 500 or more would also satisfy total_amount >= 100, and no order would ever reach the Large branch. That is the most common ordering mistake with ranges. Always order range branches from the highest, most specific threshold down to the most general threshold. The searched form can also test more than one column in a single branch, for example status and total_amount together. That flexibility is why the searched form is the right default choice.

Simple CASE: The Shorter Equality Form

The simple form compares one expression against several values for equality. It is shorter when you translate codes into labels, because you write the column name once instead of repeating it in every branch.

SELECT order_id, status,
    CASE status
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown status'
    END AS status_label
FROM orders;

The result is Pending for orders 101 and 105, Shipped for orders 102 and 103, Cancelled for order 104, and Unknown status for order 106, whose code X matches no branch. Without ELSE, order 106 would return NULL, and a reader might mistake an unknown code for a missing status. ELSE turns a silent gap into a visible label, which is almost always the better choice for a report.

PostgreSQL documentation describes the simple form as a variant in which the first expression is computed, then compared to each value in the WHEN clauses until one is found that is equal. MySQL documentation describes the same behavior for its CASE operator. The simple form therefore cannot test ranges, cannot test two columns at once, and cannot test for NULL in the way a beginner might expect.

If every branch tests the same expression for equality, with no NULL test, range, or second column, the simple form is fine. In every other case, use the searched form.

CASE in SELECT: Labels, Buckets, and Flags

The SELECT list is the most common home for CASE. Three patterns cover most SELECT uses: translating codes into labels, bucketing numbers into groups, and creating a flag.

Code translation is the pattern shown above. It does not change the stored code, only the label beside it. Keep the original column in the SELECT list during development, so you can check each mapping. Bucketing turns a continuous value into a small set of groups. The order size example buckets totals into Large, Medium, Small, and Unknown amount. State the boundaries in the report, and reuse them across related reports.

Flags turn a condition into a value that a later step can use. The following query creates a flag for orders that claim to be shipped but have no ship date, which is a data quality signal:

SELECT order_id, status, ship_date,
    CASE
        WHEN status = 'S' AND ship_date IS NULL THEN 'Check: shipped but no ship date'
        ELSE 'OK'
    END AS data_check
FROM orders;

Order 103 is the only row that returns the check flag, because it is the only shipped row with a NULL ship date. This pattern is powerful because it surfaces a contradiction without filtering any rows out. Filtering would hide the problem rows from the report. Flagging keeps every row visible and marks the ones that need attention.

CASE in WHERE: Filtering by a Computed Condition

CASE can also appear in WHERE, where it decides whether a row survives at all, rather than what value a column shows. A WHERE CASE is useful when different groups of rows need different thresholds. Suppose pending orders should be listed when their total is at least 100, while shipped orders should be listed regardless of total, because a shipped order needs a dispatch check even when it is small:

SELECT order_id, customer, total_amount, status
FROM orders
WHERE CASE
    WHEN status = 'P' THEN total_amount >= 100
    WHEN status = 'S' THEN TRUE
    ELSE FALSE
END;

For the sample table, this returns orders 102, 103, and 105. Order 105 is pending with a total of 500, so it passes. Orders 102 and 103 are shipped, so they pass regardless of total. Order 101 is pending with a total of 45, so it fails. Orders 104 and 106 fall into ELSE FALSE and are excluded.

In PostgreSQL, a CASE in WHERE must return a boolean value, because WHERE expects a condition. In MySQL and SQLite, TRUE is treated as 1, so writing TRUE and FALSE keeps the intent clear across dialects. If the rule is simple, plain AND and OR conditions are usually clearer than a WHERE CASE. Reserve WHERE CASE for rules where each group genuinely has a different test.

There is one more WHERE trap worth naming. A SELECT alias, such as order_size, cannot be relied on inside WHERE in standard SQL, because WHERE is evaluated before the SELECT list in the logical order of a query. If you need to filter on a CASE result, either repeat the CASE in WHERE, or wrap the SELECT in a subquery or a common table expression and filter on the alias in the outer query.

CASE in ORDER BY: Custom Sort Orders

Alphabetical order is often the wrong order for a report. A status list sorted alphabetically puts Cancelled first, Pending second, and Shipped third, which is not the order a warehouse team works through. CASE in ORDER BY fixes this by assigning a sort number to each value:

SELECT order_id, customer, status
FROM orders
ORDER BY CASE status
    WHEN 'P' THEN 1
    WHEN 'S' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END, order_id;

This returns pending orders first, then shipped orders, then cancelled orders, then any unknown code, with order id as a tiebreaker inside each group. For the sample table, the order is 101, 105, 102, 103, 104, 106. The ELSE 4 branch matters: without it, the unknown code X would sort as NULL, and its position would depend on the dialect default for NULL sorting.

The same pattern pushes NULL values to a chosen end of a result. Sorting by CASE WHEN ship_date IS NULL THEN 1 ELSE 0 END, followed by ship_date, puts rows with a ship date first in date order and rows without a ship date last, without relying on dialect specific NULL sorting keywords. Reversing the 1 and 0 would put missing dates first.

CASE in GROUP BY and Inside Aggregates: A Brief Map

CASE also appears in GROUP BY, where it groups rows by a computed label instead of by a stored column. Grouping by the order size CASE, for example, would count how many orders fall into Large, Medium, Small, and Unknown amount. Writers repeat the same CASE in both SELECT and GROUP BY, or wrap the labeled query in a subquery and group on the label in the outer query.

CASE inside an aggregate function produces conditional totals: placing a CASE inside COUNT or SUM counts or sums only matching rows in one pass. This guide mentions the pattern only as a map, because counting and filtering grouped results are covered in companion guides on aggregate functions and on the HAVING clause.

NULL Handling: The Part That Causes the Most Bugs

NULL means unknown or missing, not zero and not an empty string. That single fact explains most CASE bugs. A comparison involving NULL does not evaluate to true. It evaluates to unknown, and an unknown WHEN condition does not select its branch. CASE then moves to the next branch, and if no branch is true and there is no ELSE, the result is NULL.

Start with the simple form trap. The following query looks as if it handles a NULL status, but it does not:

SELECT CASE status
        WHEN NULL THEN 'Missing status'
        ELSE 'Has a status'
    END AS status_check
FROM orders;

The WHEN NULL branch never matches, because the simple form compares using equality, and NULL equals NULL does not evaluate to true. MySQL documentation states this directly for its CASE statement form: the simple syntax cannot be used to test for equality with NULL because NULL equals NULL is false. PostgreSQL and SQLite behave the same way for the simple form, because equality with NULL follows the same SQL rule. Every row, including a row whose status really is NULL, returns Has a status. That is a wrong answer produced by valid syntax, which is the worst kind of bug.

The fix is to use the searched form and test explicitly:

SELECT CASE
        WHEN status IS NULL THEN 'Missing status'
        ELSE 'Has a status'
    END AS status_check
FROM orders;

Now a NULL status returns Missing status, and every other value returns Has a status. Whenever NULL is a meaningful case in your data, write an explicit IS NULL branch in searched form. Do not rely on ELSE to catch NULL rows unless you have checked that ELSE also catches only the rows you intend it to catch. ELSE catches every unmatched row, including unexpected codes and NULL rows together. If those groups need different handling, they need different branches.

A second NULL trap involves the input expression of a simple CASE. SQLite documentation states that if the base expression is NULL, the result is the ELSE result if it exists, or NULL if it does not. So a simple CASE on a NULL column skips every WHEN branch and goes straight to ELSE. CASE does not convert NULL into a value on its own. For a simple fallback, COALESCE returns the first non NULL argument; use CASE when the fallback depends on a condition.

Result Types: Keep Every Branch Compatible

All branches of one CASE should return compatible types, because the database must choose a single result type for the whole expression. PostgreSQL documentation states that the data types of all the result expressions must be convertible to a single output type, and if branches mix a number and text, PostgreSQL raises an error rather than guessing. MySQL documentation states that the return type is the aggregated type of all result values, so MySQL resolves the mix by choosing a combined type. SQLite, with its flexible typing, stores the value each branch returns without forcing one column type across rows, but a later sort or comparison may still behave unexpectedly when text and numbers mix in one column. The practical rule is the same in every dialect: return text in every branch for a label column, and numbers in every branch for a numeric column. If a conversion is intended, convert explicitly in the branch so every branch returns text by design.

Dialect Differences, Labeled

The core CASE syntax is standard SQL and works in PostgreSQL, MySQL, and SQLite in the forms shown in this guide. The differences at the edges are labeled here.

PostgreSQL follows the standard CASE syntax closely. Its documentation states that CASE clauses can be used wherever an expression is valid, that result types must be convertible to a single output type, and that a CASE does not evaluate subexpressions that are not needed to determine the result. A guarded division can therefore avoid a division by zero failure in the guarded branch, although PostgreSQL documentation warns that evaluation timing is not ironclad in every situation.

MySQL has two distinct CASE forms. The CASE operator, used inside a query, ends with END and returns a value, with no ELSE producing NULL. The CASE statement, used inside stored programs, ends with END CASE and raises a Case not found error when no branch matches and there is no ELSE. This guide always writes END for query CASE expressions. MySQL also offers IF, and SQLite offers IIF, as two branch shorthand functions. Neither replaces CASE for three or more branches, or for branches that each need a different test.

SQLite follows the standard CASE forms. Its documentation, as reproduced in the language expressions reference, states that both forms use lazy evaluation, and that the simple form evaluates its base expression exactly once, while the searched form may evaluate a repeated expression multiple times. That rarely matters for a column reference, but it can matter for a costly function call.

Common Mistakes and How to Fix Them

Missing END is the most frequent syntax error. Every CASE needs one END, and a nested CASE needs one END per level. Using = NULL instead of IS NULL is the most frequent logic error, and it produces wrong results rather than an error message. Putting range branches in the wrong order is the most frequent ordering error: a broad branch placed before a narrow branch swallows the narrow branch rows, so test boundary values of exactly 100 and exactly 500 in the order size example. Mixing result types is the most frequent type error, so keep branch types consistent. Forgetting ELSE turns every unmatched row into NULL, so add ELSE with a visible label during development. Repeating a long CASE in several places is the most frequent maintenance error, so wrap the labeled query in a subquery or a common table expression and refer to the alias once. Finally, a CASE with twenty code translations belongs in a table; a CASE with three or four stable branches is fine.

A Complete Worked Example

The following query combines the patterns in this guide into one report: status labels, order size buckets, a data quality flag, and a custom sort that puts pending orders first.

SELECT order_id, customer,
    CASE status
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown status'
    END AS status_label,
    CASE
        WHEN total_amount >= 500 THEN 'Large'
        WHEN total_amount >= 100 THEN 'Medium'
        WHEN total_amount IS NOT NULL THEN 'Small'
        ELSE 'Unknown amount'
    END AS order_size,
    CASE
        WHEN status = 'S' AND ship_date IS NULL THEN 'Check: shipped but no ship date'
        ELSE 'OK'
    END AS data_check
FROM orders
ORDER BY CASE status
    WHEN 'P' THEN 1
    WHEN 'S' THEN 2
    WHEN 'C' THEN 3
    ELSE 4
END, order_id;

The expected result, in order, is: order 101, Ana, Pending, Small, OK; order 105, Erin, Pending, Large, OK; order 102, Ben, Shipped, Medium, OK; order 103, Cleo, Shipped, Large, Check: shipped but no ship date; order 104, Dan, Cancelled, Small, OK; order 106, Finn, Unknown status, Unknown amount, OK. Trace order 105: its total 500 meets the Large threshold at the first range branch, and its NULL ship date does not trigger the data check, because the check also requires status S.

When Not to Use CASE, and a Testing Checklist

CASE is not the right tool for every conditional task. When a decision depends on data that changes often, such as a price list maintained by another team, a lookup table joined to the query is easier to maintain. When a decision depends on business rules outside the database, application code is the right place. CASE also does not replace a filter: if a report should show only shipped orders, use WHERE status = ‘S’.

Test each branch with at least one row, including ELSE. Test boundary values and a NULL value for every column the CASE reads. Test an unexpected value, such as the X code in this guide. Test the row count before and after adding a CASE in WHERE, because a WHERE CASE filters rows, while a SELECT CASE does not. Finally, run the query on the dialect you will actually use.

FAQ

What is a SQL query with CASE?

A SQL query with CASE is a query that uses the CASE expression to return different values for different rows, based on conditions tested in order. It is the SQL equivalent of if, else if, and else logic inside a query.

What is the difference between simple CASE and searched CASE?

Simple CASE compares one expression for equality against several values, as in CASE status WHEN ‘P’ THEN ‘Pending’. Searched CASE tests a full condition in each branch, as in CASE WHEN total_amount >= 500 THEN ‘Large’. Use searched CASE for ranges, multiple columns, and NULL tests. Use simple CASE only for short equality translations.

Does CASE stop at the first true condition?

Yes. CASE tests WHEN branches in order and returns the result of the first branch whose condition is true. Later branches are not processed for that row. Order range branches from the most specific to the most general, or a broad branch will swallow rows meant for a narrower branch.

What happens if no condition matches and there is no ELSE?

The CASE result is NULL. That is valid syntax, but it can hide unmatched rows, unexpected codes, and NULL inputs behind the same blank result. Write an ELSE with a visible label whenever a missing result would be confusing.

How do I test for NULL in a CASE expression?

Use the searched form with IS NULL or IS NOT NULL, as in WHEN status IS NULL THEN ‘Missing status’. Do not use WHEN NULL in a simple CASE, and do not use = NULL, because a comparison with NULL does not evaluate to true and the branch will never match.

Can I use CASE in WHERE and ORDER BY?

Yes. In WHERE, CASE returns a condition that decides whether a row survives. In ORDER BY, CASE assigns a sort number to each value, which creates a custom sort order that alphabetical sorting cannot express. Use a tiebreaker column after a CASE in ORDER BY for a stable order inside each group.

Can CASE change data in a table?

CASE on its own does not change stored data. It only changes the value returned by a query. CASE can change data when it appears inside an UPDATE statement, where it computes the new value for each row. Test an UPDATE on a copy of the data before running it on a live table.

What is the difference between CASE and IF or IIF?

CASE handles any number of branches and any kind of condition. IF in MySQL and IIF in SQLite are shorthand functions for a single condition with two outcomes. Use IF or IIF for a simple two way choice when your dialect offers it, and use CASE for three or more branches, or for branches that each need a different test.

Why does my CASE return NULL for every row?

Check four things in order. First, confirm that every CASE has a matching END. Second, check whether the tested column contains NULL values that no branch handles. Third, check whether a simple CASE is being used to test for NULL, which never matches. Fourth, check whether branch conditions use = NULL or compare incompatible types.

Should I use CASE or a lookup table for code translations?

Use CASE for a short, stable list of translations, such as three or four status codes that rarely change. Use a lookup table when the list is long, when new codes are added often, or when another team maintains the list. A lookup table lets you add a code by adding a row.

Keep reading: Inner Join in SQL: How Matching Rows Work, With Worked Examples

Keep reading: COUNT of COUNT in SQL: Counting the Counts

Keep reading: HAVING in SQL: Filtering Grouped Results

Sources & references

• PostgreSQL Documentation, "Conditional Expressions": CASE syntax for searched and simple forms, CASE usable wherever an expression is valid, first true condition wins with the remainder not processed, no matching condition and no ELSE gives NULL, result types must be convertible to a single output type, and the guarded division example showing that unneeded subexpressions are not evaluated. https://www.postgresql.org/docs/current/functions-conditional.html
• MySQL Reference Manual, "Flow Control Functions": CASE operator syntax for simple and searched forms, first true comparison or condition wins, no matching condition and no ELSE gives NULL, return type is the aggregated type of all result values, and the distinction between the CASE operator ending in END and the CASE statement for stored programs ending in END CASE. https://dev.mysql.com/doc/refman/9.1/en/flow-control-functions.html
• MySQL Reference Manual, "CASE Statement" for stored programs: the simple form cannot test for equality with NULL because NULL equals NULL is false, and a stored program CASE with no match and no ELSE raises a Case not found error. https://dev.mysql.com/doc/refman/8.3/en/case.html?ff=nopfpls
• SQLite Language Expressions reference: both CASE forms use lazy, short circuit evaluation, the simple form evaluates its base expression once while the searched form may evaluate a repeated expression multiple times, and a NULL base expression gives the ELSE result or NULL. https://sqlite.org/lang_expr.html (§7 CASE, §14 booleans) and https://sqlite.org/datatype3.html (flexible typing)