CASE, COALESCE and NULLIF

Part of the free LogicWiz SQL & Databases course, Module 3: Functions and expressions. The queries and practice run in the app on real Postgres.

Meher wants the menu to say Budget, Regular or Premium next to each dish, and New where a restaurant has no rating yet. None of those words are stored anywhere. SQL can make them up as it reads each row.

You'll learn to

• Turn values into labels with CASE WHEN … THEN … ELSE … END
• Order the WHEN branches so the right one wins
• Replace NULL with COALESCE and avoid divide-by-zero with NULLIF

Learn

CASE: if-else inside a query

CASE checks conditions for each row and gives back one value:

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ELSE value3
END

It is an expression, like price * 2, so it can go in SELECT, WHERE, ORDER BY, and later inside COUNT and SUM. Always close it with END, and give the new column a name with AS.

Price bands for the menu

SELECT name,
  price,
  CASE
    WHEN price >= 300 THEN 'Premium'
    WHEN price >= 100 THEN 'Regular'
    ELSE 'Budget'
  END AS band
FROM menu_items
ORDER BY price DESC;

Butter Chicken (₹450) passes the first WHEN and gets Premium. Masala Dosa (₹120) fails it, passes the second, and gets Regular.

The first true WHEN wins

Postgres tries the WHEN branches from the top and stops at the first one that is true. The rest are never checked.

So put the strictest condition first. If price >= 100 came first, a ₹450 dish would stop there and be called Regular.

If no WHEN is true and there is no ELSE, the result is NULL. Add an ELSE unless a NULL is what you want.

The short form: CASE column WHEN value

SELECT order_id,
  status,
  CASE status
    WHEN 'delivered' THEN 'Done'
    WHEN 'cancelled' THEN 'Refund'
    ELSE 'Track it'
  END AS next_step
FROM orders
WHERE order_id >= 1018;

When every branch checks one column for equality, write the column once after CASE. It only does =, so it cannot test ranges, and it cannot match NULL.

COALESCE: the first value that is not NULL

SELECT name,
  rating,
  COALESCE(rating::text, 'New') AS shown_rating
FROM restaurants;

COALESCE(a, b, c) gives back the first argument that is not NULL. Pizza Corner has no rating, so it shows New.

Every argument must be the same type

SELECT name, COALESCE(rating, 'New') AS shown_rating
FROM restaurants;

rating is a number, so Postgres tries to read 'New' as a number and fails. Cast the number to text first: COALESCE(rating::text, 'New').

NULLIF: turn a value into NULL

SELECT order_id,
  delivery_fee,
  NULLIF(delivery_fee, 0) AS fee_or_null,
  500 / NULLIF(delivery_fee, 0) AS orders_per_500
FROM orders
WHERE order_id <= 1005;

NULLIF(a, b) gives NULL when a = b, otherwise a. Dividing by zero is an error that stops the whole query, but dividing by NULL just gives NULL. So x / NULLIF(y, 0) is the safe way to divide.

Interviews ask: bucket salaries or marks into grades with CASE, swap two values in one UPDATE (SET gender = CASE gender WHEN 'M' THEN 'F' ELSE 'M' END), and avoid divide-by-zero with NULLIF. University exams like asking what CASE gives when nothing matches: NULL.

In one line

CASE WHEN … THEN … ELSE … END labels rows (first true WHEN wins); COALESCE fills NULLs; x / NULLIF(y, 0) divides safely.