問題
查詢所有獎(jiǎng)金<1000的員工的姓名和獎(jiǎng)金戏羽。
表: Employee
+-------+--------+-----------+--------+
| empId | name | supervisor| salary |
+-------+--------+-----------+--------+
| 1 | John | 3 | 1000 |
| 2 | Dan | 3 | 2000 |
| 3 | Brad | null | 4000 |
| 4 | Thomas | 3 | 4000 |
+-------+--------+-----------+--------+
empId 是主鍵
表: Bonus
+-------+-------+
| empId | bonus |
+-------+-------+
| 2 | 500 |
| 4 | 2000 |
+-------+-------+
empId 是主鍵
輸出:
+-------+-------+
| name | bonus |
+-------+-------+
| John | null |
| Dan | 500 |
| Brad | null |
+-------+-------+
解答
首先兩表連接 這里需要左連接
select E.name, B.bonus
from Employee as E
left join Bonus as B
on E.empId = B.empId
然后根據(jù)bonus進(jìn)行篩選 獎(jiǎng)金<1000 沒有獎(jiǎng)金(獎(jiǎng)金為NULL)也符合
select E.name, B.bonus
from Employee as E
left join Bonus as B
on E.empId = B.empId
where B.bonus <1000 or B.bonus is null;
