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
\cto 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
\dtcommand 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
studenttable should now match the row from thegradestable 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