JOIN three tables in PostgreSQL

Introduction

In this article we wil show you how to join three tables in PostgreSQL. In previous articles we have seen the introduction of the JOINs in PostgreSQL. The purpose of JOIN was to merge two tables column wise rather the merging the rows like the union operator. In many scenarios we need to merge the content of multiple tables in order to get the necessary insight. The JOIN helps a database user to see the complex information in an easier table form which makes it easy to analyze.

Demo of how to Join three tables in PostgreSQL

Let us consider a scenario of a market store where employees work on different counters. Each counter has one employee. There is multiple customers in the market, so in this scenario we have three different tables Employee, Payment, Customer. Let’s see the schema and queries required for this scenario, but first a reminder, in order for joins to work we had one simple rule for JOINs that there must be a foreign key relationship. This time we are going to JOIN three tables so we need to have a foreign key relationship between three tables. Let’s see how it’s done.

First, we will make Employee table with dummy records

Insert records:

The above queries will make a table of employees with the given dummy record

The second table we will create is the Customer table:

Insert dummy record:

This will create the customer table as below

Now we will create the last table which is the Payment table. This table is different from other two as this will be the main table that will have all the foreign key relations from other two tables. Look carefully at how this table is created:

This will create the schema with all necessary relations required for the joins to work.

Insert the dummy record:

This will create a table like this:

https://gyazo.com/9a2d325d2c6affac08567c4a53332877

Now we are setup to illustrate a three table join example, so let’s consider the situation where you need to how much each customer has paid to which employee. We need a query that can get this information quickly and effectively. Let us break the query in two parts. First we need to know which customer paid which employee. This can be achieved through a single join query on customer and payment table as:

This query will give us results about all customers and their respective payments. Now the second part of the main query is to know which employee received which payment. This can also be achieved through a simple join query on payment and employee tables as:

Now if you see both above queries you will notice that they both are same just referenced on to different tables. Now if we want to know which customer payed to which employee it quite simple, we just have to merge the above two queries with another join statement. This query will reference three tables and two join statements. Let’s see how to achieve this.

This query will give you a result set of how much customers paid to each employee. This query will first reference the customer and payment table with one join and then it will combine the resulting table with employee table as a second join. The result will look like this:

Conclusion

In this article I have shown how anyone can JOIN three tables in PostgreSQL with the help of a scenario where this type of queries has a great importance because they are effective, easy to understand and small rather than having large subqueries.