584. Find Customer Referee

SQL Schema

Table: 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.

 

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.

Return the result table in any order.

The query result format is in the following example.

 

Example 1:

Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

 

---

solution

 

# Write your MySQL query statement below

select name from customer where referee_id is null or referee_id != 2 ;


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

----

mysql은  three-valued logic을 사용한다. TRUE, FALSE, UNKNOWN. null을 포함하여 null에 비견되는 값은 무조건 제3의 값인 UNKNOWN으로 분류된다. 틀린 것도 맞은 것도 아닌 제 삼의 값. mysql이 NULL을 체크하기 위해서 IS NULL과 IS NOT NULL 연산자를 제공하는 이유이다.

 

위의 이유로 select name from customer where referee_id != 2라고만 적으면 오류 발생