Beginner-Friendly Topic
Take your time - it's perfectly normal to re-read this topic 2-3 times. Try the interactive code editor below to run code yourself. Use the Q&A section to check your understanding before moving on. You've got this! 🚀
NULL Handling (IS NULL, IS NOT NULL)
NULL represents an unknown or missing value - not zero, not empty string, but truly unknown. NULL has special rules: comparisons with NULL always return NULL (not TRUE or FALSE). Use IS NULL and IS NOT NULL to check for NULL. COALESCE provides fallback values.
NULL Rules in SQL
- NULL = NULL → NULL (not TRUE!) - always use IS NULL
- NULL != NULL → NULL - not FALSE either
- Any arithmetic with NULL returns NULL: 5 + NULL = NULL
- Any comparison with NULL returns NULL: price > NULL = NULL
- IS NULL - tests if a value is NULL
- IS NOT NULL - tests if a value is not NULL
- COALESCE(col, default) - returns first non-NULL value
- NULLIF(a, b) - returns NULL if a = b, otherwise returns a
NULL Examples
-- Find payments not yet made (paid_at is NULL)
SELECT order_id, amount FROM payments WHERE paid_at IS NULL;
-- Find payments that ARE confirmed
SELECT order_id, paid_at FROM payments WHERE paid_at IS NOT NULL;
-- Count rows including NULLs vs excluding
SELECT COUNT(*) AS total_rows FROM payments; -- counts all rows
SELECT COUNT(paid_at) AS confirmed FROM payments; -- ignores NULL paid_at
-- COALESCE: replace NULL with a default
SELECT
order_id,
amount,
COALESCE(payment_method, 'Unknown') AS method
FROM payments;
-- NULLIF: return NULL when value equals something
SELECT NULLIF(stock, 0) AS stock_safe FROM products;
-- Returns NULL instead of 0 (useful to avoid divide-by-zero)
-- NULL in ORDER BY (comes first in DESC, last in ASC by default)
SELECT name, stock FROM products ORDER BY stock ASC NULLS LAST;
-- Beware: NOT IN with NULL
-- WHERE id NOT IN (1, 2, NULL) - returns NOTHING
-- Fix: WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL)Quick Quiz
Tip
Tip
Practice NULL Handling IS NULL IS NOT NULL in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with NULL Handling IS NULL IS NOT NULL is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of NULL Handling IS NULL IS NOT NULL from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- NULL represents an unknown or missing value - not zero, not empty string, but truly unknown.
- NULL = NULL → NULL (not TRUE!) - always use IS NULL
- NULL != NULL → NULL - not FALSE either
- Any arithmetic with NULL returns NULL: 5 + NULL = NULL