> For the complete documentation index, see [llms.txt](https://beret81.gitbook.io/study-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://beret81.gitbook.io/study-notes/mysql-shu-ju-ku/lian-he-cha-xun.md).

# 联合查询

## 1、基本概念

联合查询是可合并多个相似的选择查询的结果集。等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起，使用谓词为UNION或UNION ALL。

联合查询：将多个查询的结果合并到一起（纵向合并）：字段数不变，多个查询的记录数合并。

## 2、应用场景

1、 将同一张表中不同的结果（需要对应多条查询语句来实现），合并到一起展示数据

男生身高升序排序，女生身高降序排序

2、 最常见：在数据量大的情况下，会对表进行分表操作，需要对每张表进行部分数据统计，使用联合查询来讲数据存放到一起显示。

QQ1表获取在线数据

QQ2表获取在线数据 ---》将所有在线的数据显示出来

## 3、基本语法

基本语法：

Select 语句

Union \[union 选项]

Select 语句;

Union选项：与select选项基本一样

Distinct：去重，去掉完全重复的数据（默认的）

```
mysql> select * from class 
    -> union
    -> select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
+----------+------------+--------------+
6 rows in set (0.05 sec)
```

All：保存所有的结果

```
mysql> select * from class union all select * from class;
+----------+------------+--------------+
| class_id | class_name | class_number |
+----------+------------+--------------+
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
|        1 | 一年级     |           60 |
|        2 | 二年级     |           40 |
|        3 | 三年级     |           50 |
|        4 | 四年级     |           45 |
|        5 | 五年级     |           49 |
|        6 | 六年级     |           56 |
+----------+------------+--------------+
12 rows in set (0.03 sec)
```

注意细节：union理论上只要保证字段数一样，不需要每次拿到的数据对应的字段类型一致。永远只保留第一个select语句对应的字段名字。

```
mysql> select class_id,class_name,class_number from class
    ->union all
    ->select class_number,class_name,class_id from class;
```

## 4、Order by的使用

1、 在联合查询中，如果要使用order by，那么对应的select语句必须使用括号括起来

2、 orderby在联合查询中若要生效，必须配合使用limit：而limit后面必须跟对应的限制数量（通常可以使用一个较大的值：大于对应表的记录数）


---

# 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, and the optional `goal` query parameter:

```
GET https://beret81.gitbook.io/study-notes/mysql-shu-ju-ku/lian-he-cha-xun.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.