> For the complete documentation index, see [llms.txt](https://chesterzhang666.gitbook.io/intro-mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://chesterzhang666.gitbook.io/intro-mysql/advanced-select/join-lian-biao-cha-xun/left-join-right-join-wai-lian-cha-xun.md).

# LEFT JOIN & RIGHT JOIN 外联查询

考虑这样一个场景: 查询所有员工和其对应的部门名称。emp表中张三是一个零时工, deptno为NULL, 我们希望保留NULL  ，将张三也查询出来。

```sql
# 如emp表中张三是一个零时工, deptno为NULL, 我们希望保留NULL
# 查询所有员工和其对应的部门名称
SELECT
e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno; 

# 也可以交换JOIN表格的顺序,换成 右外连接 效果相同
SELECT
e.ename, d.dname
FROM t_dept d  RIGHT JOIN t_emp e ON e.deptno=d.deptno; 
```

另外一个场景: 查询所有部门名称和其人数。t\_dept中有个OPERATIONS部门，没有人，我们希望统计人数为0 

```sql
# 查询每个部门的名称和人数, 有个部门OPERATIONS，编号40, 没有人，我们也希望显示
SELECT
d.dname , COUNT(e.deptno)
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptno=d.deptno 
GROUP BY d.deptno;
```

## UNION 关键字

查询结果1 UNION 查询结果2,  **将查询结果1和查询结果2进行取并集 操作**

比如: 我们要查询每个部门的人数。t\_emp中有个临时工张三, 部门编号为NULL。t\_dept中有个OPERATIONS部门，没有人。我们希望显示部门为NULL的人数, OPERATIONS部门为0.

```sql
# UNION
# 查询每个部门的名称和部门的人数
# 如果没有部门的员工,部门名称用NULL代替(t_emp LEFT JOIN t_dept)
# 如果部门没有人也希望显示出来，人数为0(t_emp RIGHT JOIN t_dept)
(SELECT
d.dname , COUNT(*)
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno=d.deptno 
GROUP BY d.deptno)
UNION
(
SELECT
d.dname , COUNT(*)
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptno=d.deptno 
GROUP BY d.deptno
); 
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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://chesterzhang666.gitbook.io/intro-mysql/advanced-select/join-lian-biao-cha-xun/left-join-right-join-wai-lian-cha-xun.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.