Q: Salary tier from a single threshold

CASE WHEN evaluates each condition in order and returns the corresponding value for the first condition that is true. Alice's salary (90000) satisfies >= 70000, so she gets 'Senior'. Bob's salary (55000) does not, so the ELSE branch returns 'Junior'. CASE is an expression - it returns a value and can appear anywhere a value is expected: SELECT, WHERE, ORDER BY.

Q: Where can a CASE appear in a query?

CASE is an expression in SQL - it evaluates to a single value just like a column reference or function call. Because it produces a value, you can embed it anywhere an expression is valid: SELECT lists, WHERE predicates, HAVING clauses, ORDER BY, and even CHECK constraints. Programmers coming from procedural languages often confuse it with a switch statement, but there are no code blocks or side effects - only a returned value.

Q: No CASE branch matches - what happens?

ELSE is optional in CASE. When no WHEN matches and there is no ELSE, SQL returns NULL - not an error and not an empty string. This can silently introduce NULLs that propagate through calculations. For example, SUM(CASE WHEN ... THEN amount END) will ignore non-matching rows (NULL + anything = NULL in aggregation? No - SUM ignores NULLs), but arithmetic like total_price * CASE ... END could go NULL unexpectedly. Explicitly writing ELSE NULL makes the intent clear and helps future readers understand the behavior is intentional.

Q: Range check: simple CASE or searched CASE?

The simple CASE form (CASE expression WHEN value THEN ...) compares using equality only - you cannot use >, =, LIKE, or BETWEEN in the WHEN clause. For range comparisons or any predicate beyond equality, you must use the searched CASE form (CASE WHEN condition THEN ...). Trying to write CASE score WHEN >= 90 is a syntax error in every major SQL dialect.

Question 1

Simple CASE (equality form)

Accepted Answer

The simple CASE form places the expression to test right after CASE, then each WHEN provides a value to compare against using equality. CASE status WHEN 'shipped' is equivalent to CASE WHEN status = 'shipped'. 'returned' doesn't match any WHEN, so the ELSE branch returns 'Other'.

Question 2

Which branch wins when ranges overlap?

Accepted Answer

CASE evaluates WHEN clauses top to bottom and stops at the first match. Order id=1: 50 < 100 -> 'Small'. Order id=2: 200 is not < 100, but 200 < 500 -> 'Medium'. Order id=3: 600 is neither < 100 nor < 500 -> ELSE 'Large'. Condition order matters - if you put amount < 500 before amount < 100, everything under 100 would match the first branch and never reach the second.

Question 3

Salary tier from a single threshold

Accepted Answer

CASE WHEN evaluates each condition in order and returns the corresponding value for the first condition that is true. Alice's salary (90000) satisfies >= 70000, so she gets 'Senior'. Bob's salary (55000) does not, so the ELSE branch returns 'Junior'. CASE is an expression - it returns a value and can appear anywhere a value is expected: SELECT, WHERE, ORDER BY.

Question 4

Translating if/elif chains to SQL

Accepted Answer

The searched CASE form (CASE WHEN condition THEN result ...) evaluates each WHEN in order and stops at the first TRUE - exactly like an if/elif chain. Because evaluation stops at the first match, WHEN score >= 80 only runs if score < 90 already failed. You do not need WHEN score >= 80 AND score < 90. The simple CASE form (CASE score WHEN 90 THEN ...) only checks equality, not ranges - it would not work here.

Question 5

Where can a CASE appear in a query?

Accepted Answer

CASE is an expression in SQL - it evaluates to a single value just like a column reference or function call. Because it produces a value, you can embed it anywhere an expression is valid: SELECT lists, WHERE predicates, HAVING clauses, ORDER BY, and even CHECK constraints. Programmers coming from procedural languages often confuse it with a switch statement, but there are no code blocks or side effects - only a returned value.

Question 6

No CASE branch matches - what happens?

Accepted Answer

ELSE is optional in CASE. When no WHEN matches and there is no ELSE, SQL returns NULL - not an error and not an empty string. This can silently introduce NULLs that propagate through calculations. For example, SUM(CASE WHEN ... THEN amount END) will ignore non-matching rows (NULL + anything = NULL in aggregation? No - SUM ignores NULLs), but arithmetic like total_price * CASE ... END could go NULL unexpectedly. Explicitly writing ELSE NULL makes the intent clear and helps future readers understand the behavior is intentional.

