OUTER JOIN

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

注：MySQL 数据库不支持全连接，想要实现全连接可以使用 UNION ALL 来将左连接和右连接结果组合在一起

1 OUTER JOIN 基本语法

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

演示数据库

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

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

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

2 OUTTER JOIN 实例

下面的 SQL 语句查询所有的课程姓名和对应的教师姓名。

MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。

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

注释：FULL OUTER JOIN 关键字返回左表（courses）和右表（teachers）中所有的行。如果 "teachers" 表中的行在 "courses" 中没有匹配或者 "courses" 表中的行在 "teachers" 表中没有匹配，也会列出这些行。

OUTER JOIN 在 MySQL 中的写法，可以通过 UNION 合并LEFT JOIN 和

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

执行输出结果

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