NULL means unknown
Test for missing values with IS NULL, and fill them in with COALESCE. About 9 minutes.
A blank is not a zero
A member joined the library but never gave a phone number. That box isn't empty text and it isn't zero. It is unknown, and SQL has a name for that: NULL.
Comparing with an unknown gives an unknown answer. phone = NULL is never true, not even for the rows where phone is NULL, and phone <> NULL isn't either. WHERE keeps a row only when its test is true, so you ask with IS NULL or IS NOT NULL instead.
SELECT name FROM members WHERE phone IS NULL;
| id | name | phone |
|---|---|---|
| 1 | Asha | 98450 |
| 2 | Ravi | NULL |
| 3 | Meera | 91234 |
| 4 | Kabir | NULL |
Asha has a phone number, so phone IS NULL is false. Dropped.
Unknown rows fall out of both sides
A row where cgpa is NULL fails the test "7 or more", and it fails the test "under 7" too. Unknown is neither, so it appears in neither answer. That is how "not equal" tests quietly lose rows.
To keep them, say so with an extra OR cgpa IS NULL. And COALESCE gives the first value that isn't NULL, handy for showing a default.
WHERE cgpa < 7 OR cgpa IS NULL SELECT name, COALESCE(phone, 'none') FROM members;
A table has 10 rows. Six have cgpa 8 or more, two have cgpa under 8, and two have cgpa NULL. How many rows does WHERE cgpa < 8 return?
- A2
- B4
- C8
Show the answer
2. Only the two rows with a real cgpa below 8 pass. NULL is unknown, so it fails < 8.
Missing phone numbers
The members table has one row per library member. Show the name of every member who has no phone number on file.
members(id, name, phone)
Up to 2,000 members