Loading…
Loading…
SQL tutorial · Beginner
⏱️ 10 mins read
NULL represents missing or unknown data — not zero, not empty string. NULL propagates: any arithmetic with NULL returns NULL. Comparisons with NULL always return UNKNOWN. Use COALESCE to replace NULL with a default. Use NULLIF to return NULL when two values are equal.
WHERE col IS NULL
WHERE col IS NOT NULL
COALESCE(col, default_value)
NULLIF(col, value)-- Check for NULL (NEVER use = NULL)
SELECT id, name FROM employees WHERE department_id IS NULL; -- Edsger
SELECT id, status FROM orders WHERE shipped_at IS NULL; -- pending orders
-- Replace NULL with a default in the output
SELECT id, COALESCE(shipped_at, order_date) AS effective_date
FROM orders;
-- NULLIF: returns NULL if items = 0 (avoids division by zero)
SELECT id, total / NULLIF(items, 0) AS avg_item_price
FROM orders;
-- COUNT(*) vs COUNT(col) — may differ!
SELECT COUNT(*), COUNT(shipped_at) FROM orders;| id | name | department_id | salary | hire_date |
|---|---|---|---|---|
| 1 | Ada | 1 | 145000 | 2021-03-01 |
| 2 | Grace | 1 | 115000 | 2022-06-13 |
| 3 | Alan | 2 | 92000 | 2020-11-02 |
| 4 | Edsger | NULL | 78000 | 2023-08-19 |
| 5 | Barbara | 3 | 64000 | 2024-01-08 |
| id | customer_id | status | total | items | order_date | shipped_at |
|---|---|---|---|---|---|---|
| 1 | 1 | shipped | 240.00 | 3 | 2024-01-15 | 2024-01-18 |
| 2 | 2 | shipped | 89.90 | 1 | 2024-02-03 | 2024-02-07 |
| 3 | 1 | pending | 430.00 | 2 | 2024-03-11 | NULL |
| 4 | 3 | shipped | 59.50 | 1 | 2024-04-02 | 2024-04-05 |
| 5 | 4 | cancelled | 120.00 | 0 | 2024-05-20 | NULL |
There is no 'NULL value': NULL marks the absence of one. It is not zero, not an empty string, and not equal to anything — including itself. Any arithmetic with NULL yields NULL, and any comparison with NULL yields UNKNOWN, never TRUE or FALSE.
-- Pending orders have shipped_at = NULL:
SELECT id, total, shipped_at FROM orders WHERE status = 'pending';
-- None of these behave the way beginners expect:
SELECT id FROM orders WHERE shipped_at = NULL; -- WRONG: always empty
SELECT id FROM orders WHERE shipped_at IS NULL; -- RIGHT
-- Not-equal is bitten too — pending orders fail BOTH branches:
SELECT id FROM orders WHERE shipped_at < '2024-06-01'; -- pending rows vanish
SELECT id FROM orders
WHERE shipped_at < '2024-06-01' OR shipped_at IS NULL; -- explicit is safeSUM, AVG, MIN, and MAX skip NULL rows entirely; COUNT(*) counts rows while COUNT(col) counts non-NULL values. That is why two 'counts' on the same table can disagree — and why AVG can come out higher than expected when the missing values are exactly the interesting ones.
SELECT
COUNT(*) AS all_orders, -- every row
COUNT(shipped_at) AS shipped_orders, -- pending orders excluded silently
AVG(total) AS avg_total
FROM orders;
-- Make the split explicit instead of implicit:
SELECT status, COUNT(*) AS n
FROM orders
GROUP BY status; -- Topic 9 formalizes this patternCOALESCE(a, b, c) returns the first non-NULL argument; NULLIF(a, b) returns NULL when a = b. Both are shorthands for CASE expressions (Topic 11) — knowing the equivalence tells you exactly how they behave around three-valued logic.
-- Display a placeholder without changing stored data:
SELECT id, COALESCE(shipped_at, order_date) AS effective_date
FROM orders;
-- Guard against division by zero (items = 0 → NULL, not an error):
SELECT id, total / NULLIF(items, 0) AS avg_item_price
FROM orders;
-- NULLIF(a, b) is exactly: CASE WHEN a = b THEN NULL ELSE a ENDCOUNT(*) counts ALL rows including NULLs. COUNT(shipped_at) ignores NULL shipping dates — the two can return different numbers. Also: NULL + 5 = NULL. NULL = NULL is UNKNOWN (not TRUE). This is why WHERE col = NULL never matches.
This is a frequent gotcha question. Be ready to explain three-valued logic in SQL (TRUE, FALSE, UNKNOWN). Mention that NOT IN with a subquery containing NULLs always returns empty — a classic bug.
1. Which test correctly finds orders that have not shipped yet?
2. What does NULL + 5 evaluate to?
3. SELECT COUNT(*), COUNT(shipped_at) FROM orders — what does ShopCo return?
4. What does COALESCE(shipped_at, order_date) do?
5. x NOT IN (SELECT customer_id FROM orders) returns nothing. Why?
6. Is NULL equal to NULL?
Return the id of each transaction that has no category, labelling its category 'Uncategorized'.
⚡ Solve it in the SQL playground →x NOT IN (1, 2, NULL) is evaluated as x != 1 AND x != 2 AND x != NULL — and that last comparison is UNKNOWN, so the whole AND-chain can never be TRUE. It is one of the most famous SQL traps; the fix is NOT EXISTS or filtering NULLs out first (Topic 16).
Sometimes — but they are not interchangeable. A default says 'we know the value and it is this'; NULL says 'we do not know (or it does not apply)'. AVG(total) correctly ignores unknowns but would happily include a fake default of 0 and drag the average down. Model the meaning first, then pick.