✏️ Explanatory Question
Level: Basic — A classic conceptual trap; many candidates wrongly treat NULL as 0 or ''.
These three look similar but are completely different in MySQL:
NULL = NULL returns NULL (unknown), and you must use IS NULL / IS NOT NULL to test for it.
| Aspect | NULL | Zero (0) | Empty String ('') |
|---|---|---|---|
| Meaning | Unknown / missing | Numeric value | String with 0 length |
| Is it a value? | No | Yes | Yes |
| Takes storage? | Minimal (flag) | Yes | Yes |
| Used in math? | Result becomes NULL | Yes | N/A |
| Tested with | IS NULL | = 0 | = '' |
| LENGTH() returns | NULL | 1 (for '0') | 0 |
WHERE column = NULL → returns nothing=WHERE column IS NULL → works correctlyIS NULL / IS NOT NULLCREATE TABLE demo (
id INT,
val INT,
txt VARCHAR(20)
);
INSERT INTO demo VALUES (1, NULL, NULL); -- unknown values
INSERT INTO demo VALUES (2, 0, ''); -- zero and empty string
-- Correct way to find NULLs
SELECT * FROM demo WHERE val IS NULL; -- returns row 1
-- Math with NULL always yields NULL
SELECT 100 + NULL; -- result: NULL
SELECT 100 + 0; -- result: 100
-- Length comparison
SELECT LENGTH(NULL), LENGTH(''), LENGTH('0'); -- NULL, 0, 1