PostgreSQL: How to Use LEFT JOIN on Multiple Tables

Often in PostgreSQL you may want to use the LEFT JOIN statement multiple times to perform a left join on multiple tables.

You can use the following basic syntax to do so:

SELECT t.id, t.team, p.points, r.role
FROM team_info t
LEFT JOIN points_info p
ON t.id = p.id
LEFT JOIN role_info r
ON t.id = r.id;

This particular example will perform a left join based on the following joins:

• First, left join based on id values in the team_info table matching id values in the points_info table.
• Then, left join based on id values in the team_info table matching id values in the role_info table.

The result will be one table.

The following example shows how to use this syntax in practice.

Example: How to Use LEFT JOIN on Multiple Tables in PostgreSQL

First, we will create the following table named team_info that contains information about the teams that various basketball players belong to:

-- create table named 'team_info'
CREATE TABLE team_info (
  id INTEGER,
  team TEXT
);

-- add rows to the table 
INSERT INTO team_info VALUES (1, 'Mavs');
INSERT INTO team_info VALUES (2, 'Celtics');
INSERT INTO team_info VALUES (3, 'Kings');

-- view all rows in table
SELECT * FROM team_info;

 id |  team   
----+---------
  1 | Mavs
  2 | Celtics
  3 | Kings

Next, we will create the following table named points_info that contains information about the points scored by various basketball players:

-- create table named 'points_info'
CREATE TABLE points_info (
  id INTEGER,
  points INTEGER
);

-- add rows to the table 
INSERT INTO points_info VALUES (1, 24);
INSERT INTO points_info VALUES (2, 35);
INSERT INTO points_info VALUES (3, 17);

-- view all rows in table
SELECT * FROM points_info;

 id | points 
----+--------
  1 |     24
  2 |     35
  3 |     17

Lastly, we will create the following table named role_info that contains information about the role of various basketball players:

-- create table named 'role_info'
CREATE TABLE role_info (
  id INTEGER,
  role TEXT
);

-- add rows to the table 
INSERT INTO role_info VALUES (1, 'Guard');
INSERT INTO role_info VALUES (2, 'Forward');
INSERT INTO role_info VALUES (3, 'Center');

-- view all rows in table
SELECT * FROM role_info;

 id |  role   
----+---------
  1 | Guard
  2 | Forward
  3 | Center

We can use the following syntax to use the LEFT JOIN statement to join each of these tables together by matching on values from the id column in each table:

SELECT t.id, t.team, p.points, r.role
FROM team_info t
LEFT JOIN points_info p
ON t.id = p.id
LEFT JOIN role_info r
ON t.id = r.id;

 id |  team   | points |  role   
----+---------+--------+---------
  1 | Mavs    |     24 | Guard
  2 | Celtics |     35 | Forward
  3 | Kings   |     17 | Center

We can see that the resulting table contains the values from the id, team, points and role columns from the three tables.

You can use similar syntax to use the LEFT JOIN statement to join together multiple tables in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to SELECT DISTINCT on Multiple Columns
PostgreSQL: Use COUNT DISTINCT with CASE WHEN
PostgreSQL: How to Count Distinct Over Multiple Columns