584. Find Customer Referee
来源:互联网 发布:php workerman swoole 编辑:程序博客网 时间:2024/06/06 17:55
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
- HDR Darkroom 3 v2.2.0 中文版
- javascript-原生和jquery请求写法
- git常用命令
- spring bean的生命周期
- 动态规划---最短编辑距离
- 584. Find Customer Referee
- 《 安全测试的考虑点及测试方法 》转载
- 【oracle T4】同义词、序列 、视图 、索引2017.7.26
- DP动态规划(持续更新)
- 记Dorado7学习(2)
- HDU6033(多校联合签到水题)
- 如何更换git托管
- 不论iPhone还是安卓,都可以这样恢复手机误删的文件
- 附件上传遇到的异常