PostgreSQL UNION Operator – Combining Results from Multiple Queries

Welcome to The Coding College, your trusted source for coding and programming tutorials! In this article, we’ll dive into the UNION operator in PostgreSQL, a tool used to combine the results of two or more SELECT queries into a single dataset.

What is the UNION Operator?

The UNION operator in PostgreSQL combines the result sets of multiple SELECT statements into one. It eliminates duplicate rows by default, ensuring each row in the result is unique.

Syntax

SELECT column_name(s)
FROM table1
UNION
SELECT column_name(s)
FROM table2;

Key Points:

  1. The number and order of columns in all SELECT statements must match.
  2. The data types of the columns must be compatible.

Example: Sample Tables

Table 1: students_2023

student_idnameyear
1Alice2023
2Bob2023

Table 2: students_2024

student_idnameyear
3Charlie2024
4Alice2024

Example 1: Basic UNION

SELECT name, year
FROM students_2023
UNION
SELECT name, year
FROM students_2024;

Result:

nameyear
Alice2023
Bob2023
Charlie2024
Alice2024
  • Duplicate rows are removed by default.

Example 2: UNION ALL

If you want to include duplicate rows, use UNION ALL:

SELECT name, year
FROM students_2023
UNION ALL
SELECT name, year
FROM students_2024;

Result:

nameyear
Alice2023
Bob2023
Charlie2024
Alice2024
  • All rows from both tables, including duplicates, are returned.

Example 3: UNION with a Filter

SELECT name, year
FROM students_2023
WHERE year = 2023
UNION
SELECT name, year
FROM students_2024
WHERE year = 2024;

Result:

nameyear
Alice2023
Bob2023
Charlie2024
Alice2024
  • Adds specific filters to each query before combining results.

Benefits of UNION

  1. Data Combination: Merges data from multiple tables or queries into a single dataset.
  2. Duplicate Removal: Automatically removes duplicate rows, ensuring cleaner results.
  3. Query Simplification: Reduces the complexity of combining results manually.

Real-World Applications

  1. Yearly Reports: Combine data from different years or quarters.
  2. Customer Databases: Merge customer records from various branches.
  3. Data Migration: Integrate tables during system migrations.

Common Use Cases

  1. Aggregating Historical Data: Combine past and present records for analysis.
  2. Multi-Source Reports: Fetch data from multiple tables or databases into a unified format.
  3. Data Comparison: Compare data trends across datasets.

Learn More at The Coding College

Explore additional PostgreSQL tutorials and programming resources at The Coding College. Our content adheres to Google’s E-E-A-T (Experience, Expertise, Authority, and Trustworthiness) guidelines to ensure high-quality and user-beneficial information.

Conclusion

The PostgreSQL UNION operator is a versatile tool for combining results from multiple queries. Understanding how to use it effectively can enhance your data management and reporting skills.

Stay tuned to The Coding College for more tutorials and tips on PostgreSQL and other programming topics!

Leave a Comment