PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
Home / PostgreSQL Tutorial / PostgreSQL INNER JOIN

PostgreSQL INNER JOIN

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

Introduction to PostgreSQL INNER JOIN clause

So far, you have learned how to select data from a table, choosing which columns and rows you want, and how to sort the result set in a particular order. It is time to move to one of the most important concepts in the database called joining that allows you to relate data in one table to the data in other tables. There are several kinds of joins including INNER JOIN, OUTER JOIN and self-join. This tutorial focuses on the INNER JOIN.

Suppose you want to get data from two tables named A and B. The B table has the fkafield that relates to the primary key of the Atable.

A and B tables

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

1
2
3
4
5
6
7
8
SELECT
A.pka,
A.c1,
B.pkb,
B.c2
FROM
A
INNER JOIN B ON A .pka = B.fka;

To join A table to B table:

  • First, you specify the column in both tables from which you want to select data in the SELECT clause
  • Second, you specify the main table i.e., A in the FROM clause.
  • Third, you specify the table that the main table joins to i.e., B in the INNER JOIN clause. In addition, you put a join condition after the ON keyword i.e, A.pka = B.fka.

For each row in the A table, PostgreSQL scans the B table to check if there is any row that matches the condition i.e., A.pka = B.fka. If it finds a match, it combines columns of both rows into one row and add the combined row to the returned result set.

The primary key column ( pka) and foreign key column ( fka) are typically indexed; therefore, PostgreSQL only has to check for the match in the indexes, which is very fast.

Sometimes A and B tables have the same column name so we have to refer to the column as table_name.column_name to avoid ambiguity. In case the name of the table is long, you can use a table alias e.g., tbland refer to the column as tbl.column_name.

The following Venn Diagram illustrates how PostgreSQL INNER JOIN clause works.

PostgreSQL INNER JOIN Venn Diagram

The INNER JOIN clause returns rows in A table that have the corresponding rows in the B table.

PostgreSQL INNER JOIN examples

PostgreSQL INNER JOIN to join 2 tables example

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

customer and payment tables

Each customer may have zero or many payments. Each payment belongs to one and only one customer. The customer_id field establishes the link between two tables.

You can use the INNER JOIN clause to join the customer table to payment table as follows:

1
2
3
4
5
6
7
8
9
10
SELECT
customer.customer_id,
first_name,
last_name,
email,
amount,
payment_date
FROM
customer
INNER JOIN payment ON payment.customer_id = customer.customer_id;

PostgreSQL INNER JOIN two tables

You can add the ORDER BY clause to sort the result set by customer id as follows:

1
2
3
4
5
6
7
8
9
10
11
12
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;

PostgreSQL INNER JOIN with ORDER BY

You can also use a WHERE clause to filter customer. The following query returns customer’s rental data for the customer id 2:

1
2
3
4
5
6
7
8
9
10
11
12
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 = 2;

PostgreSQL INNER JOIN with WHERE

PostgreSQL INNER JOIN to join 3 tables example

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

  • Each staff relates to zero or many payments. Each payment is processed by one and only one staff.
  • Each customer has zero or many payments. Each payment belongs to one and only customer.

customer, payment and staff tables

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

1
2
3
4
5
6
7
8
9
10
11
12
13
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;

PostgreSQL INNER JOIN 3 tables

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

In this tutorial, we have shown you how to select data from multiple tables by joining one table to other tables using PostgreSQL INNER JOIN clause.

Related Tutorials

  • PostgreSQL Cross Join By Example
  • PostgreSQL NATURAL JOIN Explained By Examples
  • PostgreSQL FULL OUTER JOIN
Previous Tutorial: PostgreSQL BETWEEN
Next Tutorial: PostgreSQL FULL OUTER JOIN

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Change The Password of a PostgreSQL User
  • PostgreSQL AGE Function
  • PostgreSQL DATE_PART Function
  • PostgreSQL List Users
  • PostgreSQL NOW Function
  • PostgreSQL DATE_TRUNC Function
  • PostgreSQL TO_DATE Function: Convert String to Date
  • A Look at PostgreSQL User-defined Data Types
  • PostgreSQL Copy Database Made Easy
  • How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.