LEFT JOIN

左连接返回左表中的所有记录，即使右表中没有任何满足匹配条件的记录。这意味着，如果 ON 子句在右表中匹配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自右表的字段都为 NULL。

这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录，或者是 NULL （如果连接谓词无法匹配到任何记录的话）。

1 LEFT JOIN 基本语法

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

2 LEFT JOIN 实例

下面的 SQL 语句将返回所有教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

以下实例中我们把 teachers 作为左表，courses作为右表：

SELECT c.name AS course_name, t.name AS teacher_name
FROM teachers t
    LEFT JOIN courses c ON c.teacher_id = t.id;

执行输出结果

mysql> SELECT c.name AS course_name, t.name AS teacher_name
    -> FROM teachers t
    ->  LEFT JOIN courses c ON c.teacher_id = t.id;
+-------------------------+------------------+
| course_name             | teacher_name     |
+-------------------------+------------------+
| Big Data                | Eastern Heretic  |
| Data Analysis           | Eastern Heretic  |
| Dynamic Programming     | Eastern Heretic  |
| NULL                    | Northern Beggar  |
| System Design           | Western Venom    |
| Django                  | Western Venom    |
| Artificial Intelligence | Western Venom    |
| Java P6+                | Western Venom    |
| Senior Algorithm        | Southern Emperor |
| Web                     | Southern Emperor |
| Object Oriented Design  | Southern Emperor |
| NULL                    | Linghu Chong     |
+-------------------------+------------------+
12 rows in set (0.06 sec)

练习题：LEFT JOIN

使用 LEFT JOIN 查询 所有 ' country' 等于 'CN' 的教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

代码输入框：

SELECT c.name AS course_name, t.name AS teacher_name
FROM teachers t
    LEFT JOIN courses c ON c.teacher_id = t.id
WHERE t.country = 'CN';

执行输出结果

+------------------------+------------------+
| course_name            | teacher_name     |
+------------------------+------------------+
| Big Data               | Eastern Heretic  |
| Data Analysis          | Eastern Heretic  |
| Dynamic Programming    | Eastern Heretic  |
| NULL                   | Northern Beggar  |
| Senior Algorithm       | Southern Emperor |
| Web                    | Southern Emperor |
| Object Oriented Design | Southern Emperor |
| NULL                   | Linghu Chong     |
+------------------------+------------------+