PostgreSQL Unions
What Is UNION in PostgreSQL?
UNION is used to blend the results of two or more separate queries into one final output. It lines up the data vertically, placing one result below the other.
How It Works
Each query must return the same number of columns, and those columns should have compatible data types.
After running both queries, UNION stacks their results and automatically removes duplicates.
Simple Example
Let’s say we have two lists: managers and executives, and both have a column named email.
We want to gather all emails—without repetition.
SELECT email FROM managers UNION SELECT email FROM executives;
This pulls all unique email addresses from both groups.
UNION ALL (Keep Everything)
If you want to include repeated values, use UNION ALL instead. It returns every row from both queries—even duplicates.
SELECT email FROM managers UNION ALL SELECT email FROM executives;
This version does not filter out duplicate entries.
Important Notes
- All combined queries must have matching column count.
- Data types in corresponding columns must be compatible (e.g., INT with INT, or TEXT with VARCHAR).
- By default, UNION sorts out duplicates; use UNION ALL to keep all rows.
- You can apply ORDER BY after the final query to sort the combined output.
Example with Column Labels and Sorting
SELECT name FROM local_clients UNION SELECT name FROM global_clients ORDER BY name;
This merges both name lists into one and arranges them alphabetically.
Quick Comparison
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicates | Removed | Included |
| Speed | Slightly Slower | Faster |
| Use Case | Clean, unique list | Full raw data set |
Prefer Learning by Watching?
Watch these YouTube tutorials to understand POSTGRESQL Tutorial visually:
What You'll Learn:
- 📌 Intermediate SQL Tutorial | Unions | Union Operator
- 📌 MySQL UNIONS are easy