In PostgreSQL, the UNION operator combines the results from multiple queries into a single result set.

Syntax

The official syntax goes like this:

query1 UNION [ALL] query2

The UNION operator appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned).

Duplicate rows are eliminated unless UNION ALL is used.

Example

Suppose we have the following tables:

SELECT * FROM Teachers;
SELECT * FROM Students;

Result:

 teacherid | teachername 
-----------+-------------
         1 | Warren
         2 | Ben
         3 | Cathy
         4 | Cathy
         5 | Bill
         6 | Bill

 studentid | studentname 
-----------+-------------
         1 | Faye
         2 | Jet
         3 | Spike
         4 | Ein
         5 | Warren
         6 | Bill

We can use the UNION operator to return all teachers and students:

SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Spike
 Ben
 Ein
 Cathy
 Jet
 Faye
 Bill
 Warren
(8 rows)

By default, the UNION operator implicitly applies a DISTINCT operation. In other words, it returns only distinct values by default. So the above results contain just one each of Warren, Cathy and Bill. This is despite the fact that the combined tables actually contain two Warrens, two Cathys, and three Bills (there are two teachers called Cathy, a teacher and a customer called Warren, and two called Bill, as well as one student called Bill).

Here’s an example that explicitly uses the DISTINCT operator:

SELECT TeacherName FROM Teachers
UNION DISTINCT
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Spike
 Ben
 Ein
 Cathy
 Jet
 Faye
 Bill
 Warren
(8 rows)

Same result.

Include Duplicates

We can use the ALL keyword to include duplicate values in the results:

SELECT TeacherName FROM Teachers
UNION ALL
SELECT StudentName FROM Students;

Result:

 teachername 
-------------
 Warren
 Ben
 Cathy
 Cathy
 Bill
 Bill
 Faye
 Jet
 Spike
 Ein
 Warren
 Bill
(12 rows)

This time we got twelve rows instead of the eight that we got in our first example.

We can see that both Cathys were returned and all three Bills were returned.