CASE, COALESCE and computing a column that does not exist

SELECT can compute, not just retrieve

Every column in a SELECT list can be an expression. This is where a query stops fetching data and starts producing an answer.

SELECT
  full_name,
  amount,
  amount * 0.18                        AS tax,
  round(amount / 12.0, 2)              AS monthly,
  upper(left(full_name, 1))            AS initial,
  paid_on + interval '30 days'         AS due
FROM payments JOIN students ON ...

AS names the result. Without a name you get something like ?column?, which is unusable downstream.

CASE: an if-statement inside a query

SELECT
  full_name,
  amount,
  CASE
    WHEN amount >= 20000 THEN 'large'
    WHEN amount >= 5000  THEN 'medium'
    ELSE 'small'
  END AS size_band
FROM payments JOIN students ON students.id = payments.student_id;

Two mechanics decide everything CASE does.

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.

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.

Note 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.

The short forms

COALESCE(a, b, c) returns the first argument that is not NULL. It is the standard way to supply a default:

COALESCE(nickname, full_name, 'Unknown')

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:

SELECT successes::numeric / NULLIF(attempts, 0) AS rate FROM funnels;

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.

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.

CASE inside an aggregate, which is the powerful trick

This deserves its own mention because it unlocks a lot:

SELECT
  city,
  count(*)                                          AS all_orders,
  count(*) FILTER (WHERE status = 'refunded')       AS refunded,
  sum(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_value
FROM orders
GROUP BY city;

One 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.

Where an expression costs you

Computing in SELECT is free-ish; computing in WHERE can be expensive:

WHERE upper(city) = 'LAGOS'          -- cannot use an index on city
WHERE city ILIKE 'lagos'             -- also cannot, usually
WHERE city IN ('Lagos','lagos','LAGOS')  -- can

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.

Readability, briefly

A CASE with nine branches nested inside another CASE is where queries go to die. Two ways out.

Give the logic a name with a CTE:

WITH banded AS (
  SELECT id, amount,
         CASE WHEN amount >= 20000 THEN 'large' ... END AS band
  FROM payments
)
SELECT band, count(*) FROM banded GROUP BY band;

Or, 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.