PostgreSQL Union All Query

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.

Basic Usage

The general syntax for the UNION query in PostgreSQL is:

SELECT col1, col2, col3,…coln FROM tb1 UNION SELECT col1, col2, col3,…coln FROM tb2 WHERE condition;

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:

DROP DATABASE IF EXISTS union_db;
CREATE DATABASE union_db;
DROP TABLE IF EXISTS top_database;
CREATE TABLE top_database(
id serial,
db_name VARCHAR NOT NULL
);
DROP TABLE IF EXISTS all_db;
CREATE TABLE all_db(
id SERIAL,
db_name VARCHAR
);
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:

SELECT * from top_db UNION top_database;

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.