Module 2 · Aggregation and Logic · NULL and CASE

NULL and CASE WHEN — missing values and conditional logic

Handle missing values correctly and build business categories and conditional calculations with CASE WHEN.

Beginner40 min

Recommended first

This lesson builds on: SELECT, WHERE and sorting — your first real queries.

Learning objectives

Explain what NULL means and why `= NULL` never works
Use IS NULL, IS NOT NULL and COALESCE correctly
Build categories with CASE WHEN and understand evaluation order
Write conditional calculations (conditional sums and counts)

NULL means "unknown or not applicable"

NULL is not zero and not an empty string. It means there is no value. In stores, close_date is NULL for stores that have not closed; in orders, campaign_id is NULL when no campaign was running.

SQL uses three-valued logic: a comparison can be TRUE, FALSE or UNKNOWN. Any comparison with NULL — even NULL = NULL — is UNKNOWN, and WHERE keeps only TRUE rows.

SELECT
  (SELECT COUNT(*) FROM orders WHERE campaign_id = NULL)  AS using_equals,
  (SELECT COUNT(*) FROM orders WHERE campaign_id IS NULL) AS using_is_null;

Result · 1 row

using_equals	using_is_null
0	27426

= NULL matches nothing. Always use IS NULL / IS NOT NULL.

NULL in calculations and aggregates

Arithmetic with NULL returns NULL: 5 + NULL is NULL.
Aggregates ignore NULLs: COUNT(column) counts non-NULL values, AVG divides by non-NULL values only; COUNT(*) counts rows.

SELECT COUNT(*)            AS all_orders,
       COUNT(campaign_id)  AS orders_in_campaigns,
       COUNT(*) - COUNT(campaign_id) AS orders_outside_campaigns
FROM orders;

Result · 1 row

all_orders	orders_in_campaigns	orders_outside_campaigns
37651	10225	27426

COALESCE: replace NULL with a value

COALESCE(a, b, c) returns the first non-NULL argument:

SELECT store_name,
       COALESCE(close_date, 'still open') AS status_or_close_date,
       COALESCE(size_sqm, 0)             AS size_sqm
FROM stores;

Result · 9 rows

store_name	status_or_close_date	size_sqm
Orion Baku Central	still open	1200
Orion Baku Riverside	still open	850
Orion Ganja	still open	700
Orion Sumqayit	still open	640
Orion Istanbul Kadikoy	still open	1500
Orion Ankara	still open	900
Orion Tbilisi	still open	780
Orion Online	still open	0
Orion Baku Airport	2024-06-30	220

Replacing NULL with 0 when it changes the meaning

COALESCE(size_sqm, 0) is fine for display, but averaging store sizes with the online store counted as 0 m² would understate the average. Replace NULLs only when "no value" truly means zero.

CASE WHEN: conditional logic

CASE returns a value based on conditions — SQL's IF/ELSE:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ELSE default_result
END

Conditions are checked top to bottom; the first TRUE one wins. Without ELSE, unmatched rows get NULL.

Price bands for products:

SELECT product_name, list_price,
       CASE
         WHEN list_price < 20  THEN 'Budget'
         WHEN list_price < 75  THEN 'Mid-range'
         WHEN list_price < 150 THEN 'Premium'
         ELSE 'Luxury'
       END AS price_band
FROM products
ORDER BY list_price
LIMIT 8;

Result · 8 rows

product_name	list_price	price_band
Pasta 1kg Basic	2.55	Budget
Pasta 1kg Plus	3	Budget
Dark Chocolate Basic	3.4	Budget
Dark Chocolate Plus	4	Budget
Pasta 1kg Pro	4.05	Budget
Lip Balm Basic	4.25	Budget
Lip Balm Plus	5	Budget
Green Tea Basic	5.1	Budget

Because conditions are evaluated in order, the second line does not need list_price >= 20 — anything that reached it already failed the first test.

Overlapping or badly ordered conditions

If you put WHEN list_price < 150 THEN 'Premium' first, every cheaper product would also be labelled Premium. Order conditions from most to least specific, or make each condition explicit.

Conditional calculations

Combine CASE with aggregates to compute several metrics in one pass:

SELECT
  COUNT(*)                                              AS orders_2025,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'returned'  THEN 1 ELSE 0 END) AS returned,
  ROUND(100.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) / COUNT(*), 2) AS return_rate_pct
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

Result · 1 row

orders_2025	completed	returned	return_rate_pct
13972	12713	833	5.96

Note 100.0 *: in many databases dividing two integers performs integer division (7 / 2 = 3). Multiplying by a decimal first forces decimal arithmetic.

Conditional aggregation is the analyst's pivot

SUM(CASE WHEN … THEN value END) turns rows into columns — e.g. revenue per channel side-by-side — without needing a PIVOT feature. You will use it constantly.

Practice

How many orders were placed outside any campaign (no campaign_id)? Return one number.

A column bonus contains values 100, 200 and NULL in three rows. What does AVG(bonus) return?

For each payment_method, return the method, the number of orders, and the return rate in % (returned orders ÷ all orders × 100, rounded to 1 decimal). Three columns.

Summary

NULL = no value. Use IS NULL / IS NOT NULL; = NULL never matches.
Aggregates ignore NULLs; COUNT(*) counts rows, COUNT(col) counts non-NULL values.
COALESCE substitutes values — only when the substitution keeps the meaning.
CASE WHEN evaluates top to bottom; combine it with SUM/COUNT for conditional metrics; beware integer division.

← Previous

Aggregations, GROUP BY and HAVING

Joins — combining tables without double counting