SQL CASE WHEN, COALESCE, NULLIF

CASE, COALESCE and NULLIF

CASE picks a value with if/else rules, COALESCE fills in a value where there is NULL, and NULLIF does the opposite: it turns a chosen value into NULL.

Syntax

Order of execution: FROM → WHERE → SELECT → ORDER BY

A CASE in SELECT is worked out for each row. Its WHENs are checked from top to bottom, and the first true one decides the value.

Code
SELECT CASE
         WHEN condition1 THEN value1
         WHEN condition2 THEN value2
         ELSE other_value
       END AS alias,
       CASE column WHEN 'a' THEN value1 WHEN 'b' THEN value2 ELSE other_value END,
       COALESCE(column, fallback),
       NULLIF(column, value)
FROM table_name;

Part	Meaning
`CASE WHEN … THEN …`	searched CASE: any condition per branch
`CASE column WHEN 'a' THEN …`	simple CASE: compares one value with each `WHEN`
`ELSE`	the value when no `WHEN` is true; without it, NULL
`COALESCE(a, b, …)`	the first value that isn't NULL
`NULLIF(a, b)`	NULL when a = b, otherwise a

Examples

Searched CASE

Label rows by ranges or by several conditions.

Code
SELECT product_name, rating,
       CASE
         WHEN rating >= 4.5 THEN 'top rated'
         WHEN rating >= 4.0 THEN 'good'
         ELSE 'needs work'                    -- also unrated products
       END AS rating_label
FROM products;

Order of WHEN branches

Put the narrowest condition first, because the first true WHEN wins.

Code
-- Wrong: every product of 4.5 or more is already 'good', so nothing is 'top rated'
SELECT product_name,
       CASE WHEN rating >= 4.0 THEN 'good' WHEN rating >= 4.5 THEN 'top rated' END AS rating_label
FROM products;

Simple CASE

Translate one column's values into other values.

Code
SELECT order_id, status,
       CASE status
         WHEN 'delivered' THEN 'done'
         WHEN 'shipped' THEN 'on the way'
         ELSE 'not shipped'
       END AS stage
FROM orders;

CASE inside CASE

A THEN or ELSE can hold another CASE, for a decision that depends on an earlier one. One flat CASE with AND often reads more easily, so nest only when it's clearer.

Code
SELECT product_name, is_available, stock,
       CASE
         WHEN is_available THEN
           CASE WHEN stock < 50 THEN 'low stock' ELSE 'in stock' END
         ELSE 'not sold'
       END AS shelf_status
FROM products
ORDER BY product_id;

COALESCE

Show a fallback where a value is missing.

Code
SELECT product_name, COALESCE(rating, 0) AS rating FROM products;
SELECT full_name, COALESCE(date_of_birth, DATE '1900-01-01') AS dob FROM customers;

NULLIF

Hide a value that means "nothing", or avoid dividing by zero.

Code
-- Fails: some products have 0 in stock (MySQL returns NULL instead of an error)
SELECT product_name, price / stock FROM products;
 
-- NULLIF turns 0 into NULL, and dividing by NULL gives NULL
SELECT product_name, ROUND(price / NULLIF(stock, 0), 2) AS price_per_unit FROM products;

Remember

The first true WHEN wins: put narrower conditions first.
Without ELSE, rows that match no WHEN get NULL.
COALESCE replaces NULLs; NULLIF creates them.
COALESCE needs values of the same type: COALESCE(rating, 'none') fails on a number (MySQL converts instead).

Practice

CASE, COALESCE and NULLIF

CASE picks a value with if/else rules, COALESCE fills in a value where there is NULL, and NULLIF does the opposite: it turns a chosen value into NULL.

Syntax

Order of execution: FROM → WHERE → SELECT → ORDER BY

A CASE in SELECT is worked out for each row. Its WHENs are checked from top to bottom, and the first true one decides the value.

Code
SELECT CASE
         WHEN condition1 THEN value1
         WHEN condition2 THEN value2
         ELSE other_value
       END AS alias,
       CASE column WHEN 'a' THEN value1 WHEN 'b' THEN value2 ELSE other_value END,
       COALESCE(column, fallback),
       NULLIF(column, value)
FROM table_name;

Part	Meaning
`CASE WHEN … THEN …`	searched CASE: any condition per branch
`CASE column WHEN 'a' THEN …`	simple CASE: compares one value with each `WHEN`
`ELSE`	the value when no `WHEN` is true; without it, NULL
`COALESCE(a, b, …)`	the first value that isn't NULL
`NULLIF(a, b)`	NULL when a = b, otherwise a

Examples

Searched CASE

Label rows by ranges or by several conditions.

Code
SELECT product_name, rating,
       CASE
         WHEN rating >= 4.5 THEN 'top rated'
         WHEN rating >= 4.0 THEN 'good'
         ELSE 'needs work'                    -- also unrated products
       END AS rating_label
FROM products;

Order of WHEN branches

Put the narrowest condition first, because the first true WHEN wins.

Code
-- Wrong: every product of 4.5 or more is already 'good', so nothing is 'top rated'
SELECT product_name,
       CASE WHEN rating >= 4.0 THEN 'good' WHEN rating >= 4.5 THEN 'top rated' END AS rating_label
FROM products;

Simple CASE

Translate one column's values into other values.

Code
SELECT order_id, status,
       CASE status
         WHEN 'delivered' THEN 'done'
         WHEN 'shipped' THEN 'on the way'
         ELSE 'not shipped'
       END AS stage
FROM orders;

CASE inside CASE

A THEN or ELSE can hold another CASE, for a decision that depends on an earlier one. One flat CASE with AND often reads more easily, so nest only when it's clearer.

Code
SELECT product_name, is_available, stock,
       CASE
         WHEN is_available THEN
           CASE WHEN stock < 50 THEN 'low stock' ELSE 'in stock' END
         ELSE 'not sold'
       END AS shelf_status
FROM products
ORDER BY product_id;

COALESCE

Show a fallback where a value is missing.

Code
SELECT product_name, COALESCE(rating, 0) AS rating FROM products;
SELECT full_name, COALESCE(date_of_birth, DATE '1900-01-01') AS dob FROM customers;

NULLIF

Hide a value that means "nothing", or avoid dividing by zero.

Code
-- Fails: some products have 0 in stock (MySQL returns NULL instead of an error)
SELECT product_name, price / stock FROM products;
 
-- NULLIF turns 0 into NULL, and dividing by NULL gives NULL
SELECT product_name, ROUND(price / NULLIF(stock, 0), 2) AS price_per_unit FROM products;

Remember

The first true WHEN wins: put narrower conditions first.
Without ELSE, rows that match no WHEN get NULL.
COALESCE replaces NULLs; NULLIF creates them.
COALESCE needs values of the same type: COALESCE(rating, 'none') fails on a number (MySQL converts instead).

SQLMaking Decisions in SQL

Syntax

Examples

Searched CASE

Order of WHEN branches

Simple CASE

CASE inside CASE

COALESCE

NULLIF

Remember

Practice

SQLMaking Decisions in SQL

Syntax

Examples

Searched CASE

Order of WHEN branches

Simple CASE

CASE inside CASE

COALESCE

NULLIF

Remember

Practice