Introduction: Realistic Missing Data in Relational Tables
In real-world applications, data is often incomplete. An employee might not have a mobile phone registered yet, or a top executive might not report to any manager. Consider this employee table:
| id | name | manager_id | phone |
|---|---|---|---|
| 1 | Rahul | NULL | 9876543210 |
| 2 | Amit | 1 | NULL |
| 3 | Priya | 1 | 9812345678 |
In row 1, manager_id = NULL means Rahul is the CEO at the top of the hierarchy (not applicable). In row 2, phone = NULLmeans Amit's contact number is missing/unrecorded.
What Is NULL? (State vs. Value)
Remember these 3 core inequalities:
NULL ≠ 0(0 is a known numeric quantity).NULL ≠ ''(An empty string is a known text value with length 0).NULL ≠ FALSE(FALSE is a definite boolean value).
(e.g. Bank balance = $0.00)
(e.g. Middle name left blank)
(e.g. Value never entered)
NULL vs. Empty String vs. Numeric Zero
| Attribute | NULL | Empty String ('') | Numeric Zero (0) |
|---|---|---|---|
| Data Type | Untyped / Special State | Text (VARCHAR/TEXT) | Numeric (INT/DECIMAL) |
| Meaning | Unknown / Missing | Explicitly Blank Text | Explicit Number Zero |
| Equality Test | col IS NULL | col = '' | col = 0 |
| Length / Count | Ignored by COUNT(col) | Length = 0, Counted | Value = 0, Counted |
Why = NULL Doesn't Work
In SQL, asking “Is phone equal to NULL?” is asking “Is phone equal to an unknown value?”. Since the right side is unknown, the result is neither TRUE nor FALSE—it is UNKNOWN.
SELECT * FROM employees WHERE phone = NULL;
SELECT * FROM employees WHERE phone IS NULL;
phone column
Row passes filter
Row dropped
IS NULL and IS NOT NULL Syntax
SQL provides dedicated predicates to filter on missing data:
SELECT name, department
FROM employees
WHERE phone IS NULL;
-- 2. Find employees with verified contact details
SELECT name, phone
FROM employees
WHERE phone IS NOT NULL;
NULL and Comparisons: Three-Valued Logic
Most programming environments use Two-Valued Logic (TRUE / FALSE). SQL uses Three-Valued Logic (TRUE, FALSE, and UNKNOWN).
5 = 5➔TRUE5 = 10➔FALSE5 = NULL➔UNKNOWNNULL = NULL➔UNKNOWN(Two unknowns cannot be assumed equal!)
NULL With AND / OR / NOT Boolean Gates
When UNKNOWN encounters boolean operators in a WHERE clause:
TRUE AND UNKNOWN➔UNKNOWN(Row is dropped)FALSE AND UNKNOWN➔FALSE(Row is dropped)TRUE OR UNKNOWN➔TRUE(Row is kept!)NOT UNKNOWN➔UNKNOWN
NULL and Aggregate Functions: COUNT(*) vs. COUNT(column)
COUNT(column) counts ONLY rows where that column is NOT NULL.
| Aggregate Function | How It Handles NULL | Example on [100, 200, NULL] |
|---|---|---|
COUNT(*) | Counts all rows | 3 |
COUNT(val) | Ignores NULLs | 2 |
SUM(val) | Ignores NULLs | 300 |
AVG(val) | Ignores NULLs (divides by 2, not 3) | 150 |
MIN(val) / MAX(val) | Ignores NULLs | 100 / 200 |
NULL With GROUP BY Bucketing
When grouping by a column that contains missing values, SQL places all NULL records into a single dedicated group:
FROM employees
GROUP BY department;
Classifying NULL With CASE Expressions
You can categorize missing data using CASE WHEN ... IS NULL:
CASE
WHEN phone IS NULL THEN 'No Phone on File'
ELSE phone
END AS contact_info
FROM employees;
The COALESCE() Function: Default Value Fallback
The COALESCE() function returns the first non-NULL expression in its argument list. It is the cleanest way to substitute defaults:
SELECT name, COALESCE(phone, 'Not Available') AS phone
FROM employees;
Input column
NULL check
── NO ➔ Original phone
NULL in Mathematical Calculations
Any arithmetic calculation involving NULL evaluates to NULL (e.g. $80,000 + NULL = NULL). To prevent calculations from zeroing out, wrap nullable columns in COALESCE(col, 0):
SELECT name, salary + bonus AS total_comp FROM employees;
-- ✅ DEFENSIVE: Treats NULL bonus as 0
SELECT name, salary + COALESCE(bonus, 0) AS total_comp FROM employees;
NULL With ORDER BY: Sorting Behaviors
In SQL sorting, NULL values are placed together. Standard SQL engines allow you to control their placement:
ORDER BY bonus DESC NULLS LAST(Places top earners first, NULL bonuses at the bottom).ORDER BY bonus ASC NULLS FIRST(Places missing bonuses at the top).
Synthetic NULLs in LEFT JOIN Results
When a LEFT JOIN finds no match in the right table, it populates all right-table columns with synthetic NULL placeholders. This is normal and signifies “no matching record exists”.
Preserved Left Row
Synthetic Right Output
Safe Outer Output
Common SQL NULL Handling Mistakes
Always use IS NULL; equality comparisons with NULL return UNKNOWN and omit rows.
Forgetting that salary + NULL = NULL. Always wrap nullable math in COALESCE().
COUNT(*) returns total rows; COUNT(col) ignores NULL values.
'' is not NULL. WHERE col IS NULL will NOT catch blank strings.
Practical Query Exercises
name,
salary,
COALESCE(bonus, 0) AS bonus,
salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;
name,
COALESCE(mobile_phone, work_phone, email, 'No Contact Method') AS primary_contact
FROM contacts;
FROM employees
WHERE phone IS NULL;
| id | name | department | phone | Status |
|---|---|---|---|---|
| 2 | Amit Verma | Sales | NULL (Missing) | ✓ Qualified |
| 5 | Rohan Gupta | NULL | NULL (Missing) | ✓ Qualified |
SQL NULL Handling Best Practices
- Always Test With IS NULL / IS NOT NULL: Never use
= NULLor!= NULL. - Wrap Nullable Columns in COALESCE During Math: Protect addition, subtraction, and multiplication from evaluating to NULL.
- Be Mindful of COUNT(column): Remember that column counts omit missing records, while
COUNT(*)tallies total rows. - Use NOT NULL Constraints on Critical Schema Columns: Enforce primary keys, email addresses, and IDs as
NOT NULLat the table creation level.
What You Should Know Now: Checklist
- ✓Definition: NULL represents unknown or missing data; it is not zero or empty text.
- ✓Testing: Always use
IS NULLandIS NOT NULL. - ✓Three-Valued Logic: Comparisons with NULL evaluate to
UNKNOWN. - ✓Aggregates:
COUNT(*)counts all rows;SUM(),AVG(),COUNT(col)omit NULLs. - ✓COALESCE: Safely provides fallback values for missing records and calculations.