炫意html5
最早CSS3和HTML5移动技术网站之一

PostgreSQL UNION

PostgreSQL UNION 操作符合并两个或多个 SELECT 语句的结果。

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。

语法

UNIONS 基础语法如下:

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

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

实例

创建 COMPANY 表(下载 COMPANY SQL 文件 ),数据内容如下:

shulanxtdb# 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 表(下载 COMPANY SQL 文件 ),数据内容如下:

shulanxtdb=# 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 子句将两张表连接起来,如下所示:

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 子句连接起来:

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)

炫意HTML5 » PostgreSQL UNION

Java基础教程Android基础教程