SQL NULL Handling – Guided Study Workspace

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)

Crucial Concept

NULL is not a value. It is a special database state indicating the absence of a value (missing, unknown, or not applicable).

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).

Diagram 1: NULL vs Zero vs Empty String

Known Numeric Zero
(e.g. Bank balance = $0.00)

'' (Blank)

Known Empty Text
(e.g. Middle name left blank)

NULL

Missing / Unknown
(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.

-- ❌ FAILS: Always returns 0 rows because UNKNOWN is falsy in WHERE!
SELECT * FROM employees WHERE phone = NULL;

-- ✅ CORRECT: Tests for the absence of a value
SELECT * FROM employees WHERE phone IS NULL;

Diagram 2: Testing Missing Data with IS NULL

Row Checked
phone column

── IS NULL? ──▶

YES ➔ Include Row
Row passes filter

── NO ──▶

NO ➔ Exclude Row
Row dropped

IS NULL and IS NOT NULL Syntax

SQL provides dedicated predicates to filter on missing data:

-- 1. Find employees without a phone number
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 ➔ TRUE
5 = 10 ➔ FALSE
5 = NULL ➔ UNKNOWN
NULL = 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)

The Most Important Practical Rule

COUNT(*) counts every row in the table, regardless of content.
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:

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;

Classifying NULL With CASE Expressions

You can categorize missing data using CASE WHEN ... IS NULL:

SELECT name,
    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:

-- Returns phone number, or 'Not Available' if NULL
SELECT name, COALESCE(phone, 'Not Available') AS phone
FROM employees;

Diagram 3: COALESCE Fallback Resolution

phone value
Input column

── Evaluates ──▶

Is it NULL?
NULL check

── YES ➔ Fallback 'No phone'
── 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):

-- ❌ DANGEROUS: If bonus is NULL, total_comp becomes NULL!
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”.

Diagram 4: Synthetic NULL Generation in LEFT JOIN

Customer: Charlie (ID: 3)
Preserved Left Row

── Matches Orders? ──▶

No Order Found!
Synthetic Right Output

── Yields ──▶

order_id = NULL, amount = NULL
Safe Outer Output

Common SQL NULL Handling Mistakes

1. Writing WHERE col = NULL

Always use IS NULL; equality comparisons with NULL return UNKNOWN and omit rows.

2. Arithmetic Contamination

Forgetting that salary + NULL = NULL. Always wrap nullable math in COALESCE().

3. Confusing COUNT(*) and COUNT(col)

COUNT(*) returns total rows; COUNT(col) ignores NULL values.

4. Confusing Empty String with NULL

'' is not NULL. WHERE col IS NULL will NOT catch blank strings.

Practical Query Exercises

Exercise 1: Safe Total Earnings Calculation

SELECT
    name,
    salary,
    COALESCE(bonus, 0) AS bonus,
    salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;

Exercise 2: Multi-Fallback Contact Resolution

SELECT
name,
COALESCE(mobile_phone, work_phone, email, 'No Contact Method') AS primary_contact
FROM contacts;

⚡ Live Interactive SQL NULL Practice Lab

Inspect visual NULL badges, test IS NULL vs = NULL, compare COUNT(*) vs COUNT(col), and experiment with COALESCE

Filter Operator:

Generated SQL Statement

SELECT name, department, phone
FROM employees
WHERE phone IS NULL;

Filter Output (2 rows)Status: Matching Rows Returned

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 = NULL or != 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 NULL at 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 NULL and IS 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.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL NULL Handling

Test your understanding of missing data states, three-valued logic, COUNT(*) vs COUNT(col), arithmetic safety, and COALESCE fallbacks.

Question 1 of 8Current Score: 0 / 0

What does NULL represent in standard ANSI SQL?