Given a tablecustomerholding 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 customersNOTreferred by the person with id '2'.

For the sample data above, the result is:

+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

Solution

Approach: Using<>(!=) andIS NULL[Accepted]

Intuition

Some people come out the following solution by intuition.

SELECT
name
FROM
customer
WHERE
referee_Id <> 2;

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 (NULLvalue in the referee_id column) don’t show up. But why?

Algorithm

MySQL uses three-valued logic -- TRUE, FALSE and UNKNOWN. Anything compared to NULL evaluates to the third value: UNKNOWN. That “anything” includes NULL itself! That’s why MySQL provides theIS NULLandIS NOT NULLoperators to specifically check for NULL.

Thus, one more condition 'referee_id IS NULL' should be added to the WHERE clause as below.

MySQL

SELECT
name
FROM
customer
WHERE
referee_id <> 2 or referee_id IS NULL;

or

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

Tips

The following solution is also wrong for the same reason as mentioned above. The key is to always useIS NULLorIS NOT NULLoperators to specifically check for NULL value.

SELECT
name
FROM
customer
WHERE
referee_id = null OR referee_id <>2;