PostgreSQL UNION ALL statement

Preamble

PostgreSQL UNION ALL statement is used to combine the resulting sets of 2 or more SELECT operators. It returns all rows from a query and does not delete repeating rows between different SELECT statements.

Each SELECT statement in a PostgreSQL UNION ALL operator must have the same number of fields in the result sets with the same data types.

The syntax for a UNION ALL statement in PostgreSQL

SELECT expression1_id, expression2_id,... expression_n_id
FROM tabs
[WHERE conds]
UNION ALL
SELECT expression1_id, expression2_id,... expression_n_id
FROM tabs
[WHERE conds];

Parameters and arguments of the statement

• expression1_id, expression2_id,…_n_id – the column or calculation that you want to get.
• tabs – The tables from which you want to get the records. The FROM operator must specify at least one table.
• WHERE conds – Optional. The conditions to be met for the records to be selected.

Note:

• Both SELECT operators must have the same number of expressions.
• The column names from the first SELECT statement are used as column names for the result set.

Example with the return of a single field

Below is an example of PostgreSQL operator UNION ALL, which returns one field from several SELECT operators (and both fields have the same data type):

SELECT category_id
FROM products
UNION ALL
SELECT category_id
FROM categories;

This PostgreSQL UNION ALL operator will return category_id multiple times in your result set if category_id is present in the categories and product tables. PostgreSQL UNION ALL does not remove duplicates. If you want to remove duplicates, try using the PostgreSQL UNION statement.

Example using ORDER BY

PostgreSQL operator UNION ALL can use ORDER BY operator to organize results.
For example:

SELECT product_id, product_name
FROM products
WHERE product_name LIKE 'S%'
UNION
SELECT category_id, category_name
FROM categories
WHERE category_id < 99
ORDER BY 2;

In this example, since the column names of the two SELECT operators are different, it is more advantageous to refer to the columns in ORDER BY by their position in the resulting set. In this example, we have sorted the results by product_name / category_name in ascending order as ORDER BY 2.

The product_name / category_name fields are at position #2 in the resulting set.

SQL Union and Union All – SQL Training Online

About Enteros

IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.