Given a table customer holding customers information and the referee.
+------+------+-----------+ | id | name | referee_id| +------+------+-----------+ | 1 | Will | NULL | | 2 | Jane | NULL | | 3 | Alex | 2 | | 4 | Bill | NULL | | 5 | Zack | 1 | | 6 | Mark | 2 | +------+------+-----------+
Write a query to return the list of customers NOT referred by the person with id '2'.
For the sample data above, the result is:
+------+ | name | +------+ | Will | | Jane | | Bill | | Zack | +------+
<>(!=) and IS NULL [Accepted]Intuition
\nSome people come out the following solution by intuition.
\nSELECT name FROM customer WHERE referee_Id <> 2;\n
However, this query will only return one result:Zack although there are 4 customers not referred by Jane (including Jane herself). All the customers who were referred by nobody at all (NULL value in the referee_id column) don\xe2\x80\x99t show up. But why?
Algorithm
\nMySQL uses three-valued logic -- TRUE, FALSE and UNKNOWN. Anything compared to NULL evaluates to the third value: UNKNOWN. That \xe2\x80\x9canything\xe2\x80\x9d includes NULL itself! That\xe2\x80\x99s why MySQL provides the IS NULL and IS NOT NULL operators to specifically check for NULL.
Thus, one more condition \'referee_id IS NULL\' should be added to the WHERE clause as below.
\nMySQL
\nSELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;\n
or
\nSELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;\n
Tips
\nThe following solution is also wrong for the same reason as mentioned above. The key is to always use IS NULL or IS NOT NULL operators to specifically check for NULL value.
SELECT name FROM customer WHERE referee_id = NULL OR referee_id <> 2;\n