Leetcode SQL 50 -- 584. Find Customer Referee

Question

Table name: Customer

Column Name	Type
id	int
name	varchar
referee_id	int

id is the primary key column for this table.
Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

Find the names of the customer that are not referred by the customer with id = 2.
Return the result table in any order.

Explaination

Again we use SELECT <column_name> FROM <table_name> WHERE <conditions> to get the solution.

The tricky part is some of the referee_id may contain NULL value.

NULL handling

We may simply think that the solution is

WHERE referee_id !=2

However, NULL always return false in any comparison. For example:

SELECT *
FROM Customer
WHERE referee_id != 2 OR referee_id = 2

This query will return all rows that their referee_id is not NULL. Because NULL returns false in both cases.

In order to retrieve rows that contain NULL, we have to use the keyword IS NULL.

For further explaination of NULL Handling in SQL, you may check the sqlite documentation

Solution

SELECT "name" FROM Customer WHERE referee_id != 2 OR referee_id IS NULL;

---

More SQL 50 questions: here