How to Use the Postgres UNION Operator

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re learning how to construct queries in PostgreSQL, you may want to know if it’s possible to combine the results of two or more SELECT statements in one table. The UNION operator makes it easy to accomplish this task and get the results you need. In this article, we’ll discuss the Postgres UNION operator and provide some examples to show how it’s used.

Prerequisites

In order to get the most out of this tutorial, a couple of important prerequisites need to be in place:

  • You’ll need to have PostgreSQL installed on your computer so that you can try out the example queries yourself.

  • It’s helpful to have some basic knowledge of PostgreSQL in order to follow along with the instructions and examples in this article.

The PostgreSQL UNION Syntax

Let’s begin our discussion by looking at the basic syntax for the Postgres UNION operator, which is used to combine the results of two queries:

1
2
SELECT column_name FROM table_name1 UNION
SELECT column_name FROM table_name2;
  • Make sure that both of the SELECT statements joined by the UNION operator have the same number of columns.

  • The columns in the SELECT statements must have the same data types.

The UNION operator removes the duplicate rows from the returned results. If you prefer to get all the records without removing duplicate values, use the UNION ALL operator.

The following example shows the syntax for the UNION ALL operator. This operator combines the results of two queries and allows for duplicate values in the results set:

1
2
3
SELECT column_name FROM table_name1
UNION ALL
SELECT column_name FROM table_name2;

Postgres UNION example

In this section, we’ll create two tables that will be used in our examples. We’ll name these tables “dogbreed” and “catbreed”:

1
2
3
4
5
CREATE TABLE dogbreed(
id INT,
breed VARCHAR(30),
lifespan VARCHAR(30)
);
1
2
3
4
5
CREATE TABLE catbreed(
id INT,
breed VARCHAR(30),
lifespan VARCHAR(30)
);

Next, we’ll insert some records into our tables:

1
2
3
4
5
INSERT INTO dogbreed (id, breed, lifespan)
VALUES ('1', 'American Bulldog', '12-14 Years'),
('2', 'Chihuahua', '17 Years'),
('3', 'Toy Poodle', '16 Years'),
('2', 'Chihuahua', '17 Years');
1
2
3
4
INSERT INTO catbreed (id, breed, lifespan)
VALUES ('1', 'Persian', '15 Years'),
('2', 'Oriental', '10-15 Years'),
('3', 'Egyptian Mau', '13-16 Years');

The contents of our “dogbreed” table can be seen below:

1
2
3
4
5
6
7
 id |      breed       |  lifespan
----+------------------+-------------
  1 | American Bulldog | 12-14 Years
  2 | Chihuahua        | 17 Years
  3 | Toy Poodle       | 16 Years
  4 | Chihuahua        | 17 Years
(4 ROWS)

Here are the contents of our “catbreed” table:

1
2
3
4
5
6
 id |    breed     |  lifespan
----+--------------+-------------
  1 | Persian      | 15 Years
  2 | Oriental     | 10-15 Years
  3 | Egyptian Mau | 13-16 Years
(3 ROWS)

The following statement will return the rows from the “dogbreed” table and the “catbreed” table, removing all duplicate values:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM dogbreed
UNION
SELECT * FROM catbreed;
 id |      breed       |  lifespan
----+------------------+-------------
  3 | Toy Poodle       | 16 Years
  2 | Chihuahua        | 17 Years
  1 | Persian          | 15 Years
  2 | Oriental         | 10-15 Years
  1 | American Bulldog | 12-14 Years
  3 | Egyptian Mau     | 13-16 Years
(6 ROWS)

As you can see, it removed the duplicate values from the “dogbreed” table.

Postgres UNION ALL example

Now let’s modify the query from the previous example to allow duplicate values in the results. We’ll use the UNION ALL operator, as seen in the following statement:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM dogbreed
UNION ALL
SELECT * FROM catbreed;
 id |      breed       |  lifespan
----+------------------+-------------
  1 | American Bulldog | 12-14 Years
  2 | Chihuahua        | 17 Years
  3 | Toy Poodle       | 16 Years
  2 | Chihuahua        | 17 Years
  1 | Persian          | 15 Years
  2 | Oriental         | 10-15 Years
  3 | Egyptian Mau     | 13-16 Years
(7 ROWS)

As you can see, the duplicate values are now included in the results.

Using the WHERE clause in PostgreSQL

You can also add a WHERE clause to your SELECT statements when you join them using the Postgres UNION operator. The following query returns the breed of the dog and cat where the value of id is 1:

1
2
3
4
5
6
7
8
SELECT * FROM dogbreed WHERE id = 1
UNION ALL
SELECT * FROM catbreed WHERE id = 1;
 id |      breed       |  lifespan
----+------------------+-------------
  1 | American Bulldog | 12-14 Years
  1 | Persian          | 15 Years
(2 ROWS)

Conclusion

When you need to combine the results of multiple queries in PostgreSQL, the UNION operator can help you get the job done. In this article, we looked at several examples that utilize the Postgres UNION and UNION ALL operators. If you follow along with these examples, you’ll be prepared to use these operators in your own PostgreSQL queries.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.