PostgreSQL – UNION Operator
The UNION operator is used to combine the result sets of two or more SELECT queries into a single result set. The UNION operator removes duplicate rows from the combined result by default. If you want to include duplicate rows, you can use the UNION ALL operator.
The basic syntax of the UNION operator is as follows:
SELECT columns FROM table_name1 UNION SELECT columns FROM table_name2;And for UNION ALL:
SELECT columns FROM table_name1 UNION ALL SELECT columns FROM table_name2;Here’s an example illustrating the usage of UNION operator:
Consider two tables named customers and suppliers, both with columns id, name, and location. Here is the query to retrieve a list of all unique names from both tables.
SELECT name FROM customers UNION SELECT name FROM suppliers;In this query, the UNION operator combines the distinct names from both the customers and suppliers tables into a single result set.
If you want to include duplicate names, you can use UNION ALL:
SELECT name FROM customers UNION ALL SELECT name FROM suppliers;The UNION and UNION ALL operators can be useful when you need to combine the results of multiple queries and ensure uniqueness (in the case of UNION) or include all rows regardless of duplicates (in the case of UNION ALL).
The number of columns and their data types must be the same in both SELECT statements when using the UNION or UNION ALL operator.