PostgreSQL – UNION ALL Operator
The UNION ALL operator is used to combine the result sets of two or more SELECT queries into a single result set, including all rows, even if there are duplicates. Unlike the regular UNION operator, UNION ALL does not remove duplicate rows from the combined result.
The basic syntax of the UNION ALL operator is as follows:
SELECT columns FROM table_name1 UNION ALL SELECT columns FROM table_name2;Here’s an example to illustrate how to use the UNION ALL operator:
Consider two tables named employees and contractors, both with columns id, name, and position. Here is the query to retrieve a list of all names and positions from both tables, including duplicates.
SELECT name, position FROM employees UNION ALL SELECT name, position FROM contractors;In this query, the UNION ALL operator combines the names and positions from both the employees and contractors tables into a single result set. Duplicate rows are retained in the final result.
Use the UNION ALL operator when you want to combine result sets from multiple queries while maintaining all rows, including duplicates. This can be useful when you’re interested in a comprehensive overview of the data without removing any duplicates.