Employee Bonus

题目

Select all employee's name and bonus whose bonus is < 1000.

Table:Employee

+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId is the primary key column for this table.

Table:Bonus

+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId is the primary key column for this table.

Example ouput:

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

思路分析

这道题是一个典型的left outer join的题型，在得到新的left outer join table之后，要做一个filter，用where来实现，这里需要注意的是bonus为null的也要输出，但是不能用表达式bonus=null，而是要用bonus is null

# Write your MySQL query statement below
select name, bonus
from Employee
left join Bonus
on Employee.empId = Bonus.empID
where bonus < 1000 or bonus is null    <<< use bonus is null, not bonus=null here