How to Use the Postgres UNION Operator
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
SELECTstatements joined by theUNIONoperator have the same number of columns.The columns in the
SELECTstatements 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