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

![](/files/-MUMpQ_Fr266i4tLzyN9)

**演示数据库**

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

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

![](/files/-MUMdlIzuX-B0GISF1Sx)

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

![](/files/-MUMdlIyUPGj2k0qjOiN)

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