PostgreSQL UNION 用于合并两个或多个SELECT语句的输出,而不返回任何重复的行。
要使用UNION,每个SELECT必须具有相同数量的选定列,相同数量的列表达式,相同数据类型,并且具有相同的顺序,但它们的长度不必相同。
Unions - 语法
UNION 的基本语法如下:
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
在此,给定条件可以是根据您的要求的任何给定表达式。
Unions - 示例
考虑以下两个表,(a) COMPANY 表如下-
testdb=# SELECT * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Learnfk | 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 如下-
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子句将这两个表连接起来,如下所示:
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;
这将产生以下输出-
emp_id | name | dept --------+-------+-------------- 5 | David | Engineering 6 | Kim | Finance 2 | Learnfk | 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]
在此,给定条件可以是根据您的要求的任何给定表达式。
UNION ALL - 示例
现在,让无涯教程在SELECT语句中将上述两个表连接如下:
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;
这将产生以下输出-
emp_id | name | dept --------+-------+-------------- 1 | Paul | IT Billing 2 | Learnfk | Engineering 7 | James | Finance 3 | Teddy | Engineering 4 | Mark | Finance 5 | David | Engineering 6 | Kim | Finance 1 | Paul | IT Billing 2 | Learnfk | Engineering 7 | James | Finance 3 | Teddy | Engineering 4 | Mark | Finance 5 | David | Engineering 6 | Kim | Finance (14 rows)
参考链接
https://www.learnfk.com/postgresql/postgresql-unions-clause.html
标签:PostgreSQL,Finance,UNION,无涯,ID,Unions,DEPARTMENT,Engineering,SELECT From: https://blog.51cto.com/u_14033984/8957433