This tutorial will show you how to use PostgreSQL Union and Union All queries to combine results from select statements.
How PostgreSQL Union Query Works
The PostgreSQL query is pretty straightforward. It works by combining two or more SELECT data result to create a single larger set.
For example, if we take one result of a select statement as X and the result of another select statement as Y, the resulting UNION of these two statements is the total of both SELECT X and Y without any duplicates.
The general syntax for the UNION query in PostgreSQL is:
Although you will mostly be selecting specific columns, you can pass other valid PostgreSQL expressions to the select statements.
PostgreSQL Union Query Example
Let us illustrate how to use the UNION query using a simple example.
Start by creating a sample database and populate it with sample data as shown in the queries below:
CREATE DATABASE union_db;
DROP TABLE IF EXISTS top_database;
CREATE TABLE top_database(
db_name VARCHAR NOT NULL
DROP TABLE IF EXISTS all_db;
CREATE TABLE all_db(
INSERT INTO top_database(db_name) VALUES ('MySQL'), ('PostgreSQL'), ('Microsoft SQL Server'), ('SQLite'), ('MongoDB');
INSERT INTO all_db(dB_name) VALUES ('MySQL'), ('Elasticsearch'), ('SQLite'), ('DynamoDB'), ('Redis');
Using the above sample database and tables, we can perform a UNION as:
The above query should return a single set with the values combined as shown below:
To run a UNION query successfully, the specified number and order of columns in the select statements must be similar, and the data types must be compatible.
PostgreSQL Union All
A query similar to the UNION statement is the UNION ALL. This query works the same way the UNION does but does not remove duplicate values from the specified set.
We can illustrate this functionality by using the same query above.
In this case, we should return the combined values including the duplicates as shown below:
Both UNION and UNION ALL have their specific use cases. They are useful to developers because they make it easier to aggregate data into various sets.