## 584. Find Customer Referee

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

b'
\n
\n\n
\n

## Solution

\n
\n

#### Approach: Using `<>`(`!=`) and `IS NULL` [Accepted]

\n

Intuition

\n

Some people come out the following solution by intuition.

\n
`SELECT name FROM customer WHERE referee_Id <> 2;\n`
\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?

\n

Algorithm

\n

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

\n

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

\n

MySQL

\n
`SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;\n`
\n

or

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

Tips

\n

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

\n
`SELECT name FROM customer WHERE referee_id = NULL OR referee_id <> 2;\n`
\n
'