PostgreSQL INNER JOIN
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 relational database, data is typically distributed across multiple tables. To retrieve comprehensive data, you often need to query it from multiple tables.
In this tutorial, we are focusing on how to retrieve data from multiple tables using the INNER JOIN
clause.
Here is the generic syntax for the INNER JOIN
clause that joins two tables:
SELECT
select_list
FROM
table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
In this syntax:
- First, specify the columns from both tables in the select list of the
SELECT
clause. - Second, specify the main table (
table1
) from which you want to select data in theFROM
clause. - Third, specify the second table (
table2
) you want to join using theINNER JOIN
keyword. - Finally, define a condition for the join. This condition indicates which column (
column_name
) in each table should have matching values for the join.
To make the query shorter, you can use table aliases:
SELECT
select_list
FROM
table1 t1
INNER JOIN table2 t2
ON t1.column_name = t2.column_name;
In this syntax, we first assign t1
and t2
as the table aliases for table1
and table2
. Then, we use the table aliases to qualify the columns of each table.
If the columns for matching share the same name, you can use the USING
syntax:
SELECT
select_list
FROM
table1 t1
INNER JOIN table2 t2 USING(column_name);
How the INNER JOIN works
For each row in the table1
, the inner join compares the value in the column_name
with the value in the corresponding column of every row in the table2
.
When these values are equal, the inner join creates a new row that includes all columns from both tables and adds this row to the result set.
Conversely, if these values are not equal, the inner join disregards the current pair and proceeds to the next row, repeating the matching process.
The following Venn diagram illustrates how INNER JOIN
clause works.
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 this schema, whenever a customer makes a payment, a new row is inserted into the
payment
table. While each customer may have zero or many payments, each payment belongs to one and only one customer. The customer_id
column serves as the link establishing the relationship between the two tables.
The following statement uses the INNER JOIN
clause to select data from both tables:
SELECT
customer.customer_id,
customer.first_name,
customer.last_name,
payment.amount,
payment.payment_date
FROM
customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
ORDER BY
payment.payment_date;
Output:
customer_id | first_name | last_name | amount | payment_date
-------------+-------------+--------------+--------+----------------------------
416 | Jeffery | Pinson | 2.99 | 2007-02-14 21:21:59.996577
516 | Elmer | Noe | 4.99 | 2007-02-14 21:23:39.996577
239 | Minnie | Romero | 4.99 | 2007-02-14 21:29:00.996577
592 | Terrance | Roush | 6.99 | 2007-02-14 21:41:12.996577
49 | Joyce | Edwards | 0.99 | 2007-02-14 21:44:52.996577
...
To make the query shorter, you can use the table aliases:
SELECT
c.customer_id,
c.first_name,
c.last_name,
p.amount,
p.payment_date
FROM
customer c
INNER JOIN payment p ON p.customer_id = c.customer_id
ORDER BY
p.payment_date;
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 below illustrates the relationship between three tables: staff
, payment
, and customer
:
Each staff member can handle zero or multiple payments, with each payment being processed by one and only one staff member.
Similarly, each customer can make zero or multiple payments, and each payment is associated with a single customer.
The following example uses INNER JOIN
clauses to retrieve data from three tables
SELECT
c.customer_id,
c.first_name || ' ' || c.last_name customer_name,
s.first_name || ' ' || s.last_name staff_name,
p.amount,
p.payment_date
FROM
customer c
INNER JOIN payment p USING (customer_id)
INNER JOIN staff s using(staff_id)
ORDER BY
payment_date;
Output:
customer_id | customer_name | staff_name | amount | payment_date
-------------+-----------------------+--------------+--------+----------------------------
416 | Jeffery Pinson | Jon Stephens | 2.99 | 2007-02-14 21:21:59.996577
516 | Elmer Noe | Jon Stephens | 4.99 | 2007-02-14 21:23:39.996577
239 | Minnie Romero | Mike Hillyer | 4.99 | 2007-02-14 21:29:00.996577
592 | Terrance Roush | Jon Stephens | 6.99 | 2007-02-14 21:41:12.996577
49 | Joyce Edwards | Mike Hillyer | 0.99 | 2007-02-14 21:44:52.996577
...
Summary
- Use
INNER JOIN
clauses to select data from two or more related tables and return rows that have matching values in all tables.