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 the FROM clause.
Third, specify the second table (table2) you want to join using the INNER 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 Join - Inner Join]

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 customerand payment tables in the sample database.

[画像:customer and payment tables] 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:

[画像:customer, payment and staff tables] 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;