MySQL NULL Values
MySQL NULL Values
In MySQL, a NULL value represents missing, unknown, or undefined data. It is not the same as zero (0) or an empty string (”). NULL simply means no value has been stored.
📌 Key Facts About NULL
| Value Type | Meaning |
|---|---|
NULL |
No value / unknown |
0 |
Number zero (valid value) |
'' (empty string) |
Text with zero characters |
' ' (space) |
One character text |
So:
❌ NULL ≠ 0
❌ NULL ≠ ''
🧪 Example Table
| id | name | age | |
|---|---|---|---|
| 1 | Rahul | 25 | rahul@gmail.com |
| 2 | Priya | NULL | priya@example.com |
| 3 | Aman | 30 | NULL |
Here, Priya’s age is unknown and Aman’s email is missing.
🔍 Checking NULL Values (IS NULL)
To find rows where a column contains NULL:
🔍 Checking Non-NULL Values (IS NOT NULL)
❌ Why Not Use = NULL?
This is incorrect:
Because NULL is not a value — it represents absence of a value — so equality comparison doesn’t work.
🧠 Updating a Field to NULL
🆕 Inserting NULL Values
🔧 Using IFNULL() or COALESCE() to Handle NULL
Sometimes, you want to display a default value when NULL is found.
🧐 NULL in Comparisons with Operators
| Expression | Result |
|---|---|
NULL = NULL |
FALSE |
NULL <> NULL |
FALSE |
NULL + 10 |
NULL |
COUNT(column) |
Ignores NULL values |
🧾 Summary
| Feature | Example |
|---|---|
| Check for NULL | IS NULL |
| Check for not NULL | IS NOT NULL |
| Insert NULL | VALUES (NULL) |
| Update to NULL | SET column = NULL |
| Display alternate value | IFNULL() or COALESCE() |
