5.30. PostgreSQL UNION operator

The PostgreSQL UNION operation matches the result of combining two or more SELECT statements.

The UNION operator is used to merge the result sets of two or more SELECT statements.

Note that each SELECT statement within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order.

5.30.1. Grammar ¶

UNIONS The basic syntax is as follows:

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

The conditional statement here can set any expression according to your needs.

5.30.2. Example ¶

Create COMPANY 表（下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# 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) 

Create DEPARTMENT 表（下载 DEPARTMENT SQL 文件 ), the data are as follows:

runoobdb=# 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) 

Now, we’re here. SELECT Statement using the UNION Clause joins the two tables as follows:

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; 

The results are as follows:

 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) 

5.30.3. UNION ALL clause ¶

UNION ALL Operator can join two duplicate lines SELECT Statement, by default UNION Operator selects a different value. If duplicate values are allowed, use the UNION ALL .

5.30.4. Grammar ¶

UINON ALL The basic syntax of the clause is as follows:

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

The conditional statement here can set any expression according to your needs.

5.30.5. Example ¶

Now, let’s use the two tables mentioned above. SELECT Sentence combination UNION ALL Clauses are joined together:

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; 

The results are as follows:

 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) 