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: