PostgreSQL UNION子句/运算符用于组合两个或多个SELECT语句的结果，而不返回任何重复的行。
要使用UNION，每个SELECT必须具有相同的列数，相同数量的列表达式，相同的数据类型，并且具有相同的顺序，但不一定要相同。
UNION的基本语法如下：
| SELECT column1 [, column2 ] |
| FROM table1 [, table2 ] |
| [WHERE condition] |
| UNION |
| SELECT column1 [, column2 ] |
| FROM table1 [, table2 ] |
| [WHERE condition] |
这里根据您的要求给出的条件表达式。
示例：
考虑以下两个表，COMPANY表如下：
| web3_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如下：
| web3_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子句连接这两个表，如下所示：
| web3_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运算符用于组合两个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语句中加入上面提到的两个表，如下所示：
| web3_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) |
