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

表结构：

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)

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)

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)

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。

最后

以上就是开心钢铁侠为你收集整理的PostgreSQL UNION[ALL],INTERSECT [ALL],EXCEPT [ALL]的全部内容，希望文章能够帮你解决PostgreSQL UNION[ALL],INTERSECT [ALL],EXCEPT [ALL]所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。