Leetcode SQL 50 -- 584. Find Customer Referee

  1. Question
    1. Table name: Customer
  2. Explaination
    1. NULL handling
  3. Solution

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
sqlcopy code

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

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

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;
sqlcopy code

More SQL 50 questions: here


Please cite the source for reprints, feel free to verify the sources cited in the article, and point out any errors or lack of clarity of expression. You can comment in the comments section below or email to GreenMeeple@yahoo.com

Related Issues not found

Please contact @GreenMeeple to initialize the comment

This Repo