584. Find Customer Referee
来源:互联网 发布:网络招聘提成方案 编辑:程序博客网 时间:2024/06/06 18:32
Question
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 |
+——+
Quick Navigation
Solution
Approach: Using <>(!=) and IS NULL [Accepted]
Solution
Approach: Using <>(!=) and IS 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 (NULL value 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 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
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 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;
- 584. Find Customer Referee
- 584. Find Customer Referee
- Find Error--Print Customer Order Invoices
- EBS_OM_Customer: Find Customer information base on SO
- Customer
- Xs and Os Referee
- checkio-Xs and Os Referee
- checkio Xs and Os Referee
- ZOJ 3426 & HDU 3719 Snooker Referee
- Customer Enhancement
- Customer Exit
- Customer & Vendor
- Customer Service
- activeMQ customer
- Background-customer
- Customer Class
- Your Customer Is Not Your Customer
- cannot watch `referee.log': No space left on device
- Kafka常用命令集合
- 国内3C数码第一股 巅峰科技荣登新三板
- 定制时代开启,看微鲸电视如何玩转功夫熊猫
- 传统行业也逆袭,这家酒店免费住了
- 蔡宗辉的“儿童智能手表”之道:硬件是基础,服务才是王道
- 584. Find Customer Referee
- ZeroMQ(java)入门之Requerst/Response模式
- 玩转国际顶级IP 微鲸推功夫熊猫定制版
- 大小端介绍及检测方法
- POJ1845 Sumdiv
- [分块打表] [Luogu P1822] 魔法指纹
- 可持续化线段树
- vivo X6&X6Plus速度快得吓死人!苹果不再是全球最流畅手机
- 深度学习--tensorflow playground-神经网络的可视化