PostgreSQL: UNION Operator

Postgres Union operator is used to combine the result set of two or more SELECT statements into a single result set.

It returns all rows returned by both the query result sets. By default, the UNION operator removes duplicate rows from the combined data set.

To get duplicate rows, use the UNION ALL operator. UNION ALL operator performs better than the UNION operator. Use the UNION ALL operator If there is no duplicate data in each data set.

Syntax

SELECT Query 1
UNION
SELECT Query 2
UNION
SELECT Query 3
...

        

The following rule must be applied to combine the result set of two or more quires using the UNION operator.

There must be the same number of columns or expressions in each SELECT statement.
The corresponding columns in each SELECT statement must have similar data types.

We will use the following Employee and Person tables to understand how UNION and UNION ALL operator works.

Now let's use the UNION operator to fetch data from both the tables.

Example: UNION

SELECT * FROM Employee
UNION
SELECT * FROM Person;

The following displays the result in pgAdmin.

You can also include column names in the SELECT clause. However, number of columns and data types of columns must be the same.

Example: UNION

SELECT first_name, last_name FROM Employee
UNION 
SELECT first_name, last_name FROM Person;

If the number of columns or types of columns is different then it will raise an error. In the following example, the first query includes two columns of character type whereas the second query includes the first column of integer type. So, it will raise an error, as shown below.

You can even combine result of three or more SELECT queries, as shown below.

Example: UNION of Multiple Queries

SELECT id, first_name FROM Employee
UNION 
SELECT id, first_name FROM Person
UNION
SELECT dept_id, dept_name from Department

UNION ALL Operator

Now let's use the UNION ALL operator to combine results from the Employee and Person tables.

Example: UNION ALL

SELECT * FROM Employee
UNION ALL
SELECT * FROM Person;

The following displays the result of the above query. You can see that UNION ALL does not remove duplicate data from the result.

The ORDER BY clause is optional in the syntax. When you use the UNION or UNION ALL operator, the result set will show rows in any order from each of the queries. To sort out rows of the final result set, the ORDER BY clause is used at the end of the last query like below.

Example: UNION ALL

SELECT * FROM Employee
UNION ALL
SELECT * FROM Person ORDER BY ID;