# 集合运算

TiDB 支持三种集合运算：并集 (UNION)，差集 (EXCEPT) 和交集 (INTERSECT)。最小的集合单位是一个 `SELECT` 语句

## 并集 (UNION)

``````.css-1qhimia{margin-right:3rem;overflow:auto;}select 1 union select 2;
+---+
| 1 |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.00 sec)
.css-ux9q70{text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:2.4rem;background:transparent;border:unset;}.css-ux9q70:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-ux9q70:hover{background-color:transparent;}}.css-ux9q70.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}.css-1rmx1rm{display:-webkit-inline-box;display:-webkit-inline-flex;display:-ms-inline-flexbox;display:inline-flex;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;-webkit-box-pack:center;-ms-flex-pack:center;-webkit-justify-content:center;justify-content:center;position:relative;box-sizing:border-box;-webkit-tap-highlight-color:transparent;background-color:transparent;outline:0;border:0;margin:0;border-radius:0;padding:0;cursor:pointer;-webkit-user-select:none;-moz-user-select:none;-ms-user-select:none;user-select:none;vertical-align:middle;-moz-appearance:none;-webkit-appearance:none;-webkit-text-decoration:none;text-decoration:none;color:inherit;text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:2.4rem;background:transparent;border:unset;}.css-1rmx1rm::-moz-focus-inner{border-style:none;}.css-1rmx1rm.Mui-disabled{pointer-events:none;cursor:default;}@media print{.css-1rmx1rm{-webkit-print-color-adjust:exact;color-adjust:exact;}}.css-1rmx1rm:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-1rmx1rm:hover{background-color:transparent;}}.css-1rmx1rm.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}.css-1e2dcm1{z-index:1500;pointer-events:none;}.css-okvapm{z-index:1500;pointer-events:none;}.css-rnn29l{text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:0.625rem;background:transparent;border:unset;}.css-rnn29l:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-rnn29l:hover{background-color:transparent;}}.css-rnn29l.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}.css-fxo539{display:-webkit-inline-box;display:-webkit-inline-flex;display:-ms-inline-flexbox;display:inline-flex;-webkit-align-items:center;-webkit-box-align:center;-ms-flex-align:center;align-items:center;-webkit-box-pack:center;-ms-flex-pack:center;-webkit-justify-content:center;justify-content:center;position:relative;box-sizing:border-box;-webkit-tap-highlight-color:transparent;background-color:transparent;outline:0;border:0;margin:0;border-radius:0;padding:0;cursor:pointer;-webkit-user-select:none;-moz-user-select:none;-ms-user-select:none;user-select:none;vertical-align:middle;-moz-appearance:none;-webkit-appearance:none;-webkit-text-decoration:none;text-decoration:none;color:inherit;text-align:center;-webkit-flex:0 0 auto;-ms-flex:0 0 auto;flex:0 0 auto;font-size:1.5rem;padding:8px;border-radius:50%;overflow:visible;color:rgba(0, 0, 0, 0.54);-webkit-transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;transition:background-color 150ms cubic-bezier(0.4, 0, 0.2, 1) 0ms;padding:5px;font-size:1.125rem;position:absolute;top:0.625rem;right:0.625rem;background:transparent;border:unset;}.css-fxo539::-moz-focus-inner{border-style:none;}.css-fxo539.Mui-disabled{pointer-events:none;cursor:default;}@media print{.css-fxo539{-webkit-print-color-adjust:exact;color-adjust:exact;}}.css-fxo539:hover{background-color:rgba(0, 0, 0, 0.04);}@media (hover: none){.css-fxo539:hover{background-color:transparent;}}.css-fxo539.Mui-disabled{background-color:transparent;color:rgba(0, 0, 0, 0.26);}``````

TiDB 支持 `UNION ALL``UNION DISTINCT` 并集，两者区别在于 `UNION DISTINCT` 会对并集结果去重复，而 `UNION ALL` 不会。TiDB 中默认使用 `UNION DISTINCT`

``````create table t1 (a int);
create table t2 (a int);
insert into t1 values (1),(2);
insert into t2 values (1),(3);
``````

`UNION DISTINCT``UNION ALL` 的结果分别如下：

``````select * from t1 union distinct select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.00 sec)

select * from t1 union all select * from t2;
+---+
| a |
+---+
| 1 |
| 2 |
| 1 |
| 3 |
+---+
4 rows in set (0.00 sec)
``````

## 差集 (EXCEPT)

``````select * from t1 except select * from t2;
+---+
| a |
+---+
| 2 |
+---+
1 rows in set (0.00 sec)
``````

## 交集 (INTERSECT)

``````select * from t1 intersect select * from t2;
+---+
| a |
+---+
| 1 |
+---+
1 rows in set (0.00 sec)
``````

``````select * from t1 union all select * from t1 intersect select * from t2;
+---+
| a |
+---+
| 1 |
| 1 |
| 2 |
+---+
3 rows in set (0.00 sec)
``````

## 括号优先

TiDB 支持使用括号修改集合运算的优先级，如同四则运算中先计算括号部分，集合运算也先计算括号内的部分。

``````(select * from t1 union all select * from t1) intersect select * from t2;
+---+
| a |
+---+
| 1 |
+---+
1 rows in set (0.00 sec)
``````

## 与 `Order By` 和 `Limit` 结合

TiDB 支持单独为整个集合运算进行 `ORDER BY` 或者 `LIMIT`

``````(select * from t1 union all select * from t1 intersect select * from t2) order by a limit 2;
+---+
| a |
+---+
| 1 |
| 1 |
+---+
2 rows in set (0.00 sec)
``````

TiDB
TiDB Cloud