PostgreSQL UNION statement

Preamble

PostgreSQL UNION statement is used to combine the resulting sets of 2 or more SELECT operators. It removes repetitive lines between different SELECT operators.

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

The syntax for a UNION statement in PostgreSQL

SELECT expression1_id, expression2_id,... expression_n_id
FROM tabs
[WHERE conds]
UNION
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 the calculation 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 statements must have the same number of expressions.
• Since the UNION operator by default removes all repetitive strings from the result set, providing a UNION DISTINCT modifier does not affect the results.
• The column names from the first SELECT operator in UNION are used as column names for the result set.

Example with the return of a single field

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

SELECT category_id
FROM products
UNION
SELECT category_id
FROM categories;

In this example of the PostgreSQL UNION operator, if a category_id appears in both the products table and the categories table, it will appear in your resulting set once. The PostgreSQL UNION statement removes duplicates. If you do not want to remove duplicates, try using PostgreSQL UNION ALL operator.

Example using the ORDER BY statement

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

SELECT product_id, product_name
FROM products
WHERE product_id >= 24
UNION
SELECT category_id, category_name
FROM categories
WHERE category_name <> 'Hardware'
ORDER BY 2;

In this PostgreSQL operator UNION, since column names in two SELECT operators are different, it is more advantageous to refer to columns in ORDER BY operator 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. is used to combine the resulting sets of 2 or more SELECT operators. It removes repetitive lines between different SELECT operators.

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

PostgreSQL Tutorial – UNION and UNION ALL

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.