In this article, we will learn about different types of PostgreSQL joins with examples.
Introduction
Data is generally hosted in a variety of data management repositories, one of them being relational database management systems. There are various industry-leading database systems available in the industry. One of the most popular open-source relational databases is PostgreSQL. Relational databases are generally used to store data in database objects known as tables which are modeled in a normalized fashion for several reasons like data deduplication, better organization of data, modeled entity relationships, etc. Due to this relational and inter-linked nature of the data model, which is implemented in the form of tables, it becomes inevitable to extract data from multiple tables and then relate this data together from a result set that is requested by the consuming application. The technical term for relating data while querying data from a set of tables is known as “join”. There are multiple types of PostgreSQL Joins that can be used to extract data for different types of scenarios.
Setting up PostgreSQL
PostgreSQL is an open-source relational database and there are various ways of installing and configuring it. Many cloud vendors offer a managed version of PostgreSQL on the cloud. For simplicity, we would be using Azure Database for PostgreSQL on the Microsoft Azure cloud. One can opt to choose any setup of PostgreSQL on any hosting platform including installation and setup on a local machine. Tables and Joins are fundamental constructs of relational databases and PostgreSQL as well, so any version/edition of PostgreSQL should have them.
Assuming the PostgreSQL is installed and configured, we need to create at least two tables in the database instance to be able to explore various types of PostgreSQL joins. To create the table, we can either use an IDE like pgAdmin and graphically create new tables or use SQL queries to create the tables. Here, we are executing SQL queries using pgAdmin and we will create two simple tables as shown below.
Once the tables are created, we can use a simple Insert query as shown below to add data to these tables. We would want to keep the ids the same but shuffle the values around so that the tables can be joined by the same ids but would have different values as shown below.
Now that the data has been created, we are now ready to explore the different joins that can be used to retrieve the data from these two tables.
PostgreSQL Joins
The first type of join that we would explore is known as INNER JOIN. This is the most frequently used join typically compared to other types of PostgreSQL joins. Let’s say that we intend to find values from both the tables where the names are matching, we can use an INNER JOIN as shown below. Here we got only two out of the four records in both the tables as only two names are matching in both tables. This dataset is an intersection of data that is common in both tables.
|
1 2 3 |
SELECT * FROM E1 INNER JOIN E2 on E1.Firstname = E2.Firstname |
The exact inverse of this join is a PostgreSQL join known as the full outer join which is the union of all the data from the two tables. The matching records are returned as well as the records that do not match are returned as well. The records that did not match would have an id with the value of null from the table to which the record is joined, as it’s a record that has a no match in the other table as shown below.
|
1 2 3 |
SELECT * FROM E1 FULL OUTER JOIN E2 on E1.Firstname = E2.Firstname |
Now that we have understood the two PostgreSQL Joins – INNER JOIN and FULL OUTER JOIN, it’s time to explore the variation of the PostgreSQL OUTER JOIN – LEFT OUTER JOIN and RIGHT OUTER JOIN.
Let’s say that we intend to retrieve all the records from table A, but from table B we intend to only retrieve records that match the values in table A. Consider table A as the LEFT table and table B as the right table. The query to retrieve the records that meet the above-mentioned criteria would look as shown below. The records where the id field is null indicate the records that did not match the join criteria, but as the nature of the join in LEFT OUTER JOIN, these records were still included as they are from table A which is the LEFT table in this case.
|
1 2 3 |
SELECT * FROM E1 LEFT OUTER JOIN E2 on E1.Firstname = E2.Firstname |
If the above-mentioned scenario was to be reversed where we wanted all the records from the right table, but only the matching records from the left table, we can use the RIGHT OUTER JOIN table as shown below. If we compare the below table with the above table, this time all the records from table B were retrieved, and the ones that matched from table A were retrieved. And those that did not match have a null value for the id field from table A.
|
1 2 3 |
SELECT * FROM E1 RIGHT OUTER JOIN E2 on E1.Firstname = E2.Firstname |
Now we have seen different types of PostgreSQL joins like INNER JOIN, FULL OUTER JOIN, LEFT JOIN, and RIGHT JOIN. All these joins had one thing in common, all of them had a join criterion to match attributes from two tables. Let’s say that we have a scenario where we intend to find out records from both the tables that do not have a match in the other table, i.e., unique records from both the tables. In this case, we can still use the FULL OUTER JOIN as it would select all the records from both tables, but with a filter, condition to select only those records which did not match. This can be done by adding a where clause to the query which states that the Id field from either table should be NULL. This happens only when the matching record was not found in the other table. The query and the result would look as shown below.
|
1 2 3 |
SELECT * FROM E1 FULL OUTER JOIN E2 on E1.Firstname = E2.Firstname WHERE E1.ID IS NULL OR E2.ID IS NULL |
One special type of PostgreSQL JOIN is a CROSS JOIN. Let’s say we want to create a cartesian product of select attributes/records from two tables, we can use a cross join. A cartesian product of 4 values from one table with four values from another table would be 16 records. Here a value means a record with one field. As we add more fields, this would multiple further. Shown below is an example where we select one field from both tables and create a cartesian product of it using a PostgreSQL CROSS JOIN.
|
1 2 3 |
SELECT E1.firstname, E2.firstname FROM E1 CROSS JOIN E2 |
In this way, we can use different types of PostgreSQL joins to fetch data from multiple tables in PostgreSQL. We can also add scope and formatting to the resulting dataset by adding more filter conditions, functions, and other such constructs that are available with the SELECT query in PostgreSQL.
Conclusion
In this article, we assumed a setup of PostgreSQL and added two tables to it along with some sample data. Then we learned about different types of PostgreSQL joins and understood them with examples of how to form a query with the join and learned how to interpret the results from the query that uses specific types of joins.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023