PostgreSQL – INNER JOIN

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: