How to Perform an Inner Join With Three Tables in SQL Baeldung on SQL

1. Overview

In SQL, understanding how to join tables is fundamental for querying data efficiently. One common type of JOIN is the INNER JOIN, which combines rows from two or more tables based on a related column between these tables. While INNER JOINs with two tables are frequently encountered, performing an INNER JOIN with three tables adds complexity but can be equally essential in database management.

In this tutorial, we’ll learn how to perform an INNER JOIN with three tables in SQL. We’ll explore the syntax of such an operation, its usage scenarios, and practical examples.

Notably, we’re going to perform the INNER JOIN using the PostgreSQL database. However, the same process applies to other databases as well.

2. Syntax of INNER JOIN With Three Tables

First, let’s see an example syntax for performing an INNER JOIN with three tables:

SELECT columns_list
FROM table1
INNER JOIN table2 ON table1.column = table2.column
INNER JOIN table3 ON table2.column = table3.column;

Now, we can begin analyzing this SQL query:

table1, table2, and table3 are the table names involved in the INNER JOIN
column is the common column to both table1 and table2, as well as table2 and table3
columns_list are the columns to retrieve data from the joined tables

Overall, this query aims to combine rows from three tables (table1, table2, and table3) by finding matching values in the specified columns. It retrieves only the rows with a match in all three tables, effectively filtering the data to include only related records across these tables.

3. INNER JOIN With Three Table Usage Example

Let’s use the tables from the Baeldung University schema to illustrate the INNER JOIN with three tables.

3.1. Create the Table

Before moving forward, let’s create the three tables, i.e., Department, Faculty, and Teaching:

CREATE TABLE Department(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (50),
    code VARCHAR (4),
    UNIQUE (id)
);

CREATE TABLE Faculty(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (60),
    national_id BIGINT NOT Null,
    position VARCHAR (30),
    start_date DATE,
    end_date DATE,
    department_id INT,
    CONSTRAINT faculty_department_id_fkey FOREIGN KEY(department_id) REFERENCES Department(id),
    UNIQUE (id)
);

CREATE TABLE Teaching( 
    id INT PRIMARY KEY NOT Null,
    role VARCHAR(60),
    semester VARCHAR(30),
    year INT,
    course_id VARCHAR (10),
    faculty_id INT,
    CONSTRAINT teaching_faculty_id_fkey FOREIGN KEY(faculty_id) REFERENCES Faculty(id),
    UNIQUE (id)
);

These queries create the tables successfully.

3.2. Insert Sample Data

Next, let’s insert the sample data into these newly created tables:

INSERT INTO Department (id, name, code) VALUES
  (1, 'Computer Science', 'CS'),
  (2, 'Electronics and Communications', 'EC'),
  (3, 'Mechanical Engineering', 'ME'),
  (4, 'Civil Engineering', 'CE'),
  (5, 'Mathematics', 'MA');

INSERT INTO Faculty (id, name, national_id, position, start_date, end_date, department_id) VALUES
  (1, 'Anubha Gupta', 1018901231, 'Professor', '2010-1-11', '2027-3-11', 2),
  (21, 'Peter Pan', 2130989011, 'Professor', '2007-5-11', NULL, 2),
  (51, 'Vikram Goyal', 6541236090, 'Professor', '2016-1-11', NULL, 2),
  (111, 'AV Subramanium', 1340902317, 'Assistant Professor', '2011-5-11', NULL, 1),
  (121, 'Risa Sodi', 1409239017, 'Associate Professor', '2010-1-11', NULL, 1),
  (230, 'Kiu Chi', 3163218917, 'Assistant Professor', '2020-12-10', NULL, 1),
  (401, 'Vini Joseph', 9028291011, 'Professor', '2015-2-12', NULL, 3),
  (431, 'Thomas Cook', 7734139012, 'Associate Professor', '2016-10-11', NULL, 3),
  (510, 'Ranold Krose', 2314553301, 'Teaching Assistant', '2021-9-11', NULL, 3),
  (601, 'Sussie Smith', 1657230918, 'Professor', '2019-1-11', '2027-2-18', 5),
  (691, 'Brandon Ford', 2131435609, 'Assistant Professor', '2013-1-11', NULL, 4);

INSERT INTO Teaching (id, role, semester, year, course_id, faculty_id) VALUES
  (2001, 'Instructor', 'SPRING', 2022, 'EC111', 1),
  (2301, 'Instructor', 'FALL', 2023, 'EC111', 1),
  (2021, 'Instructor', 'SPRING', 2022, 'EC421', 51),
  (2313, 'Instructor', 'SPRING', 2023, 'EC221', 21),
  (4003, 'Instructor', 'SPRING', 2022, 'CE121', 691),
  (5103, 'Instructor', 'FALL', 2022, 'MA121', 601),
  (5027, 'Instructor', 'SPRING', 2022, 'MA441', 601),
  (101, 'Instructor', 'FALL', 2022, 'CS111', 111),
  (103, 'Instructor', 'FALL', 2022, 'CS112', 121),
  (113, 'Instructor', 'FALL', 2022, 'CS131', 230),
  (3302, 'TA', 'FALL', 2023, 'ME111', 510);

These SQL statements add the data to the created tables.

We can verify the tables and data with SELECT:

SELECT * FROM Department;

This results in 5 rows:

 id |              name              | code 
----+--------------------------------+------
  1 | Computer Science               | CS
  2 | Electronics and Communications | EC
  3 | Mechanical Engineering         | ME
  4 | Civil Engineering              | CE
  5 | Mathematics                    | MA
(5 rows)

Similarly, we can check the Faculty table:

SELECT * FROM Faculty;

This query shows all the rows from the Faculty table:

 id  |      name      | national_id |      position       | start_date |  end_date  | department_id 
-----+----------------+-------------+---------------------+------------+------------+---------------
   1 | Anubha Gupta   |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2
  21 | Peter Pan      |  2130989011 | Professor           | 2007-05-11 |            |             2
  51 | Vikram Goyal   |  6541236090 | Professor           | 2016-01-11 |            |             2
 111 | AV Subramanium |  1340902317 | Assistant Professor | 2011-05-11 |            |             1
 121 | Risa Sodi      |  1409239017 | Associate Professor | 2010-01-11 |            |             1
 230 | Kiu Chi        |  3163218917 | Assistant Professor | 2020-12-10 |            |             1
 401 | Vini Joseph    |  9028291011 | Professor           | 2015-02-12 |            |             3
 431 | Thomas Cook    |  7734139012 | Associate Professor | 2016-10-11 |            |             3
 510 | Ranold Krose   |  2314553301 | Teaching Assistant  | 2021-09-11 |            |             3
 601 | Sussie Smith   |  1657230918 | Professor           | 2019-01-11 | 2027-02-18 |             5
 691 | Brandon Ford   |  2131435609 | Assistant Professor | 2013-01-11 |            |             4
(11 rows)

Finally, let’s verify the Teaching table:

SELECT * FROM Teaching;

The result set includes 11 rows:

  id  |    role    | semester | year | course_id | faculty_id 
------+------------+----------+------+-----------+------------
 2001 | Instructor | SPRING   | 2022 | EC111     |          1
 2301 | Instructor | FALL     | 2023 | EC111     |          1
 2021 | Instructor | SPRING   | 2022 | EC421     |         51
 2313 | Instructor | SPRING   | 2023 | EC221     |         21
 4003 | Instructor | SPRING   | 2022 | CE121     |        691
 5103 | Instructor | FALL     | 2022 | MA121     |        601
 5027 | Instructor | SPRING   | 2022 | MA441     |        601
  101 | Instructor | FALL     | 2022 | CS111     |        111
  103 | Instructor | FALL     | 2022 | CS112     |        121
  113 | Instructor | FALL     | 2022 | CS131     |        230
 3302 | TA         | FALL     | 2023 | ME111     |        510
(11 rows)

The results ensure that our sample datasets are ready for performing the INNER JOIN operation.

3.3. Performing the INNER JOIN

Now that we have the tables and data ready, we can perform an INNER JOIN to link the related records from each table:

SELECT Department.code, Faculty.name, Faculty.position, Teaching.role, Teaching.semester
  FROM Department INNER JOIN Faculty ON Department.id = Faculty.department_id
  INNER JOIN Teaching ON Faculty.id = Teaching.faculty_id;

This query selects the code column from the Department table, the name and position columns from the Faculty table, and the role and semester columns from the Teaching table. Department, Faculty, and Teaching are the tables involved in the INNER JOIN. The ON clause specifies the columns used to JOIN the tables:

Department.id with Faculty.department_id
Faculty.id with Teaching.faculty_id

The result of the query is a combination of data from the three tables:

 code |      name      |      position       |    role    | semester 
------+----------------+---------------------+------------+----------
 EC   | Anubha Gupta   | Professor           | Instructor | SPRING
 EC   | Anubha Gupta   | Professor           | Instructor | FALL
 EC   | Vikram Goyal   | Professor           | Instructor | SPRING
 EC   | Peter Pan      | Professor           | Instructor | SPRING
 CE   | Brandon Ford   | Assistant Professor | Instructor | SPRING
 MA   | Sussie Smith   | Professor           | Instructor | FALL
 MA   | Sussie Smith   | Professor           | Instructor | SPRING
 CS   | AV Subramanium | Assistant Professor | Instructor | FALL
 CS   | Risa Sodi      | Associate Professor | Instructor | FALL
 CS   | Kiu Chi        | Assistant Professor | Instructor | FALL
 ME   | Ranold Krose   | Teaching Assistant  | TA         | FALL
(11 rows)

This result set demonstrates the effectiveness of using an INNER JOIN with three tables to retrieve related data across multiple tables.

4. Using Nested Subqueries for INNER JOIN

In addition to the standard method of performing an INNER JOIN with three tables, we can also use nested subqueries. In particular, this technique involves joining two tables first and then using the result set to JOIN with the third table. Additionally, this can sometimes be useful for clarity or when dealing with complex joins.

First, we JOIN the Department and Faculty tables, and then we use the result set to JOIN with the Teaching table:

SELECT dept.code, dept.name, dept.position, Teaching.role, Teaching.semester
  FROM (SELECT Department.code, Faculty.id, Faculty.name, Faculty.position
  FROM Department INNER JOIN Faculty ON Department.id = Faculty.department_id) AS dept
  INNER JOIN Teaching ON dept.id = Teaching.faculty_id;

This SQL query uses a subquery that combines the Department and Faculty tables to create a temporary result set named dept. Then, the main query performs an INNER JOIN between the temporary result set (dept) and the Teaching table.

The result of this nested subquery is the same as the standard INNER JOIN, specifically displaying the department code from the Department table, faculty name and position from the Faculty table, and role and semester from the Teaching table.

5. Conclusion

In this article, we learned how to perform an INNER JOIN with three tables in SQL. In particular, we used two different methods (standard INNER JOIN and nested subqueries) to perform the INNER JOIN.

Whether we use standard INNER JOIN or nested subqueries, understanding these methods is important for effective database management.

Learn Java Collections

Learn Spring

Learn Maven

View All Courses

Full Archive

About Baeldung