Schema and sample data
| emp_id | name | manager_id |
|---|---|---|
| 1 | Ava Torres | NULL |
| 2 | Ben Ncube | 1 |
| 3 | Cara Singh | 1 |
| 4 | Deng Liu | 2 |
| 5 | Emma Rossi | NULL |
| 6 | Farid Khan | 3 |
Show setup SQL (copy to run)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
manager_id INT NULL
);
INSERT INTO Employees (emp_id, name, manager_id) VALUES
(1, 'Ava Torres', NULL),
(2, 'Ben Ncube', 1),
(3, 'Cara Singh', 1),
(4, 'Deng Liu', 2),
(5, 'Emma Rossi', NULL),
(6, 'Farid Khan', 3);The Employees table records each person and, in manager_id, the emp_id of the person they report to. Top level employees report to nobody, so their manager_id is NULL. Return the name of every employee whose manager_id is NULL, sorted by name in ascending order.
Expected result
| name |
|---|
| Ava Torres |
| Emma Rossi |
Show hint
To test a column for NULL you must use the IS NULL predicate. A comparison like manager_id = NULL never returns true, so it would match no rows at all.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT name
FROM Employees
WHERE manager_id IS NULL
ORDER BY name;NULL means "no value is known", so it is not equal to anything, not even to another NULL. Because of that, SQL uses three valued logic: any comparison with NULL, including manager_id = NULL, evaluates to UNKNOWN rather than TRUE, and WHERE keeps only rows where the predicate is TRUE. That is why WHERE manager_id = NULL silently returns zero rows instead of an error.
The correct test is the dedicated IS NULL predicate. WHERE manager_id IS NULL keeps only Ava Torres and Emma Rossi, the two rows whose manager_id holds no value, and ORDER BY name lists them alphabetically. To ask for the opposite set (everyone who does report to a manager) you would write WHERE manager_id IS NOT NULL.
A common way to handle NULLs is to substitute a stand in value with COALESCE, for example COALESCE(manager_id, -1) = -1, but the direct and idiomatic choice here is IS NULL. Reaching for = NULL is the classic beginner mistake, and it is a silent one because the query runs without error and just returns nothing.