CODEWITHSUNDEEP
sqlsqlite
Nic Parmee
Nic Parmee

Reputation: 153

SQLite subqueries trying find if id does not exist in another column

I have been battling with this for a bit. This is a test question from a testing site but I have no one to email and try find the answer from.

CREATE TABLE employees (
  id INTEGER NOT NULL PRIMARY KEY,
  managerId INTEGER REFERENCES employees(id), 
  name VARCHAR(30) NOT NULL
);

INSERT INTO employees(id, managerId, name) VALUES(1, NULL, 'John');
INSERT INTO employees(id, managerId, name) VALUES(2, 1, 'Mike');

-- Expected output (in any order):
-- name
-- ----
-- Mike

-- Explanation:
-- In this example.
-- John is Mike's manager. Mike does not manage anyone.
-- Mike is the only employee who does not manage anyone.

Write a query that selects the names of employees who are not managers.

This is what I have come up with but it does not work.

SELECT name
FROM employees
WHERE id NOT IN(SELECT managerId FROM employees)

I'm just trying to understand how I can iterate through the managerId column and check whether the Id matches it or not?

Upvotes: 1

Views: 1157

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

This is because one of the selected manager IDs is null. Null ist the "unknown value". So NOT IN does not succeed, as it cannot guarantee that your value is not in the data set (as your value could be the unknown value). Well, so far for the argument.

So either:

SELECT name
FROM employees
WHERE id NOT IN (SELECT managerId FROM employees WHERE managerId IS NOT NULL);

or

SELECT name
FROM employees e
WHERE NOT EXISTS (SELECT * FROM employees m WHERE m.managerId = e.id);

This is really a nasty trap one must be aware of. Most often we look up values that cannot be null. Bad luck yours is a rare case where nulls exist in the lookup :-)

Upvotes: 2

Related Questions