Postgresql 简明教程

PostgreSQL - UNIONS Clause

PostgreSQL UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，而不会返回任何重复行。

The PostgreSQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

要使用 UNION，每个 SELECT 的选取的列数、列表达式数量、数据类型，以及顺序必须相同，但长度不必相同。

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order but they do not have to be the same length.

Syntax

UNION 的基本语法如下：

The basic syntax of UNION is as follows −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

在此，给出的条件可基于您的要求，为任何给定的表达式。

Here, given condition could be any given expression based on your requirement.

Example

请查看以下两个表，（a） COMPANY 表如下所示：

Consider the following two tables, (a) COMPANY table is as follows −

testdb=# 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)

（b）另一张表 DEPARTMENT 如下所示：

(b) Another table is DEPARTMENT as follows −

testdb=# 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 子句将这两个表联接如下 −

Now let us join these two tables using SELECT statement along with UNION clause as follows −

testdb=# 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;

这将产生以下结果 -

This would produce the following result −

 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)

The UNION ALL Clause

UNION ALL 运算符用于合并两个 SELECT 语句的结果，包括重复行。应用于 UNION 的规则也适用于 UNION ALL 运算符。

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows. The same rules that apply to UNION apply to the UNION ALL operator as well.

Syntax

UNION ALL 的基本语法如下：

The basic syntax of UNION ALL is as follows −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

在此，给出的条件可基于您的要求，为任何给定的表达式。

Here, given condition could be any given expression based on your requirement.

Example

现在，让我们将上述两个表加入我们的 SELECT 语句中，如下所示：

Now, let us join above-mentioned two tables in our SELECT statement as follows −

testdb=# 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;

这将产生以下结果 -

This would produce the following result −

 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)