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:
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:
The above query should return a single set with the values combined as shown below:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/image1-32.png" data-lazy- height="376" src="data:image/svg xml,” width=”698″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/image2-32.png" data-lazy- height="414" src="data:image/svg xml,” width=”709″>
Conclusion
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.
About the author
<img alt="" data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2021/08/echo/john-150×150.png61245ed31fd19.jpg" height="112" src="data:image/svg xml,” width=”112″>
John Otieno
My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list
