PostgreSQL: How to Combine Data from 2 Tables (4 examples)

Introduction

In the realm of database management, PostgreSQL stands out for its robust features and flexibility. One common task that database administrators and developers often encounter is combining data from two tables. This might be necessary for various reasons, such as generating reports, conducting analyses, or integrating systems. In PostgreSQL, there are several ways to achieve this, and in this tutorial, we’ll explore four practical examples to combine data from two tables: using JOIN, UNION, INTERSECT, and CTE (Common Table Expressions).

Understanding the Data

Before diving into the examples, let’s establish a common dataset that we’ll use throughout this tutorial. Assume we have two tables: Orders and Customers.

Here’s the Orders table:

Orders	OrderID	OrderDate	CustomerID	TotalAmount
	1	2023-01-01	101	150
	2	2023-01-02	102	250

And this is Customers:

Customers	CustomerID	Name	Email
	101	Jane Doe	[email protected]
	102	John Smith	[email protected]

Example 1: Using JOIN

The JOIN operator is one of the most common and versatile methods to combine data from two tables. In this first example, let’s demonstrate how to use a simple INNER JOIN to retrieve a list of orders along with the customer information for each order.

SELECT O.OrderID, O.OrderDate, C.Name, C.Email
 FROM Orders O
 INNER JOIN Customers C
 ON O.CustomerID = C.CustomerID;

This query will return only those orders where a matching customer ID is found in both tables. It’s an efficient way to combine relevant information without having extra or unrelated data.

Example 2: Using UNION

If the goal is to combine results from two select queries into a single result set, the UNION operator can be used. It’s important to note that UNION removes duplicate records. Here, we’ll simulate combining two separate queries reflecting orders from two different regions, hypothetical tables Orders_North and Orders_South.

SELECT OrderID, CustomerID, TotalAmount
 FROM Orders_North
 UNION
 SELECT OrderID, CustomerID, TotalAmount
 FROM Orders_South;

This query combines the data from both tables into a single set, ensuring no duplicate records. It’s a great way to compile comprehensive lists from fragmented datasets.

Example 3: Using INTERSECT

The INTERSECT operator is used when you want to find common records between two queries. Suppose you want to find customers who have made orders from both the Orders_North and Orders_South tables.

SELECT CustomerID
 FROM Orders_North
 INTERSECT
 SELECT CustomerID
 FROM Orders_South;

This query will return the IDs of customers who exist in both tables, effectively finding the intersection of the two datasets.

Example 4: Using CTE (Common Table Expressions)

Common Table Expressions (CTEs) offer a powerful and flexible way to write more complex queries. They allow you to create temporary result sets that can be easily referenced within another SQL statement. In this case, we’ll use a CTE to combine data from the Orders and Customers tables, where we aim to create a summary report showing total orders per customer.

WITH CustomerOrders AS (
   SELECT C.CustomerID, C.Name, COUNT(O.OrderID) AS TotalOrders
   FROM Customers C
   LEFT JOIN Orders O
   ON C.CustomerID = O.CustomerID
   GROUP BY C.CustomerID
 )
 SELECT * FROM CustomerOrders;

In this example, the CTE CustomerOrders creates a temporary view of customers along with a count of their total orders. This enables a direct, readable syntax for generating aggregated reports or summaries combining data across tables.