Find Customer Referee

easy database sql

Problem

Customer(id, name, referee_id). Return names of customers whose referee_id is not 2 (NULL counts as 'not 2').

Inputc=[(1,'W',null),(2,'A',null),(3,'M',1),(4,'B',2),(5,'L',1)]

Output['W','A','M','L']

Customer 4 is filtered out; NULL rows pass.

rows (compact)

SELECT name FROM Customer
WHERE referee_id <> 2 OR referee_id IS NULL;

Explanation

We just want customers whose referee_id is not 2. The catch — and the whole point of this problem — is how SQL treats NULL values, which mean "unknown".

In SQL, comparing anything to NULL gives neither true nor false but unknown. So a plain referee_id <> 2 would silently drop the rows where referee_id is NULL, even though those customers clearly weren't referred by 2.

To fix this we add OR referee_id IS NULL. Now a row passes the WHERE if its referee is some value other than 2, or if its referee is unknown.

Example: customers W and A have NULL referees, so the IS NULL half lets them through. M and L have referee 1, which satisfies <> 2. Only B, whose referee is exactly 2, is filtered out.

Time: O(n) Space: O(n)