August 3, 2023
Summary: in this tutorial, you will learn how to select data from multiple tables using the PostgreSQL INNER JOIN clause.
Table of Contents
Introduction to PostgreSQL INNER JOIN clause
In a relation database, data is typically distributed in more than one table. To select complete data, you often need to query data from multiple tables.
In this tutorial, we are focusing on how to combine data from multiple tables using the INNER JOIN
clause.
Suppose that you have two tables A and B. The table A has a column pka whose value matches with values in the fka column of table B.
To select data from both tables, you use the INNER JOIN
clause in the SELECT
statement as follows:
SELECT
pka,
c1,
pkb,
c2
FROM
A
INNER JOIN B ON pka = fka;
To join table A
with the table B
, you follow these steps:
- First, specify columns from both tables that you want to select data in the
SELECT
clause. - Second, specify the main table i.e., table
A
in theFROM
clause. - Third, specify the second table (table
B
) in theINNER JOIN
clause and provide a join condition after theON
keyword.
How the INNER JOIN
works.
For each row in the table A
, inner join compares the value in the pka column with the value in the fka column of every row in the table B
:
- If these values are equal, the inner join creates a new row that contains all columns of both tables and adds it to the result set.
- In case these values are not equal, the inner join just ignores them and moves to the next row.
The following Venn diagram illustrates how INNER JOIN
clause works.
Most of the time, the tables that you want to join will have columns with the same name e.g., id
column like customer_id
.
If you reference columns with the same name from different tables in a query, you will get an error. To avoid the error, you need to qualify these columns fully using the following syntax:
table_name.column_name
In practice, you will use table aliases to assign the joined tables short names to make the query more readable.
PostgreSQL INNER JOIN examples
Let’s take some examples of using the INNER JOIN
clause.
1) Using PostgreSQL INNER JOIN to join two tables
Let’s take a look at the customer
and payment
tables in the sample database.
In these tables, whenever a customer makes a payment, a new row is inserted into the payment
table.
Each customer may have zero or many payments. However, each payment belongs to one and only one customer. The customer_id
column establishes the relationship between the two tables.
The following statement uses the INNER JOIN
clause to select data from both tables:
SELECT
customer.customer_id,
first_name,
last_name,
amount,
payment_date
FROM
customer
INNER JOIN payment
ON payment.customer_id = customer.customer_id
ORDER BY payment_date;
The following query returns the same result. However, it uses table aliases:
SELECT
c.customer_id,
first_name,
last_name,
email,
amount,
payment_date
FROM
customer c
INNER JOIN payment p
ON p.customer_id = c.customer_id
WHERE
c.customer_id = 2;
Since both tables have the same customer_id
column, you can use the USING
syntax:
SELECT
customer_id,
first_name,
last_name,
amount,
payment_date
FROM
customer
INNER JOIN payment USING(customer_id)
ORDER BY payment_date;
2) Using PostgreSQL INNER JOIN to join three tables
The following diagram illustrates the relationship between three tables: staff
, payment
, and customer
.
- Each staff handles zero or many payments. And each payment is processed by one and only one staff.
- Each customer made zero or many payments. Each payment is made by one customer.
To join the three tables, you place the second INNER JOIN
clause after the first INNER JOIN
clause as the following query:
SELECT
c.customer_id,
c.first_name customer_first_name,
c.last_name customer_last_name,
s.first_name staff_first_name,
s.last_name staff_last_name,
amount,
payment_date
FROM
customer c
INNER JOIN payment p
ON p.customer_id = c.customer_id
INNER JOIN staff s
ON p.staff_id = s.staff_id
ORDER BY payment_date;
To join more than three tables, you apply the same technique.
In this tutorial, you have learned how to select data from multiple tables by using the PostgreSQL INNER JOIN
clause.