{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"case-expressions","title":"CASE, COALESCE and computing a column that does not exist","url":"https://addaly.com/learn/data-and-sql/case-expressions","position":8,"of":66,"minutes":7,"answer":"CASE tests its branches strictly top to bottom and returns the first match, so the order you write the conditions in is part of the logic, not a matter of style.","keyIdea":"CASE tests its branches strictly top to bottom and returns the first match, so the order you write the conditions in is part of the logic, not a matter of style."},"body":{"format":"markdown","text":"## SELECT can compute, not just retrieve\n\nEvery column in a SELECT list can be an expression. This is where a query stops fetching data and starts producing an answer.\n\n```sql\nSELECT\n  full_name,\n  amount,\n  amount * 0.18                        AS tax,\n  round(amount / 12.0, 2)              AS monthly,\n  upper(left(full_name, 1))            AS initial,\n  paid_on + interval '30 days'         AS due\nFROM payments JOIN students ON ...\n```\n\n`AS` names the result. Without a name you get something like `?column?`, which is unusable downstream.\n\n## CASE: an if-statement inside a query\n\n```sql\nSELECT\n  full_name,\n  amount,\n  CASE\n    WHEN amount >= 20000 THEN 'large'\n    WHEN amount >= 5000  THEN 'medium'\n    ELSE 'small'\n  END AS size_band\nFROM payments JOIN students ON students.id = payments.student_id;\n```\n\nTwo mechanics decide everything CASE does.\n\n**It stops at the first true branch.** There is no fall-through. So the order of the WHENs is the logic. Reverse the two above and every payment over 20,000 is labelled `medium`, because `amount >= 5000` was tested first and matched. This is the single most common CASE bug, and it produces a perfectly plausible-looking result.\n\n**A missing ELSE means NULL.** Leave out the ELSE and any row matching nothing gets NULL rather than an error. Then a later `WHERE size_band <> 'small'` drops those rows entirely, for reasons explained in the NULL lesson. Write the ELSE, even when you are sure it cannot be reached, especially then.\n\nNote also that every branch must return the same type. `THEN 0 ... ELSE 'none'` will be rejected, or worse, silently coerced in a database that is trying to be helpful.\n\n## The short forms\n\n`COALESCE(a, b, c)` returns the first argument that is not NULL. It is the standard way to supply a default:\n\n```sql\nCOALESCE(nickname, full_name, 'Unknown')\n```\n\n`NULLIF(a, b)` returns NULL when the two are equal. Its main use is defusing division by zero, which is an error rather than a NULL in most databases:\n\n```sql\nSELECT successes::numeric / NULLIF(attempts, 0) AS rate FROM funnels;\n```\n\nZero attempts now yields NULL (\"we cannot say\") instead of aborting the whole query. That is usually what you want, and it is honest: there is no rate.\n\n`GREATEST(a, b)` and `LEAST(a, b)` compare values across a row, unlike MAX and MIN which compare down a column. Worth knowing because the confusion between the two is common.\n\n## CASE inside an aggregate, which is the powerful trick\n\nThis deserves its own mention because it unlocks a lot:\n\n```sql\nSELECT\n  city,\n  count(*)                                          AS all_orders,\n  count(*) FILTER (WHERE status = 'refunded')       AS refunded,\n  sum(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_value\nFROM orders\nGROUP BY city;\n```\n\nOne pass over the table producing several differently-filtered counts side by side. The `FILTER` clause is standard SQL and supported by Postgres and SQLite; `sum(CASE ...)` is the portable form that works everywhere including MySQL and older engines. The aggregation module goes much further with this.\n\n## Where an expression costs you\n\nComputing in SELECT is free-ish; computing in WHERE can be expensive:\n\n```sql\nWHERE upper(city) = 'LAGOS'          -- cannot use an index on city\nWHERE city ILIKE 'lagos'             -- also cannot, usually\nWHERE city IN ('Lagos','lagos','LAGOS')  -- can\n```\n\nWrapping a column in a function hides its stored value from the index, as the index lesson explains in detail. The clean answer is usually to fix the data once (store the city normalised) rather than to normalise it on every read forever.\n\n## Readability, briefly\n\nA CASE with nine branches nested inside another CASE is where queries go to die. Two ways out.\n\nGive the logic a name with a CTE:\n\n```sql\nWITH banded AS (\n  SELECT id, amount,\n         CASE WHEN amount >= 20000 THEN 'large' ... END AS band\n  FROM payments\n)\nSELECT band, count(*) FROM banded GROUP BY band;\n```\n\nOr, when the bands are business rules that change, put them in a small table and join to it. A `band_thresholds` table with three rows lets someone change the definition of \"large\" without editing SQL, and, more importantly, makes the definition visible to people who cannot read SQL at all. That last point is worth more than it sounds."},"check":{"question":"A query labels orders with `CASE WHEN amount > 1000 THEN 'high' WHEN amount > 5000 THEN 'vip' END`. What does it produce for an order of 7,500, and for an order of exactly 1,000?","options":[{"text":"Orders over 5000 get both labels, so they appear twice in a grouped count.","correct":false,"tempting":"CASE returns exactly one value per row; it cannot emit two labels for the same row."},{"text":"Orders over 5000 are labelled 'vip', because the more specific condition wins, and orders of exactly 1000 are labelled 'high' since the boundary is inclusive.","correct":false,"tempting":"Assumes CASE picks the best-matching branch the way some rule engines do; it evaluates strictly top to bottom."},{"text":"Orders of exactly 1000 are labelled 'high' and everything below is NULL.","correct":false,"tempting":"Gets the boundary wrong (`> 1000` excludes 1000) and misses the larger problem with the branch order."},{"text":"Orders over 5000 are labelled 'high', because that branch is tested first and matches; orders of 1000 or less are NULL.","correct":true}]},"prev":{"slug":"sorting-limits-and-ties","title":"Sorting, limits, and the rows that move between runs","url":"https://addaly.com/learn/data-and-sql/sorting-limits-and-ties"},"next":{"slug":"reading-an-unfamiliar-schema","title":"Reading a database somebody else built","url":"https://addaly.com/learn/data-and-sql/reading-an-unfamiliar-schema"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'CASE, COALESCE and computing a column that does not exist', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/case-expressions (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"CASE, COALESCE and computing a column that does not exist","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":1,"slug":"tables-types-and-the-question","title":"Tables, types and the question you are actually asking"},"answer":null,"keyIdea":"CASE tests its branches strictly top to bottom and returns the first match, so the order you write the conditions in is part of the logic, not a matter of style.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"select-can-compute-not-just-retrieve","runs":[{"text":"SELECT can compute, not just retrieve"}]},{"t":"p","runs":[{"text":"Every column in a SELECT list can be an expression. This is where a query stops fetching data and starts producing an answer."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  full_name,\n  amount,\n  amount * 0.18                        AS tax,\n  round(amount / 12.0, 2)              AS monthly,\n  upper(left(full_name, 1))            AS initial,\n  paid_on + interval '30 days'         AS due\nFROM payments JOIN students ON ..."},{"t":"p","runs":[{"text":"AS","code":true},{"text":" names the result. Without a name you get something like "},{"text":"?column?","code":true},{"text":", which is unusable downstream."}]},{"t":"h2","id":"case-an-if-statement-inside-a-query","runs":[{"text":"CASE: an if-statement inside a query"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  full_name,\n  amount,\n  CASE\n    WHEN amount >= 20000 THEN 'large'\n    WHEN amount >= 5000  THEN 'medium'\n    ELSE 'small'\n  END AS size_band\nFROM payments JOIN students ON students.id = payments.student_id;"},{"t":"p","runs":[{"text":"Two mechanics decide everything CASE does."}]},{"t":"p","runs":[{"text":"It stops at the first true branch.","b":true},{"text":" There is no fall-through. So the order of the WHENs is the logic. Reverse the two above and every payment over 20,000 is labelled "},{"text":"medium","code":true},{"text":", because "},{"text":"amount >= 5000","code":true},{"text":" was tested first and matched. This is the single most common CASE bug, and it produces a perfectly plausible-looking result."}]},{"t":"p","runs":[{"text":"A missing ELSE means NULL.","b":true},{"text":" Leave out the ELSE and any row matching nothing gets NULL rather than an error. Then a later "},{"text":"WHERE size_band <> 'small'","code":true},{"text":" drops those rows entirely, for reasons explained in the NULL lesson. Write the ELSE, even when you are sure it cannot be reached, especially then."}]},{"t":"p","runs":[{"text":"Note also that every branch must return the same type. "},{"text":"THEN 0 ... ELSE 'none'","code":true},{"text":" will be rejected, or worse, silently coerced in a database that is trying to be helpful."}]},{"t":"h2","id":"the-short-forms","runs":[{"text":"The short forms"}]},{"t":"p","runs":[{"text":"COALESCE(a, b, c)","code":true},{"text":" returns the first argument that is not NULL. It is the standard way to supply a default:"}]},{"t":"code","lang":"sql","label":"SQL","text":"COALESCE(nickname, full_name, 'Unknown')"},{"t":"p","runs":[{"text":"NULLIF(a, b)","code":true},{"text":" returns NULL when the two are equal. Its main use is defusing division by zero, which is an error rather than a NULL in most databases:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT successes::numeric / NULLIF(attempts, 0) AS rate FROM funnels;"},{"t":"p","runs":[{"text":"Zero attempts now yields NULL (“we cannot say”) instead of aborting the whole query. That is usually what you want, and it is honest: there is no rate."}]},{"t":"p","runs":[{"text":"GREATEST(a, b)","code":true},{"text":" and "},{"text":"LEAST(a, b)","code":true},{"text":" compare values across a row, unlike MAX and MIN which compare down a column. Worth knowing because the confusion between the two is common."}]},{"t":"h2","id":"case-inside-an-aggregate-which-is-the-powerful-trick","runs":[{"text":"CASE inside an aggregate, which is the powerful trick"}]},{"t":"p","runs":[{"text":"This deserves its own mention because it unlocks a lot:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT\n  city,\n  count(*)                                          AS all_orders,\n  count(*) FILTER (WHERE status = 'refunded')       AS refunded,\n  sum(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_value\nFROM orders\nGROUP BY city;"},{"t":"p","runs":[{"text":"One pass over the table producing several differently-filtered counts side by side. The "},{"text":"FILTER","code":true},{"text":" clause is standard SQL and supported by Postgres and SQLite; "},{"text":"sum(CASE ...)","code":true},{"text":" is the portable form that works everywhere including MySQL and older engines. The aggregation module goes much further with this."}]},{"t":"h2","id":"where-an-expression-costs-you","runs":[{"text":"Where an expression costs you"}]},{"t":"p","runs":[{"text":"Computing in SELECT is free-ish; computing in WHERE can be expensive:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WHERE upper(city) = 'LAGOS'          -- cannot use an index on city\nWHERE city ILIKE 'lagos'             -- also cannot, usually\nWHERE city IN ('Lagos','lagos','LAGOS')  -- can"},{"t":"p","runs":[{"text":"Wrapping a column in a function hides its stored value from the index, as the index lesson explains in detail. The clean answer is usually to fix the data once (store the city normalised) rather than to normalise it on every read forever."}]},{"t":"h2","id":"readability-briefly","runs":[{"text":"Readability, briefly"}]},{"t":"p","runs":[{"text":"A CASE with nine branches nested inside another CASE is where queries go to die. Two ways out."}]},{"t":"p","runs":[{"text":"Give the logic a name with a CTE:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH banded AS (\n  SELECT id, amount,\n         CASE WHEN amount >= 20000 THEN 'large' ... END AS band\n  FROM payments\n)\nSELECT band, count(*) FROM banded GROUP BY band;"},{"t":"p","runs":[{"text":"Or, when the bands are business rules that change, put them in a small table and join to it. A "},{"text":"band_thresholds","code":true},{"text":" table with three rows lets someone change the definition of “large” without editing SQL, and, more importantly, makes the definition visible to people who cannot read SQL at all. That last point is worth more than it sounds."}]}],"check":{"question":[{"text":"A query labels orders with "},{"text":"CASE WHEN amount > 1000 THEN 'high' WHEN amount > 5000 THEN 'vip' END","code":true},{"text":". What does it produce for an order of 7,500, and for an order of exactly 1,000?"}],"options":[{"text":[{"text":"Orders over 5000 get both labels, so they appear twice in a grouped count."}],"correct":false,"tempting":[{"text":"CASE returns exactly one value per row; it cannot emit two labels for the same row."}]},{"text":[{"text":"Orders over 5000 are labelled ‘vip’, because the more specific condition wins, and orders of exactly 1000 are labelled ‘high’ since the boundary is inclusive."}],"correct":false,"tempting":[{"text":"Assumes CASE picks the best-matching branch the way some rule engines do; it evaluates strictly top to bottom."}]},{"text":[{"text":"Orders of exactly 1000 are labelled ‘high’ and everything below is NULL."}],"correct":false,"tempting":[{"text":"Gets the boundary wrong ("},{"text":"> 1000","code":true},{"text":" excludes 1000) and misses the larger problem with the branch order."}]},{"text":[{"text":"Orders over 5000 are labelled ‘high’, because that branch is tested first and matches; orders of 1000 or less are NULL."}],"correct":true,"tempting":null}],"order":[2,3,0,1],"why":null},"prev":{"slug":"sorting-limits-and-ties","title":"Sorting, limits, and the rows that move between runs","minutes":7},"next":{"slug":"reading-an-unfamiliar-schema","title":"Reading a database somebody else built","minutes":9}}}