# 集合运算

## 加减运算

对一堆集合进行`并集、交集、差集`运算

* `UNION`并集：表相加

创建一张新的和Product一样的表Product2，插入一些数据，部分和Product一样，又多了新的数据，现在开始相加表`Product+Product2`，以下查询结果包含两个表的并数据：

```
SELECT product_id,product_name
 FROM Product
UNION
SELECT product_id,product_name
 FROM Prodcut2;
```

![](/files/-LokoTTowwRPVcs_72wB)

PS:查询结果去重复了，也可以包含重复(`UNION ALL`)；查询时几个表的列要一一对应数量一致；`ORDER BY`只能在最后面写。

* `INTERSECT`交集，取表的公共数据，要重复同样可以加ALL

```
SELECT product_id,product_name
 FROM Product
INTERSECT
SELECT product_id,product_name
 FROM Prodcut2;
```

![](/files/-LokoTTqxFeSoyOt8_pU)

* `EXCEPT`差集，`Product-Product2`，上面的减下面的

```
SELECT product_id,product_name
 FROM Product
EXCEPT
SELECT product_id,product_name
 FROM Prodcut2
ORDER BY product_id;
```

## 联结

以`列为单位`对表进行联结(导致列数变化)，通常用于一张表无法获得全部期望数据时；上面的UNION等是以`行为单位`进行操作(导致行数变化)；

形象的表示联结：

![](/files/-LokoTTsFztaCRsLEKOx)

### 内联结 INNER JOIN

如图所示联结两个表，这两个表只有product\_id是公共的

![](/files/-LokoTTuFIUvM4WCE-hS)

![](/files/-LokoTTw6mUwsDK3JIO3)

内联结，从Product中取出product\_name、sale\_price与ShopProduct表内容结合，

**ON 用于指定联结的键，写在FROM 和 WHERE之间**

联结后的结果可以看作一个新表

```
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price
 FROM ShopProduct AS SP INNER JOIN Product AS P
  ON SP.product_id = P.product_id;
```

![](/files/-LokoTTyJSoLur1w6nhR)

### 外联结 OUTER JOIN

上表改为外联结：

```
SELECT SP.shop_id,SP.shop_name,SP.product_id,P.product_name,P.sale_price
 FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
  ON SP.product_id = P.product_id;
```

结果新表数据多了Product中有但是ShopProduct中没有的，

（**内联结选出多表都满足ON的，外联结只要一张表有这数据就选出**）

（外联结不公共的部分就是NULL选出，这样一行有部分缺失）

（外联结的`LEFT、RIGHT`用于选择哪个是主表，`主表拥有全部数据`，LEFT时左侧是主表）

![](/files/-LokoTU-zEwNBPT4GOeF)

#### 3张表联结

一层层联结，可以想象为：`(SP INNER JOIN P) INNER JOIN IP`:先SP表联结P表为一个新表，新表再联结IP表，和两个表一样操作

![](/files/-LokoTU15p1WtUkeJdJe)

## CROSS JOIN交叉联结

使用场景比较少，对联结的两个表数据进行`笛卡尔积`组合输出，A表13条，B表8条，结果13X8=104条


---

# 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://im-qianuxn.gitbook.io/pytorch/ji-suan-ji/shu-ju-ku/mysql/ji-he-yun-suan.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.