PostgreSQL UNIONS子句

作者：--    发布时间：2019-11-20

postgresql union子句/运算符用于组合两个或多个select语句的结果，而不返回任何重复的行。

要使用union，每个select必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同。

语法：

union的基本语法如下：

select column1 [, column2 ]
from table1 [, table2 ]
[where condition]

union

select column1 [, column2 ]
from table1 [, table2 ]
[where condition]

这里根据您的要求给出的条件表达式。

示例：
考虑以下两个表，company表如下：

h3_db=# select * from company;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | paul  |  32 | california|  20000
  2 | allen |  25 | texas     |  15000
  3 | teddy |  23 | norway    |  20000
  4 | mark  |  25 | rich-mond |  65000
  5 | david |  27 | texas     |  85000
  6 | kim   |  22 | south-hall|  45000
  7 | james |  24 | houston   |  10000
(7 rows)

另一张表是department如下：

h3_db=# select * from department;
 id | dept        | emp_id
----+-------------+--------
  1 | it billing  |      1
  2 | engineering |      2
  3 | finance     |      7
  4 | engineering |      3
  5 | finance     |      4
  6 | engineering |      5
  7 | finance     |      6
(7 rows)

现在使用select语句和union子句连接这两个表，如下所示：

h3_db=# select emp_id, name, dept from company inner join department
        on company.id = department.emp_id
   union
     select emp_id, name, dept from company left outer join department
        on company.id = department.emp_id;

这将产生以下结果：

 emp_id | name  |  dept
--------+-------+--------------
      5 | david | engineering
      6 | kim   | finance
      2 | allen | engineering
      3 | teddy | engineering
      4 | mark  | finance
      1 | paul  | it billing
      7 | james | finance
(7 rows)

union all子句

union all运算符用于组合两个select语句(包括重复行)的结果。 适用于union的相同规则也适用于union all运算符。

语法：
union all的基本语法如下：

select column1 [, column2 ]
from table1 [, table2 ]
[where condition]

union all

select column1 [, column2 ]
from table1 [, table2 ]
[where condition]

这里根据您的要求给出的条件表达式。

示例：
现在，我们在select语句中加入上面提到的两个表，如下所示：

h3_db=# select emp_id, name, dept from company inner join department
        on company.id = department.emp_id
   union all
     select emp_id, name, dept from company left outer join department
        on company.id = department.emp_id;

这将产生以下结果：

 emp_id | name  | dept
--------+-------+--------------
      1 | paul  | it billing
      2 | allen | engineering
      7 | james | finance
      3 | teddy | engineering
      4 | mark  | finance
      5 | david | engineering
      6 | kim   | finance
      1 | paul  | it billing
      2 | allen | engineering
      7 | james | finance
      3 | teddy | engineering
      4 | mark  | finance
      5 | david | engineering
      6 | kim   | finance
(14 rows)