- NULL in SQL means: Missing, unknown, or not applicable value.
- It’s not zero
0, it’s not an empty string''. - It literally means “I don’t know” or “value does not exist.”
NULL ≠ 0
NULL ≠ ”
NULL = unknown
Key Characteristics of NULL
| Property | Meaning |
|---|---|
| NULL means unknown | Absence of any value. |
| NULL is not equal to anything | Even NULL is not equal to NULL! (NULL <> NULL). |
| Special comparisons needed | Use IS NULL or IS NOT NULL — not = or !=. |
| Affects expressions and functions | Most operations on NULL produce NULL. (1 + NULL = NULL) |
How are NULLs Handled in SQL?
1. Comparison with NULL
You can’t use = or != for NULLs!
✅ Correct:
WHERE column IS NULL
or
WHERE column IS NOT NULL
❌ Wrong:
WHERE column = NULL
(It will always fail — NULL compared to anything is unknown.)
2. NULL in Aggregations
Aggregations usually ignore NULLs:
SELECT AVG(score) FROM Students;
NULL score rows are ignored in the AVG() calculation.
3. NULL in Functions
- You can replace NULLs with a default value using
COALESCE():
SELECT COALESCE(name, 'Unknown') FROM Users;
If name is NULL, it shows 'Unknown'.
Or with IFNULL() (in MySQL):
SELECT IFNULL(name, 'Unknown') FROM Users;
4. NULL and UNIQUE Constraint
- Most databases allow one NULL in a column with a UNIQUE constraint:
- NULL is treated as not equal to other NULLs.
Example:
| id | |
|---|---|
| 1 | alice@email.com |
| 2 | NULL |
| 3 | NULL |
✅ Some databases (e.g., PostgreSQL, MySQL) allow this.
❌ SQL Server allows only one NULL if you use a UNIQUE index.
5. NULL in ORDER BY
When you sort with ORDER BY, NULLs can:
- Appear first or last depending on the database.
- You can control it explicitly:
ORDER BY column_name ASC NULLS LAST
or
ORDER BY column_name DESC NULLS FIRST
NULL in Expressions Example
| x | y | x + y |
|---|---|---|
| 10 | 5 | 15 |
| NULL | 5 | NULL |
| 10 | NULL | NULL |
| NULL | NULL | NULL |
- Any operation involving NULL results in NULL.
Three-Valued Logic (3VL) in SQL
When NULL is involved, SQL uses three-valued logic:
| Comparison | Result |
|---|---|
5 = 5 | TRUE |
5 = 10 | FALSE |
5 = NULL | UNKNOWN |
NULL = NULL | UNKNOWN |
SQL WHERE clauses only select rows where the condition is TRUE — not FALSE, not UNKNOWN.
In Short
| Concept | Meaning |
|---|---|
| NULL | Unknown, missing, or inapplicable value. |
| Special comparison | Use IS NULL / IS NOT NULL. |
| Aggregates | NULLs are ignored (e.g., in SUM(), AVG()). |
| Expressions | Any calculation with NULL → result is NULL. |
| Three-Valued Logic | NULL introduces an unknown third state. |
Simple Analogy
- 0 = “I have $0.”
- ” = “I have an empty wallet.”
- NULL = “I don’t even know if I have a wallet.”