PostgreSQL – INNER JOIN

Last Updated : 19 Sep, 2023

In PostgreSQL the INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.

Syntax:
SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

Let’s analyze the above syntax:

Firstly, using the SELECT statement we specify the tables from where we want the data to be selected.
Second, we specify the main table.
Third, we specify the table that the main table joins to.

The below Venn Diagram illustrates the working of PostgreSQL INNER JOIN clause:

For the sake of this article we will be using the sample DVD rental database, which is explained here .

Now, let’s look into a few examples.

Example 1:
Here we will be joining the “customer” table to “payment” table using the INNER JOIN clause.

SELECT
    customer.customer_id,
    first_name,
    last_name,
    email,
    amount,
    payment_date
FROM
    customer
INNER JOIN payment ON payment.customer_id = customer.customer_id;

Output:

Example 2:

Here we will be joining the “customer” table to “payment” table using the INNER JOIN clause and sort them with the ORDER BY clause:

SELECT
    customer.customer_id,
    first_name,
    last_name,
    email,
    amount,
    payment_date
FROM
    customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
ORDER BY
    customer.customer_id;

Output:

Example 3:
Here we will be joining the “customer” table to “payment” table using the INNER JOIN clause and filter them with the WHERE clause:

SELECT
    customer.customer_id,
    first_name,
    last_name,
    email,
    amount,
    payment_date
FROM
    customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
WHERE
    customer.customer_id = 15;

Output:

Example 4:
Here we will establish the relationship between three tables: staff, payment, and customer using the INNER JOIN clause.

SELECT
    customer.customer_id,
    customer.first_name customer_first_name,
    customer.last_name customer_last_name,
    customer.email,
    staff.first_name staff_first_name,
    staff.last_name staff_last_name,
    amount,
    payment_date
FROM
    customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
INNER JOIN staff ON payment.staff_id = staff.staff_id;

Output: