How to Use Postgres to Join Multiple Tables

Introduction

This tutorial will explain how to use Postgres to join multiple tables using the INNER JOIN clause. Of all of the five main types of JOIN clauses, which includes the INNER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN and CROSS JOIN, the INNER JOIN clause is one of the most useful and commonly used functions in an SQL server. This command allows for the easy querying of data from two or more related tables by specifying the columns in each table. This function works similarly to the standard JOIN clause and selects all of the rows from the specified tables, provided there is a match between the columns in each of the specified tables.

Prerequisites

  • A basic knowledge of some SQL commands or database management applications, particularly on the use of the JOIN functions in PostgreSQL, is required to use Postgres to join multiple tables.

Open and access the interactive shell for PostgreSQL

First, open the psql terminal by executing the following command:

1
sudo su - postgres

Type in the password, hit the -kbd-ENTER-/kbd- key then execute the following command:

1
psql

Use PostgreSQL to join multiple tables

PostgreSQL can be used to join multiple tables inside a database with the INNER JOIN clause. This command allows for relating the data in one table to another table by specifying the columns in each table that contain the data that is to be joined. Also, the first table from the main source of data that will relate to the second table with a specified condition must be stipulated.

Use the following syntax to execute the INNER JOIN clause:

1
2
3
4
5
6
7
8
9
10
SELECT
    first_table.primarykey,
    first_table.column_name,
    second_table.primarykey,
    second_table.column_name
FROM first_table
INNER JOIN
    second_table
ON
    first_table.primarykey = second_table.foreignkey;

Create a database and table in PostgreSQL

Before relating the two tables, first create a database in PostgreSQL by executing the following command:

1
CREATE DATABASE somedb;
  • Now execute the command \c to connect and enter the database.

Next, execute the following command to create a table in PostgreSQL:

1
2
CREATE TABLE TABLE_NAME(
    COLUMN_NAME DATATYPE CONSTRAINTS[OPTIONAL]);

Following is an example of the fist table being used:

1
2
3
4
5
6
CREATE TABLE student(
        stud_id INT PRIMARY KEY NOT NULL,
        firstname VARCHAR(255) NOT NULL,
        lastname VARCHAR(255) NOT NULL,
        email VARCHAR(50)
);

An example of the second table follows:

1
2
3
4
5
6
7
CREATE TABLE grades(
        grd_id INT NOT NULL PRIMARY KEY,
        s_id INT,
        average FLOAT NOT NULL,
        remarks TEXT,
        FOREIGN KEY (s_id) REFERENCES student (stud_id)
);
  • Executing the \dt command will return all the tables from the current database.

The results should resemble the following:

1
2
3
4
5
6
somedb=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner  
--------+---------+-------+----------
 public | grades  | table | postgres
 public | student | table | postgres

Next, execute the following INSERT statement to add data to the table:

1
INSERT INTO TABLE_NAME(COLUMN_NAME) VALUES(VALUE_DATA);

Use Postgres inner join clause to join multiple tables

Now execute the following SELECT statement to join the two tables in the condition:

1
2
3
4
5
6
7
8
9
10
11
SELECT
        student.stud_id,
        student.firstname,
        student.email,
        grades.grd_id,
        grades.average,
        grades.remarks
FROM
        student
INNER JOIN
        grades ON student.stud_id = grades.s_id;
  • The row from the student table should now match the row from the grades table and will combine both of the rows and be returned as a single one.

The results should resemble the following table:

1
2
3
4
5
6
7
8
 stud_id | firstname |              email              | grd_id | average | remarks
---------+-----------+---------------------------------+--------+---------+---------
     792 | Stevie    | stevie.hallo@hotmail.com        |      1 |       1 | Pass
     434 | Rebecca   | rebbecca.didio@didio.com.au     |      2 |       2 | Pass
     123 | Shawna    | shawna.albrough@albrough.com.au |      3 |     2.5 | Pass
     456 | Rose      | rose@jebb.net.au                |      4 |       3 | Fail
     138 | Mariko    | mariko_stayer@hotmail.com       |      5 |       5 | Fail
(5 rows)

Conclusion

This tutorial explained how to use Postgres to join multiple tables. The tutorial specifically covered how to open and access the interactive shell for PostgreSQL and how to create a sample database and tables in PostgreSQL. The article then explained how to use Postgre to join multiple tables, provided two specific example tables and then explained how to use the INSERT statement to add data to the tables. The tutorial then explained how to use the Postgres INNER JOIN clause to join multiple tables. Remember that the first table, that will relate to the other table(s) with a specific condition, must be specified from the main source of data.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.