August 3, 2023
Summary: in this tutorial, you will learn how to select data from multiple tables using the PostgreSQL INNER JOIN clause.
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
- Second, specify the main table i.e., table
- Third, specify the second table (table
B) in the
INNER JOINclause and provide a join condition after 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
- 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
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:
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
paymenttables in the sample database.
In these tables, whenever a customer makes a payment, a new row is inserted into the
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
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:
- 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.