PostgreSQL UNION

PostgreSQL UNION Operator

The UNION operator in PostgreSQL is used to combine the result sets of two or more SELECT queries into a single result set. It returns all unique rows that appear in any of the SELECT queries. If you want to include duplicate rows, you can use the UNION ALL operator.

1. Syntax of `UNION`

SELECT columns
FROM table1
UNION
SELECT columns
FROM table2;

The SELECT statements must have the same number of columns, and the corresponding columns should have compatible data types.

2. Example: Basic Use of `UNION`

Scenario:

Let’s assume we have two tables: employees and contractors. We want to find the people who are either employees or contractors (or both).

`employees` Table:

employee_id	name
1	Alice
2	Bob
3	Charlie
4	David

`contractors` Table:

contractor_id	name
2	Bob
4	David
5	Edward

Query: Find All Employees and Contractors (Without Duplicates)

SELECT name
FROM employees
UNION
SELECT name
FROM contractors;

Result:

name
Alice
Bob
Charlie
David
Edward

Explanation: The query combines the names of employees and contractors. Duplicates (Bob and David) are removed, so they appear only once.

3. Example: Using `UNION ALL` to Retain Duplicates

If you want to include duplicates (i.e., return rows that appear in both SELECT statements), you can use the UNION ALL operator.

Query: Find All Employees and Contractors (Including Duplicates)

SELECT name
FROM employees
UNION ALL
SELECT name
FROM contractors;

Result:

name
Alice
Bob
Charlie
David
Bob
David
Edward

Explanation: The UNION ALL operator includes duplicates. In this case, Bob and David appear twice, once as employees and once as contractors.

4. Using `UNION` with Multiple Columns

You can also use UNION to combine multiple columns from two or more SELECT queries.

`orders` Table:

order_id	customer_id	order_date
1	101	2025-01-01
2	102	2025-01-02
3	103	2025-01-03

`returns` Table:

return_id	customer_id	return_date
1	101	2025-01-05
2	102	2025-01-06

Query: Find All Customer IDs from Orders and Returns (Without Duplicates)

SELECT customer_id
FROM orders
UNION
SELECT customer_id
FROM returns;

Result:

customer_id
101
102
103

Explanation: This query returns a list of all unique customer IDs from both the orders and returns tables. Duplicates (like customer 101 and 102) are removed.

5. Important Points About `UNION`

Column Matching: Both SELECT statements must return the same number of columns, and the corresponding columns should have compatible data types.
Duplicates: UNION removes duplicates, while UNION ALL retains duplicates.
Sorting: By default, UNION sorts the result set, which may impact performance for large datasets. If you want to avoid this automatic sorting, use UNION ALL.

6. `UNION` vs `JOIN`

While both UNION and JOIN combine data from multiple tables, they do so in different ways:

UNION: Combines the result sets of two or more SELECT queries vertically (one result set below another).
JOIN: Combines data horizontally, by linking rows based on common columns.

Example of Using `JOIN`:

SELECT employees.name, contractors.name
FROM employees
JOIN contractors ON employees.name = contractors.name;

This query will return rows where employees are also contractors, and it combines data from both tables.

7. Example: Using `UNION` with `ORDER BY`

You can use ORDER BY to sort the combined result set after using UNION. If you want to order by a specific column from the combined data, you apply ORDER BY at the end.

Query: Combine Employees and Contractors, Then Sort by Name

SELECT name
FROM employees
UNION
SELECT name
FROM contractors
ORDER BY name;

Result:

name
Alice
Bob
Charlie
David
Edward

Explanation: The result set is sorted alphabetically by name.

8. Summary of `UNION`

Operator	Description
`UNION`	Combines the result sets of two or more `SELECT` statements and removes duplicates.
`UNION ALL`	Combines the result sets and retains duplicates.
Column Matching	Both queries must return the same number of columns with compatible data types.
Performance	`UNION` may be slower than `UNION ALL` because it sorts and removes duplicates.

Would you like more details on UNION, or would you like to explore other advanced SQL operations? 🚀

Souy Soeng

PostgreSQL UNION