PostgreSQL UNION子句/運(yùn)算符用于組合兩個(gè)或多個(gè)SELECT語句的結(jié)果,而不返回任何重復(fù)的行。
要使用UNION,每個(gè)SELECT必須具有相同的列數(shù),相同數(shù)量的列表達(dá)式,相同的數(shù)據(jù)類型,并且具有相同的順序,但不一定要相同。
UNION的基本語法如下:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
這里根據(jù)您的要求給出的條件表達(dá)式。
示例:
考慮以下兩個(gè)表,COMPANY
表如下:
yiibai_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
如下:
yiibai_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)
現(xiàn)在使用SELECT
語句和UNION
子句連接這兩個(gè)表,如下所示:
yiibai_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;
這將產(chǎn)生以下結(jié)果:
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
運(yùn)算符用于組合兩個(gè)SELECT
語句(包括重復(fù)行)的結(jié)果。 適用于UNION的相同規(guī)則也適用于UNION ALL
運(yùn)算符。
語法:
UNION ALL的基本語法如下:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
這里根據(jù)您的要求給出的條件表達(dá)式。
示例:
現(xiàn)在,我們在SELECT語句中加入上面提到的兩個(gè)表,如下所示:
yiibai_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;
這將產(chǎn)生以下結(jié)果:
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)