Handling NULLs

In SQL, NULL is not a value; it is a marker indicating that a value is missing or unknown. This distinction is the source of many bugs and requires a fundamental shift in how you think about logic.

Definition: Three-Valued Logic (3VL)

Most programming languages use Two-Valued Logic: a statement is either TRUE or FALSE. SQL uses Three-Valued Logic:

1. TRUE
2. FALSE
3. UNKNOWN

When you compare something to NULL, the result is always UNKNOWN.

Context: The Equality Fallacy

In math, $x = x$ is always true. In SQL, NULL = NULL is NOT true. It is UNKNOWN.

Think of it this way:

• I have a box, and I don’t know what’s inside.
• You have a box, and you don’t know what’s inside.
• Are the contents the same? We don’t know.

Detailed Explanation: IS NULL vs. = NULL

Because NULL is unknown, you cannot use the standard equals operator (=) to find it.

Incorrect

WHERE MiddleName = NULL
Result: Always returns zero rows because the comparison evaluates to UNKNOWN.

Correct

WHERE MiddleName IS NULL
Result: Correcty identifies rows where the marker is present.

Logical Truth Tables with UNKNOWN

Expression	Result
TRUE AND UNKNOWN	UNKNOWN
FALSE AND UNKNOWN	FALSE (Short-circuited!)
TRUE OR UNKNOWN	TRUE (Short-circuited!)
FALSE OR UNKNOWN	UNKNOWN
NOT UNKNOWN	UNKNOWN

Example: Filtering Missing Data

We have a Profiles table:

UserID	Username	Email	PhoneNumber
1	tech_guy	tg@example.com	555-0101
2	data_queen	dq@example.com	NULL
3	sql_pro	sp@example.com	555-0202
4	ghost	NULL	NULL

Task 1: Find users without a phone number

Check for NULL

SELECT Username
FROM Profiles
WHERE PhoneNumber IS NULL;

Task 2: Find users with a phone number (Negative check)

Check for NOT NULL

SELECT Username
FROM Profiles
WHERE PhoneNumber IS NOT NULL;

Output

Task 1:

Username
data_queen
ghost

Task 2:

Username
tech_guy
sql_pro

Under the Hood: NULLs in Indexes and Sorting

1. Sorting

Where does NULL go when you sort?

• In PostgreSQL, NULL values are treated as “larger” than all other values by default (they appear last in ASC and first in DESC).
• In SQL Server, NULL values are treated as “smaller” (they appear first in ASC).

Most databases allow you to override this using ORDER BY column_name ASC NULLS FIRST or NULLS LAST.

2. Indexes

By default, most B-Tree indexes include NULL values. However, some databases (like Oracle) do not index rows where all indexed columns are NULL. This can lead to unexpected Full Table Scans if you query for IS NULL.

3. Aggregates (A Preview)

Almost all aggregate functions (like SUM, AVG, COUNT) ignore NULL values. The only exception is COUNT(*), which counts every row regardless of content.

Danger

The NOT IN Trap:
WHERE ID NOT IN (1, 2, NULL) will return nothing.
Why? Because ID NOT IN (1, 2, NULL) expands to:
ID != 1 AND ID != 2 AND ID != NULL.
Since ID != NULL is UNKNOWN, the entire AND chain becomes UNKNOWN, and no rows are returned.