API with NestJS #90. Using various types of SQL joins

NestJS SQL

This entry is part 90 of 147 in the API with NestJS

It’s a very common case to need to combine multiple related tables. In SQL, we can do that using a join statement. Therefore, this article explains various types of joins along with real-life examples.

Inner joins

A few articles ago, we defined the  and tables.

It’s typical to retrieve a particular post along with the details of its author. The easiest way of doing that is to perform an inner join. An inner join will return all rows from the posts table that have a corresponding row in the table.

inner join

To find a corresponding row, we need to tell PostgreSQL to match the column from the table and the column from the table.

We could write instead of , but inner join is a default kind of join.

The diagram above shows that the inner join would disregard rows from the table without a matching user and rows from the table without a matching post. It does not bother us in the above case because every post has an author.

In our application, we use the above approach when fetching the details of a particular post.

posts.repository.ts

There is also a special kind of an inner join called the self join. It occurs when we link a table to itself. It might come in handy when we have a hierarchical structure. A good example is an employee who is a manager of another employee.

Outer joins

In one of the recent articles, we’ve added the table.

Let’s create one user with the address and one without it.

Now, let’s try using the inner join to find all users and their addresses.

The above query does not return users that don’t have addresses. To fix this issue, we need to perform an outer join. An outer join can return both matched and unmatched values.

Left join

The left outer join returns all rows from the first table matched with the rows from the second table.

left join

If we perform the left join on users and addresses, we get all of the users, regardless of whether they have the address.

We use the above approach when fetching all of the details about a particular user.

users.repository.ts

Right join

The right join works in reverse as compared to the left join. It returns all rows from the second table and the rows matched with the first table.

We could use it to reverse the query we use to fetch the users together with their addresses.

right join



Please notice that in the above query, is the name of the left table, and is the name of the right table.

Full outer join

Let’s add a new address without assigning it to a user.

The outer joins we’ve done so far in this article will not return the above record.

If we want to include our new address in the results, we can perform a full outer join. The full outer join returns rows from both tables, matching the results if possible.

full outer join

If a particular row is not matched in the other table, it’s still included. If we run a full outer join on the and table, we get all the records from both tables.

As you can see in the above result, doing a full outer join can result in many null values, so we need to consider it.

Summary

In this article, we’ve gone through all the types of joins we might need when developing a NestJS application with raw SQL queries. It included the inner joins, which is the default type. Besides that, we went through various types of outer joins. All the above knowledge can help you choose the right join for a given situation.

Series Navigation<< API with NestJS #89. Replacing Express with FastifyAPI with NestJS #91. Dockerizing a NestJS API with Docker Compose >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments