Lesson 8 of 10
Missing values
- 12 min
- 5 checks
- 3 hints
- Go to task
Goal
NULL and COALESCE
Explanation
NULL isn't a value. It means the value is missing or unknown, and any comparison with it gives NULL too, which is neither true nor false. WHERE keeps only the rows where the condition is true.
That's why WHERE email = NULL matches no rows at all, not even the ones whose email really is missing, and it doesn't give you an error either. Use IS NULL and IS NOT NULL instead. The same goes for a condition like email <> 'x': it silently drops the NULL rows.
COALESCE(a, b) returns the first value that isn't NULL, so you can use it to put a clear fallback in place of a missing value.
Your task
Show the name and phone of the customers who have no email, put 'none' in place of a missing phone number, and name the second column phone.
Checks
5 checks- Columns are name and phone
- Everyone with no email appears, using IS NULL
- No NULL in the phone column
- A missing phone number became 'none'
- Existing numbers stayed as they were
Tables in this lesson
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
phone TEXT
);
INSERT INTO customers (name, email, phone) VALUES
('Layan', 'layan@mail.sa', '0551234567'),
('Khalid', 'Khalid@Mail.SA', '0509876543'),
('Reem', NULL, '0543216789'),
('Sara', 'sara@gmail.com', NULL),
('Fahad', NULL, NULL),
('Noura', 'noura@mail.sa', '055123456'),
('Omar', 'omar@webmail.sa', '0561112233'),
('Hind', 'hind@mail.sa', '0598765432'),
('Yousef', NULL, '0533334444');