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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://liancode.gitbook.io/sql/level-5/join/outer-join.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.