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 ≠ 0NULL ≠ ''
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() |
NULL values help represent missing information in the database.
