intersect & union | union all & minus

原创已于 2022-03-06 17:41:29 修改 · 2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

于 2022-03-06 17:39:22 首次发布

DBA 专栏收录该内容

1069 篇文章

订阅专栏

本文详细介绍了SQL中的交集(INTERSECT)、并集(UNION UNIONALL)和差集(MINUS)操作。INTERSECT用于找出两个查询结果的共同部分，同时处理NULL值；UNION和UNION ALL的区别在于是否去除重复行，UNION ALL保留所有记录而不去重；MINUS则返回第一表中但不在第二表中的记录。了解这些操作对于数据库查询和数据处理至关重要。

区别

intersect 交集 去重 排序
union 并集 去重 排序
union all 并集 不去重 不排序
minus 差集 前表中有，后表中没有的差集

intersect

It processes NULLs in the selected columns.

union | union all

The number of columns selected in each SELECT statement must be identical.

NULLS are not ignored during duplicate checking.

The data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.

The number, but not names, of columns must be identical for all SELECT statements in the query.

minus

SALES2 is a table with the same description as SALES1. Some sales data is duplicated in both tables.You want to display the rows from the SALES1 table which are not present in the SALES2 table.