SQL Left Outer Join can be illustrated by the following:

where the query will take everything from left table and those fields from the right table. Let’s take a look at the following question as a left-outer-join (which is known as left-join) example.

Employee Bonus

Select all employee’s name and bonuses whose bonus is less than $1000.

SQL Schema

Create table If Not Exists Employee (EmpId int, Name varchar(255), Supervisor int, Salary int)
Create table If Not Exists Bonus (EmpId int, Bonus int)
Truncate table Employee
insert into Employee (EmpId, Name, Supervisor, Salary) values ('3', 'Brad', 'None', '4000')
insert into Employee (EmpId, Name, Supervisor, Salary) values ('1', 'John', '3', '1000')
insert into Employee (EmpId, Name, Supervisor, Salary) values ('2', 'Dan', '3', '2000')
insert into Employee (EmpId, Name, Supervisor, Salary) values ('4', 'Thomas', '3', '4000')
Truncate table Bonus
insert into Bonus (EmpId, Bonus) values ('2', '500')
insert into Bonus (EmpId, Bonus) values ('4', '2000')

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

The SQL of left-join will return all the employees and their bonuses, however, some employees don’t have bonuses – as they are not shown in the Bonus table. Therefore, the result field (bonus) will be empty for those employees. Thus, we have to explicitly use IS (or IS NOT) operator to check for nullness.

SELECT
    Employee.name, Bonus.bonus
FROM
    Employee
LEFT JOIN
    Bonus ON Employee.empid = Bonus.empid
WHERE
    bonus < 1000 OR bonus IS NULL;

–EOF (The Ultimate Computing & Technology Blog) —

推荐阅读：
数学题:前三轮的平均的平均分是94　 数学题:财务室会计结账时，发现账面上少了890.1元钱　 数学题:一个玻璃瓶内原有盐是水的1/11　 数学题:把圆柱平均分成若干份后拼成一个长方体　 奥数题:甲乙两地中间有一座山岭　 奥数题:一份工作按计划的时间算　 简便计算题：1997÷(1997+1997/1998)+(1/1999)　 数学题:在比例尺1：5000的图纸上　 2014年是平年还是闰年　 数学题:在11次红灯变绿灯之间的黄灯亮起中