PostgreSQL UNION[ALL],INTERSECT [ALL],EXCEPT [ALL]

原创已于 2023-04-21 12:28:23 修改 · 3.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#PostgreSQL

于 2015-03-25 10:34:13 首次发布

PostgreSQL 基础专栏收录该内容

65 篇文章

订阅专栏

本文详细介绍了如何使用SQL的UNION, INTERSECT和EXCEPT操作符来处理查询结果集，并通过具体的例子展示了如何利用这些操作符进行数据筛选，包括合并结果集、查找交集和求差集。

表结构：

postgres=# \d person 
         Table "public.person"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | character varying |

数据：

postgres=# select * from person;
 id | name 
----+------
  1 | aa
  2 | bb
  3 | cc
  4 | dd
  5 | ee
  6 | aa
  7 | bb
  8 | aa
(8 rows)

1. UNION[ALL] 合并两个结果集，使用ALL不去除两个结果集中重复的。

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way asDISTINCT, unless UNION ALL is used.

postgres=# select name from person where id<5 union all select name from person where id>5;
 name 
------
 aa
 bb
 cc
 dd
 aa
 bb
(6 rows)

postgres=# select name from person where id<5 union select name from person where id>5;
 name 
------
 bb
 cc
 dd
 aa
(4 rows)

2. INTERSECT [ALL] 查询两个结果集的交集。

INTERSECT returns all rows that are both in the result ofquery1 and in the result ofquery2. Duplicate rows are eliminated unlessINTERSECT ALL is used.

postgres=# select name from person where id<5 intersect select name from person where id>5;
 name 
------
 bb
 aa
(2 rows)

3. EXCEPT [ALL] 查询在前一个结果集中但是不再后面一个结果集中的记录。

EXCEPT returns all rows that are in the result ofquery1 but not in the result ofquery2. (This is sometimes called thedifference between two queries.) Again, duplicates are eliminated unlessEXCEPT ALL is used.

postgres=# select name from person where id<5 except select name from person where id>5;
 name 
------
 cc
 dd
(2 rows)

4. note：
In order to calculate the union, intersection, or difference of two queries, the two queries must be"union compatible", which means that they return the same number of columns and the corresponding columns have compatible data types。