CASE WHEN, COALESCE and NULLIF in SQL

Problem

Words instead of codes

On the “My orders” page, the customer sees the status as it is in the database: paid, shipped. The codes are handy for the program, but the customer expects “Paid” and “On its way”.

The website also wants a “cheap, mid-range, expensive” filter. There is no such column in the database, only a price.

The idea

CASE WHEN

CASE gives a different value depending on a condition:

SQLdatabase: shop

SELECT name, price,
       CASE
         WHEN price < 2 THEN 'cheap'
         WHEN price < 10 THEN 'mid-range'
         ELSE 'expensive'
       END AS price_group
FROM products
WHERE category_id IN (4, 8);

The conditions are checked from top to bottom. The first true WHEN gives the value of the THEN next to it, and the rest are not looked at. ELSE is for everything for which nothing is true. END closes the expression, and AS gives it a name.

When you compare one column with several values, there is a shorter form: the column is written once after CASE, and each WHEN holds only the value:

SQLdatabase: shop

SELECT id, status,
       CASE status
         WHEN 'new' THEN 'Received'
         WHEN 'paid' THEN 'Paid'
       END AS status_text
FROM orders
WHERE id BETWEEN 57 AND 59;

Try it

The statuses in words

Show the number, the date and the status in words of every order, in a column called status_text: new is “Received”, paid is “Paid”, shipped is “On its way”, delivered is “Delivered”, cancelled is “Cancelled”.

SQLdatabase: shop

SELECT id, ordered_at, status FROM orders;

Solution

SELECT id, ordered_at,
       CASE status
         WHEN 'new' THEN 'Received'
         WHEN 'paid' THEN 'Paid'
         WHEN 'shipped' THEN 'On its way'
         WHEN 'delivered' THEN 'Delivered'
         WHEN 'cancelled' THEN 'Cancelled'
       END AS status_text
FROM orders;

See it

The expensive ones vanished

A colleague rewrites the price groups with >. Run it and look at the kitchen:

SQLdatabase: shop

SELECT name, price,
       CASE
         WHEN price > 2 THEN 'mid-range'
         WHEN price > 10 THEN 'expensive'
         ELSE 'cheap'
       END AS price_group
FROM products
WHERE category_id IN (4, 8);

1. Why did the frying pan at 24.90 € come out as “mid-range”?Because CASE does not work with fractionsBecause the first true WHEN wins: 24.90 > 2 is true, and “> 10” is never reachedBecause ELSE catches everything

One more thing: if there is no ELSE and no WHEN is true, the result is NULL. The bread gets a note only when it is sold out or running low:

SQLdatabase: shop

SELECT name, stock,
       CASE
         WHEN stock = 0 THEN 'out of stock'
         WHEN stock < 30 THEN 'last few'
       END AS note
FROM products
WHERE category_id = 3;

Sometimes that is exactly what you want. If not, write ELSE.

The idea

COALESCE and NULLIF

COALESCE(a, b, …) returns the first value that is not NULL. That is how you put a word in place of what is missing:

SQLdatabase: shop

SELECT name, COALESCE(city, 'unknown') AS city
FROM customers
WHERE id BETWEEN 10 AND 13;

Sometimes “missing” is stored not as NULL but as empty text. In the two examples below the database has one more customer, Anna, saved through a form that sends '' instead of NULL. COALESCE does not catch her, because empty text is a value:

SQLdatabase: shop

SELECT name, COALESCE(city, 'unknown') AS shown, city IS NULL AS no_city
FROM customers
WHERE id >= 12 AND (city IS NULL OR city = '');

NULLIF(a, b) gives NULL when a equals b, and a otherwise. That way the empty text becomes NULL, and COALESCE catches both customers:

SQLdatabase: shop

SELECT name, COALESCE(NULLIF(city, ''), 'unknown') AS shown
FROM customers
WHERE id >= 12 AND (city IS NULL OR city = '');

More precisely, if you are curious

NULLIF is also used for division: total / NULLIF(count, 0). When count is 0, the divisor becomes NULL and so does the result. In SQLite division by zero gives NULL anyway, but in PostgreSQL it is an error that stops the whole query.

Try it

The line for the courier

Fix the “name, city” line from the lesson on text: when the city is missing, it should say “no city” in its place. The column should be called contact.

SQLdatabase: shop

SELECT name || ', ' || city AS contact FROM customers;

Solution

SELECT name || ', ' || COALESCE(city, 'no city') AS contact FROM customers;

Exercise

Two tasks

1. Next to every product, the website shows the stock in words: “out of stock” at 0, “last few” under 10, otherwise “in stock”. Show the name, the stock and the words in a column called availability.

The stock in words for every product:

SQLdatabase: shop

Solution

SELECT name, stock,
       CASE
         WHEN stock = 0 THEN 'out of stock'
         WHEN stock < 10 THEN 'last few'
         ELSE 'in stock'
       END AS availability
FROM products;

2. The warehouse works in this order: first it packs the paid orders (paid), then looks at the new ones (new), and last checks the shipped ones (shipped). Show the number, status and date of these orders in working order, and within one status the oldest first. With the same date, the smaller number first.

The open orders in the order the warehouse handles them:

SQLdatabase: shop

Solution

SELECT id, status, ordered_at
FROM orders
WHERE status IN ('paid', 'new', 'shipped')
ORDER BY CASE status WHEN 'paid' THEN 1 WHEN 'new' THEN 2 ELSE 3 END, ordered_at, id;

More precisely, if you are curious

SQLite also has IIF(condition, if true, if not): a short CASE with one WHEN and an ELSE. SQL Server has it too, but PostgreSQL does not. CASE is the standard form and works everywhere.

SQLdatabase: shop

SELECT name, stock, IIF(stock = 0, 'out of stock', 'available') AS shown
FROM products
WHERE category_id = 3;

Test

Check yourself

1. What does CASE give when no WHEN is true and there is no ELSE?An errorNULLEmpty text

2. What does COALESCE(NULL, NULL, 'Sofia', 'Varna') return?VarnaSofiaNULL

3. What does NULLIF(city, '') return for a city that is empty text?NULL''An error

4. Where can CASE go?Only in WHEREAnywhere an expression can: in SELECT, WHERE, ORDER BYOnly in SELECT

CASE and missing values

Cookies