PostgreSQL UNION子句/运算符用于组合两个或多个SELECT语句的结果,而不返回任何重复的行。
要使用UNION,每个SELECT必须具有相同的列数,相同数量的列表达式,相同的数据类型,并且具有相同的顺序,但不一定要相同。
UNION的基本语法如下:
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] UNION SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
这里根据您的要求给出的条件表达式。
示例:
考虑以下两个表,COMPANY
表如下:
zyiz_db=# SELECT * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- | Paul | 32 | California| 20000 | Allen | 25 | Texas | 15000 | Teddy | 23 | Norway | 20000 | Mark | 25 | Rich-Mond | 65000 | David | 27 | Texas | 85000 | Kim | 22 | South-Hall| 45000 | James | 24 | Houston | 10000 (7 rows)
另一张表是DEPARTMENT
如下:
zyiz_db=# SELECT * from DEPARTMENT; id | dept | emp_id ----+-------------+-------- | IT Billing | 1 | Engineering | 2 | Finance | 7 | Engineering | 3 | Finance | 4 | Engineering | 5 | Finance | 6 (7 rows)
现在使用SELECT
语句和UNION
子句连接这两个表,如下所示:
zyiz_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 --------+-------+-------------- | David | Engineering | Kim | Finance | Allen | Engineering | Teddy | Engineering | Mark | Finance | Paul | IT Billing | 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语句中加入上面提到的两个表,如下所示:
zyiz_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 --------+-------+-------------- | Paul | IT Billing | Allen | Engineering | James | Finance | Teddy | Engineering | Mark | Finance | David | Engineering | Kim | Finance | Paul | IT Billing | Allen | Engineering | James | Finance | Teddy | Engineering | Mark | Finance | David | Engineering | Kim | Finance (14 rows)