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