Question 7

Range check: simple CASE or searched CASE?

Accepted Answer

The simple CASE form (CASE expression WHEN value THEN ...) compares using equality only - you cannot use >, <, >=, LIKE, or BETWEEN in the WHEN clause. For range comparisons or any predicate beyond equality, you must use the searched CASE form (CASE WHEN condition THEN ...). Trying to write CASE score WHEN >= 90 is a syntax error in every major SQL dialect.

Question 8

Conditional aggregation with CASE

Accepted Answer

Wrapping CASE inside SUM (or COUNT, AVG) is called conditional aggregation - it lets you pivot or filter within an aggregate without a subquery. For Alice: shipped order = 100, pending order = 0 (ELSE). SUM = 100. For Bob: shipped order = 300. SUM = 300. A common variant: COUNT(CASE WHEN status = 'shipped' THEN 1 END) counts shipped rows per customer.

Question 9

CASE with no ELSE

Accepted Answer

When CASE has no ELSE and no WHEN condition matches, the result is NULL. Bob's dept is 'Sales', not 'Eng', so none of the WHEN branches match and CASE returns NULL. This is different from an error - it is valid SQL. However, NULL in unexpected places can cause bugs downstream, so it is usually clearer to include an explicit ELSE.

Question 10

Sorting by a priority label, not alphabetically

Accepted Answer

CASE in ORDER BY maps text priority values to sort numbers, enabling custom sort orders that don't follow alphabetical or insertion order. high -> 1 (first), medium -> 2, low -> 3 (last). Result: Beta (high), Gamma (medium), Alpha (low). This technique is useful for any enum-like column where the natural order differs from alphabetical.

Question 11

COUNT with conditional CASE

Accepted Answer

COUNT ignores NULLs. When CASE finds no matching WHEN and has no ELSE, it returns NULL - so non-matching rows contribute nothing to the count. Alice has 1 shipped order (the pending row produces NULL and is skipped by COUNT). Bob has 2 shipped orders. This is cleaner than a subquery: one pass over the table computes per-customer counts for any number of statuses simultaneously.

Question 12

Grouping employees into salary bands on the fly

Accepted Answer

CASE can appear in GROUP BY just like any other expression. Alice (95000) -> Senior, Bob (65000) -> Junior, Carol (80000) -> Senior. GROUP BY collapses by the CASE result: Senior group has Alice and Carol (count=2), Junior group has Bob (count=1). Some databases let you reference the SELECT alias in GROUP BY (GROUP BY band), but the standard-compliant form repeats the expression.

Question 13

Pivoting rows to columns with CASE

Accepted Answer

Conditional aggregation with CASE is the standard SQL pivot technique - transforming row values into columns without a proprietary PIVOT keyword. For each product, SUM(CASE WHEN month='Jan' THEN amount ELSE 0 END) sums only January rows, producing a Jan column. Same for Feb. This works in every major database and scales to any number of columns - just add more CASE expressions.

Question 14

Catching NULL - which comparison actually works?

Accepted Answer

NULL = NULL evaluates to UNKNOWN in SQL, not TRUE. So CASE WHEN discount = NULL never matches. The simple CASE form (CASE discount WHEN NULL ...) also uses equality internally and will never match NULL. The only correct test for NULL is IS NULL. CASE WHEN discount IS NULL correctly returns 0 for NULL rows and the real discount otherwise. COALESCE(discount, 0) is the shorter equivalent - both compile to the same plan in most databases.

Question 15

When to use CASE pivot vs PIVOT keyword

Accepted Answer

PostgreSQL (and most ANSI-compliant databases) do not have a PIVOT keyword. SQL Server and Oracle do, but they are vendor-specific extensions. Conditional aggregation - SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) - is the portable solution that works everywhere. The downside is that you must hard-code each column name; if the set of values changes, you update the query. For dynamic column sets, some databases offer crosstab functions or application-level pivoting.

Question 16

Overlapping WHEN clauses - who wins?

Accepted Answer

The SQL standard guarantees that CASE evaluates WHEN clauses in the order they are written and stops at the first one that is TRUE. This is different from WHERE predicates, which a database optimizer can evaluate in any order for performance. The guaranteed order is why writing WHEN amount < 100 before WHEN amount < 500 works correctly - once the first branch fires, later ones are never reached.

