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. The conditional statement here can set any expression according to your needs. Create Create Now, we’re here. The results are as follows: The conditional statement here can set any expression according to your needs. Now, let’s use the two tables mentioned above. The results are as follows: 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]
5.30.2. Example ¶
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)
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)
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;
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]
5.30.5. Example ¶
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;
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)