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: 👁 Image
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: 👁 Image
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: 👁 Image
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: 👁 Image
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: 👁 Image