> For the complete documentation index, see [llms.txt](https://liancode.gitbook.io/sql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://liancode.gitbook.io/sql/level-5/join/outer-join.md).

# OUTER JOIN

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

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

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

## 1 OUTER JOIN 基本语法

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

![](https://1752284691-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTtZid1T_fKn-TbD7v4%2Fsync%2Fcd04bcb866e659367104bd5a624782e654639243.png?generation=1614233845173620\&alt=media)

**演示数据库**

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

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

![](https://1752284691-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTtZid1T_fKn-TbD7v4%2Fsync%2Fd8f5f14985f9bf120a5cc43a19e8dd775ec8b9b7.png?generation=1614836407333898\&alt=media)

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

![](https://1752284691-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MTtZid1T_fKn-TbD7v4%2Fsync%2F6b3f9e5f825ae5a2e7cc56e739fd38a279f8d5d7.png?generation=1614836407419028\&alt=media)

## 2 OUTTER JOIN 实例

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

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

```sql
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 和

```sql
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;
```

执行输出结果

```bash
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)
```