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: