PostgreSQL: UNION Operator
Postgres Union operator is used to combine the result set of two or more SELECT statements into a single result set.
It returns all rows returned by both the query result sets. By default, the UNION operator removes duplicate rows from the combined data set.
To get duplicate rows, use the UNION ALL operator. UNION ALL operator performs better than the UNION operator. Use the UNION ALL operator If there is no duplicate data in each data set.
SELECT Query 1 UNION SELECT Query 2 UNION SELECT Query 3 ...
The following rule must be applied to combine the result set of two or more quires using the UNION operator.
- There must be the same number of columns or expressions in each SELECT statement.
- The corresponding columns in each SELECT statement must have similar data types.
We will use the following
Person tables to understand how UNION and UNION ALL operator works.
Now let's use the
UNION operator to fetch data from both the tables.
SELECT * FROM Employee UNION SELECT * FROM Person;
The following displays the result in pgAdmin.
You can also include column names in the SELECT clause. However, number of columns and data types of columns must be the same.
SELECT first_name, last_name FROM Employee UNION SELECT first_name, last_name FROM Person;
If the number of columns or types of columns is different then it will raise an error. In the following example, the first query includes two columns of character type whereas the second query includes the first column of integer type. So, it will raise an error, as shown below.
You can even combine result of three or more SELECT queries, as shown below.
SELECT id, first_name FROM Employee UNION SELECT id, first_name FROM Person UNION SELECT dept_id, dept_name from Department
UNION ALL Operator
Now let's use the UNION ALL operator to combine results from the
SELECT * FROM Employee UNION ALL SELECT * FROM Person;
The following displays the result of the above query. You can see that UNION ALL does not remove duplicate data from the result.
ORDER BY clause is optional in the syntax. When you use the UNION or UNION ALL operator, the result set will show rows in any order from each of the queries. To sort out rows of the final result set, the ORDER BY clause is used at the end of the last query like below.
SELECT * FROM Employee UNION ALL SELECT * FROM Person ORDER BY ID;