PostgreSQL UNION

In PostgreSQL, UNION is a set operator that returns the union of two sets.

Other set operators are: EXCEPT and INTERSECT.

PostgreSQL UNION syntax

To merge two result sets, use the use UNION operator:

SELECT_statement_1
UNION [ALL]
SELECT_statement_2
[ORDER BY ...];

Or you can merge multiple result sets at the same time, like this:

SELECT_statement_1
UNION [ALL]
SELECT_statement_2
UNION [ALL]
SELECT_statement_3
UNION [ALL]
...
[ORDER BY ...];

Here:

• The SELECT_statement_N are independent SELECT statements.

• All result sets participating in the UNION operation should have the same columns, and the data types and order of the columns should be the same.

• UNION and UNION ALL has different logic:

  • UNION merges the two result sets and remove duplicate rows.
  • UNION ALL merges the two results, keeping all rows.

• The ORDER BY clause is used to sort the final result and it is optional.

PostgreSQL UNION Examples

In this example, we will use the generate_series() function to generate two result sets.

We need to use two result sets, the following is the first result set:

SELECT generate_series(1, 5);

 generate_series
-----------------
               1
               2
               3
               4
               5

The following is the second result set:

SELECT generate_series(3, 6);

 generate_series
-----------------
               3
               4
               5
               6

Now, we merge the above two sets using the UNION operate:

SELECT generate_series(1, 5)
UNION
SELECT generate_series(3, 6)
ORDER BY generate_series;

 generate_series
-----------------
               1
               2
               3
               4
               5
               6
(6 rows)

It can be seen from the results that the UNION operator merges the first result set and the second result set, removes the duplicate rows, and returns the merged result set.

If you don’t want to delete duplicate rows, use the UNION ALL operator like this:

SELECT generate_series(1, 5)
UNION ALL
SELECT generate_series(3, 6)
ORDER BY generate_series;

 generate_series
-----------------
               1
               2
               3
               3
               4
               4
               5
               5
               6
(9 rows)

Conclusion

In PostgreSQL, UNION and UNION ALL are used to combine two or more result sets into one result set. The difference is that UNION will remove duplicate rows.