postgresql union——迹忆客-ag捕鱼王app官网

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

要使用 union，每个 select 必须选择相同数量的列、相同数量的列表达式、相同的数据类型，并且它们的顺序相同。但是，长度不必相同。

语法

unions 基础语法如下：

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

这里的条件语句可以根据您的需要设置任何表达式。

示例

创建 ，数据内容如下：

jiyik_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)

创建 ，数据内容如下：

jiyik_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 子句将两张表连接起来，如下所示：

jiyik_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。

语法

uinon all 子句基础语法如下：

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

这里的条件语句可以根据您的需要设置任何表达式。

示例

现在，让我们把上面提到的两张表用 select 语句结合 union all 子句连接起来：

jiyik_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)