Question 17

Overlapping WHEN conditions - order matters

Accepted Answer

CASE evaluates WHEN clauses top to bottom and stops at the first match. Alice scores 95, which satisfies >= 70 - so she gets 'Pass', not 'Distinction', even though 95 >= 90 is also true. The second WHEN is never checked because the first already matched. To fix this, put the more specific condition (>= 90) before the broader one (>= 70). This is a common bug: both branches overlap, but the ordering makes the stricter check unreachable.

Question 18

Simple CASE vs searched CASE - LIKE predicate

Accepted Answer

Simple CASE (CASE expression WHEN value ...) tests for equality and nothing else. Writing CASE name WHEN 'Pro%' THEN ... compares name to the literal string 'Pro%', not a pattern. To use LIKE, BETWEEN, IS NULL, or any other predicate, you need the searched CASE form where the full predicate goes in the WHEN clause. The searched form is strictly more powerful - simple CASE is just a convenience shorthand for pure equality checks.

Question 19

Dynamic threshold inside WHERE

Accepted Answer

CASE is an expression, so it can appear anywhere a value is expected - including the right-hand side of a WHERE comparison. For Widget (category A): threshold is 15, price 10 is not > 15, excluded. For Gadget (category B): threshold is 25, price 30 > 25, included. For Doohick (category A): threshold is 15, price 20 > 15, included.

Question 20

CASE to avoid divide-by-zero

Accepted Answer

Dividing by zero raises an error in most SQL databases. Wrapping the division in a CASE guards the dangerous branch: when visits is 0, CASE returns NULL without ever evaluating revenue / visits. The short-circuit evaluation of CASE guarantees this - the ELSE branch is only reached when the WHEN condition is FALSE. Returning NULL for undefined ratios is usually more useful than an error, and downstream COALESCE or application logic can handle the NULL as needed.

Question 21

Picking a fallback phone number

Accepted Answer

COALESCE evaluates its arguments left to right and returns the first one that is not NULL. Row 1: mobile is not NULL -> 555-1111. Row 2: mobile is NULL, home is NULL, work is not NULL -> 555-2222. Row 3: mobile is NULL, home is not NULL -> 555-3333. COALESCE(a, b, c) is exactly equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END - it is standard SQL shorthand for that pattern.

Question 22

COALESCE vs ISNULL portability

Accepted Answer

COALESCE is defined by the SQL standard and works in PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. ISNULL is a SQL Server and Sybase proprietary function that takes exactly two arguments and always returns the data type of the first argument. NVL is Oracle-specific, and IFNULL is MySQL/SQLite-specific. When writing portable SQL, always prefer COALESCE over any vendor-specific two-argument NULL replacement function.

Question 23

SUM with CASE 1/0 flag pattern

Accepted Answer

SUM(CASE WHEN condition THEN 1 ELSE 0 END) is a classic conditional count: each matching row contributes 1, each non-matching row contributes 0, and SUM adds them up. Alice and Carol are active (1+1=2), Bob and Dave are inactive (1+1=2). This is equivalent to COUNT(CASE WHEN status='active' THEN 1 END) which exploits the fact that COUNT ignores NULLs - the ELSE 0 vs no ELSE distinction matters only for SUM, not COUNT.

Question 24

What happens to the NULL bonus?

Accepted Answer

CASE WHEN bonus IS NULL THEN 0 ELSE bonus END replaces NULL with 0. Alice has a bonus (5000), so the ELSE returns 5000. Bob has NULL, so the WHEN matches and returns 0. This is equivalent to COALESCE(bonus, 0), which is shorter. Use CASE when you need more complex NULL replacement logic (e.g. different defaults by department).

Question 25

CASE WHEN NULL - will this branch fire?

Accepted Answer

Simple CASE compares using equality (region = NULL internally), and NULL = NULL evaluates to UNKNOWN, not TRUE. Because the WHEN NULL branch is never satisfied, execution falls through to ELSE 'Other'. To catch NULLs in a CASE you must use the searched form: CASE WHEN region IS NULL THEN 'Unknown' ... END. This is one of the most common traps when converting simple CASE to handle NULLs.

CASE Expressions