PostgreSQL INNER JOIN

PostgreSQL INNER JOIN

Summary: in this tutorial, you will learn how to select data from multiple tables using the PostgreSQL INNER JOIN clause.

Introduction to PostgreSQL INNER JOIN clause

In a relational database, data is typically distributed in more than one table. To select complete data, you often need to query data from multiple tables.

In this tutorial, we are focusing on how to combine data from multiple tables using the INNER JOIN clause.

Suppose that you have two tables A and B. Table A has a column pka whose value matches with values in the fka column of table B.

To select data from both tables, you use the INNER JOIN clause in the SELECT a statement as follows:

SELECT
	pka,
	c1,
	pkb,
	c2
FROM
	A
INNER JOIN B ON pka = fka;

To join table A with the table B, you follow these steps:

• First, specify columns from both tables that you want to select data in the SELECT clause.
• Second, specify the main table i.e., table A in the FROM clause.
• Third, specify the second table (table B) in the INNER JOIN clause and provide a join condition after the ON keyword.

How the INNER JOIN works.

For each row in the table A, inner join compares the value in the PKA column with the value in the fka column of every row in the table B:

• If these values are equal, the inner join creates a new row that contains all columns of both tables and adds it to the result set.
• In case these values are not equal, the inner join just ignores them and moves to the next row.

The following Venn diagram illustrates how INNER JOIN clause works.

Most of the time, the tables that you want to join will have columns with the same name e.g., id column like customer_id.

If you reference columns with the same name from different tables in a query, you will get an error. To avoid the error, you need to qualify these columns fully using the following syntax:

table_name.column_name

In practice, you will use table aliases to assign the joined tables short names to make the query more readable.

PostgreSQL INNER JOIN examples

Let’s take some examples of using the INNER JOIN clause.

1) Using PostgreSQL INNER JOIN to join two tables

Let’s take a look at the customerand paymenttables in the sample database.

In these tables, whenever a customer makes a payment, a new row is inserted into the payment table.

Each customer may have zero or many payments. However, each payment belongs to one and only one customer. The customer_id column establishes the relationship between the two tables.

The following statement uses the INNER JOIN clause to select data from both tables:

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

The following query returns the same result. However, it uses table aliases:

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

Since both tables have the same customer_id column, you can use the USING syntax:

SELECT
	customer_id,
	first_name,
	last_name,
	amount,
	payment_date
FROM
	customer
INNER JOIN payment USING(customer_id)
ORDER BY payment_date;	

2) Using PostgreSQL INNER JOIN to join three tables

The following diagram illustrates the relationship between the three tables: staff, payment, and customer.

• Each staff handles zero or many payments. And each payment is processed by one and only one staff.
• Each customer made zero or many payments. Each payment is made by one customer.

To join the three tables, you place the second INNER JOIN clause after the first INNER JOIN a clause like the following query:

SELECT
	c.customer_id,
	c.first_name customer_first_name,
	c.last_name customer_last_name,
	s.first_name staff_first_name,
	s.last_name staff_last_name,
	amount,
	payment_date
FROM
	customer c
INNER JOIN payment p 
    ON p.customer_id = c.customer_id
INNER JOIN staff s 
    ON p.staff_id = s.staff_id
ORDER BY payment_date;

To join more than three tables, you apply the same technique.

In this tutorial, you have learned how to select data from multiple tables by using the PostgreSQL INNER JOIN clause.