Working With Null Values

Who this is for

Business Analysts who run queries, build KPIs, and prepare reports where missing data can skew sums, averages, counts, and filters.

Prerequisites

• Basic SELECT, FROM, WHERE, GROUP BY
• Comfort with reading simple result sets

Why this matters

Real datasets often have missing values: no discount code, unknown region, unshipped orders, or amounts to be filled later. If you treat NULL like zero or an empty string, you’ll miscount, mis-sum, or filter out important records. Knowing how NULL behaves avoids wrong KPIs and misleading dashboards.

• KPI accuracy: COUNT, SUM, AVG handle NULL differently.
• Filtering: WHERE amount = NULL never works; you need IS NULL.
• Reporting: Replace NULL with readable defaults only in presentation, not raw logic.

Concept explained simply

NULL means “unknown/not applicable/missing,” not zero and not an empty string.

• Comparison: Any comparison with NULL yields UNKNOWN (not TRUE, not FALSE).
• Filtering: Use IS NULL or IS NOT NULL.
• Aggregates: SUM/AVG ignore NULLs; COUNT(*) counts rows; COUNT(col) counts non-NULL values.
• Replacement: COALESCE(x, default) returns the first non-NULL value in its arguments.

Mental model

Think of NULL as a blank sticky note. You cannot say if it’s equal to anything, because you don’t know what’s written there. You must explicitly test if the note is blank (IS NULL) or choose a safe fallback (COALESCE) when reading it.

Sample data for examples and exercises

-- Customers
CREATE TABLE customers (
  customer_id INT,
  name TEXT,
  region TEXT
);
INSERT INTO customers VALUES
  (101, 'A Corp', 'North'),
  (102, 'B LLC', NULL),
  (103, 'C Inc', 'West'),
  (104, 'D Co', 'North');

-- Orders
CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  amount DECIMAL(10,2),
  discount_code TEXT,
  shipped_at DATE
);
INSERT INTO orders VALUES
  (1, 101, 120.00, 'SAVE10', '2024-05-10'),
  (2, 102,  80.00, NULL,     '2024-05-12'),
  (3, 103,  NULL,  'WELCOME', NULL),
  (4, 101, 200.00, NULL,     '2024-05-15'),
  (5, 104,  50.00, NULL,     '2024-05-16');

Worked examples

Example 1 — Counting rows vs. counting values

-- How many orders total vs. how many have a discount code?
SELECT COUNT(*) AS total_orders,
       COUNT(discount_code) AS orders_with_code
FROM orders;

COUNT(*) counts all rows (5). COUNT(discount_code) counts only non-NULL codes (2).

Example 2 — Filtering NULL correctly

-- Find orders without a discount code
SELECT order_id
FROM orders
WHERE discount_code IS NULL;

-- Incorrect (returns no rows): WHERE discount_code = NULL

Use IS NULL/IS NOT NULL. Any comparison like = NULL is UNKNOWN and filters out.

Example 3 — Replacing NULL with COALESCE

-- Use 0 when amount is missing
SELECT order_id,
       COALESCE(amount, 0) AS clean_amount
FROM orders;

-- Aggregate with a safe default
SELECT customer_id,
       SUM(COALESCE(amount, 0)) AS total_clean_amount
FROM orders
GROUP BY customer_id;

COALESCE picks the first non-NULL argument.

Example 4 — NULLIF to avoid divide-by-zero or to normalize placeholders

-- Treat empty string discount_code as NULL (if your data mixes both)
SELECT order_id,
       NULLIF(discount_code, '') AS normalized_code
FROM orders;

NULLIF(x, y) returns NULL if x = y, otherwise x.

Common mistakes (and how to self-check)

• Using = NULL instead of IS NULL. Self-check: Run a query you expect to return rows; if it returns none, replace with IS NULL.
• Replacing NULLs too early. Self-check: Aggregate first, then decide presentation defaults. Replacing before joins can change match behavior.
• Assuming NULL = 0 or '' in business logic. Self-check: Explicitly confirm with data owners what NULL means (unknown vs not applicable).
• Forgetting how COUNT behaves. Self-check: Compare COUNT(*), COUNT(col), and SUM(col IS NULL::int) where supported; they should reconcile.

Exercises — practice now

Use the provided tables (customers, orders). You can copy the sample data into your SQL environment.

Exercise 1 — Find missing discount codes

List order_id for orders where discount_code is missing.

• Expected order_ids: 2, 4, 5

Exercise 2 — Null-safe revenue by customer

For each customer_id, compute total_clean_amount where amount defaults to 0 when NULL.

• Expected: 101 → 320.00; 102 → 80.00; 103 → 0.00; 104 → 50.00

Checklist before you move on

• I can explain why = NULL does not work
• I know how COUNT(*), COUNT(col) differ
• I can use COALESCE in SELECT and aggregates
• I can find and present NULLs clearly in reports

Mini challenge

Create a query that returns, per region, three columns: total_orders, orders_with_code, and orders_without_code. Treat customers with NULL region as 'Unknown' only in the final SELECT output.

SELECT COALESCE(c.region, 'Unknown') AS region,
       COUNT(*) AS total_orders,
       COUNT(o.discount_code) AS orders_with_code,
       COUNT(*) - COUNT(o.discount_code) AS orders_without_code
FROM orders o
JOIN customers c USING (customer_id)
GROUP BY COALESCE(c.region, 'Unknown')
ORDER BY region;

Learning path

• Today: NULL basics (this lesson) + exercises
• Next: Joins and how NULLs appear from LEFT/RIGHT JOIN
• Then: CASE expressions for conditional defaults
• Finally: Reporting conventions for displaying missing values

Practical projects

• Data Quality Snapshot: Build a query that counts NULLs per key column across 3–5 tables.
• Null-Safe KPI: Produce weekly revenue where missing amounts default to 0, clearly labeled in the output.
• Join Diagnostics: Compare metrics before/after LEFT JOIN to see how NULLs appear in the joined table.

Next steps

• Re-run KPIs you’ve built before and check whether NULL handling changes the numbers.
• Document which columns can be NULL and what they mean in your domain.

Quick Test is available to everyone; only logged-in users have their progress saved.

Quick Test