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 theINNER JOIN clause.

Here is the generic syntax for theINNER JOIN clause that joins two tables:

SELECT  select_listFROM  table1INNER JOIN table2  ON table1.column_name= table2.column_name;

In this syntax:

• First, specify the columns from both tables in the select list of theSELECT 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 usetable aliases:

SELECT  select_listFROM  table1 t1INNER JOIN table2 t2    ON t1.column_name= t2.column_name;

In this syntax, we first assignt1 andt2 as the table aliases fortable1 andtable2. 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 theUSING syntax:

SELECT  select_listFROM  table1 t1INNER JOIN table2 t2USING(column_name);

How the INNER JOIN works

For each row in thetable1, the inner join compares the value in thecolumn_name with the value in the corresponding column of every row in thetable2.

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 howINNER JOIN clause works.

PostgreSQL INNER JOIN examples

Let’s take some examples of using theINNER JOIN clause.

1) Using PostgreSQL INNER JOIN to join two tables

Let’s take a look at thecustomerandpayment tables in thesample database.

In this schema, whenever a customer makes a payment, a new row is inserted into thepayment table. While each customer may have zero or many payments, each payment belongs to one and only one customer. Thecustomer_id column serves as the link establishing the relationship between the two tables.

The following statement uses theINNER JOIN clause to select data from both tables:

SELECT  customer.customer_id,  customer.first_name,  customer.last_name,  payment.amount,  payment.payment_dateFROM  customer  INNER JOIN paymentON payment.customer_id= customer.customer_idORDER 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_dateFROM  customer c  INNER JOIN payment p ON p.customer_id = c.customer_idORDER BY  p.payment_date;

Since both tables have the samecustomer_id column, you can use theUSING syntax:

SELECT  customer_id,  first_name,  last_name,  amount,  payment_dateFROM  customer  INNER JOIN paymentUSING(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, andcustomer:

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 usesINNER 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_dateFROM  customer c  INNER JOIN payment pUSING (customer_id)  INNER JOIN staff susing(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

• UseINNER JOIN clauses to select data from two or more related tables and return rows that have matching values in all tables.

Last updated onJanuary 17, 2024

On this page

• Introduction to PostgreSQL INNER JOIN clause
• PostgreSQL INNER JOIN examples
• Summary