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

January 9, 2023

This entry is part 90 of 180 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 users and posts tables.

CREATE TABLE users (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

email text NOT NULL UNIQUE,

name text NOT NULL,

password text NOT NULL,

address_id int UNIQUE REFERENCES addresses(id)

);

CREATE TABLE posts (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

title text NOT NULL,

post_content text NOT NULL,

author_id int REFERENCES users(id) NOT NULL

);

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 users table.

inner join

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

SELECT posts.title AS post_title, users.email AS user_email

FROM posts

JOIN users ON posts.author_id = users.id

LIMIT 2

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

The diagram above shows that the inner join would disregard rows from the posts table without a matching user and rows from the users 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

import { Injectable, NotFoundException } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import PostWithDetails from './postWithDetails.model';

@Injectable()

class PostsRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getWithDetails(postId: number) {

const postResponse = await this.databaseService.runQuery(

SELECT

posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.author_id as author_id,

users.id AS user_id, users.email AS user_email, users.name AS user_name, users.password AS user_password

FROM posts

JOIN users ON posts.author_id = users.id

WHERE posts.id=$1

[postId],

);

const postEntity = postResponse.rows[0];

if (!postEntity) {

throw new NotFoundException();

}

return new PostWithDetails(postEntity);

}

// ...

}

export default PostsRepository;

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 addresses table.

CREATE TABLE addresses (

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

street text,

city text,

country text

);

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

WITH created_address AS (

INSERT INTO addresses (

street,

city,

country

) VALUES (

'Amphitheatre Parkway',

'Mountain View',

'USA'

) RETURNING *

)

INSERT INTO users (

email,

name,

password,

address_id

) VALUES (

'adam@wilson.com',

'Adam',

'strongPassword123',

(SELECT id FROM created_address)

);

INSERT INTO users (

email,

name,

password,

address_id

) VALUES (

'amanda@williams.com',

'Amanda',

'strongPassword123',

null

);

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

SELECT users.name AS name, addresses.country AS country

FROM users

JOIN addresses ON users.address_id = addresses.id

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.

SELECT users.name AS name, addresses.country AS country

FROM users

LEFT JOIN addresses ON users.address_id = addresses.id

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

users.repository.ts

import { Injectable, NotFoundException } from '@nestjs/common';

import DatabaseService from '../database/database.service';

import UserModel from './user.model';

@Injectable()

class UsersRepository {

constructor(private readonly databaseService: DatabaseService) {}

async getByEmail(email: string) {

const databaseResponse = await this.databaseService.runQuery(

SELECT users.*,

addresses.street AS address_street, addresses.city AS address_city, addresses.country AS address_country

FROM users

LEFT JOIN addresses ON users.address_id = addresses.id

WHERE email=$1

[email],

);

const entity = databaseResponse.rows[0];

if (!entity) {

throw new NotFoundException();

}

return new UserModel(entity);

}

// ...

}

export default UsersRepository;

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

SELECT users.name AS name, addresses.country AS country

FROM addresses

RIGHT JOIN users ON users.address_id = addresses.id

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

Full outer join

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

INSERT INTO addresses (

street,

city,

country

) VALUES (

'Niagara Parkway',

'Niagara Falls',

'Canada'

)

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

SELECT users.name AS name, addresses.country AS country

FROM users

LEFT JOIN addresses ON users.address_id = addresses.id

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 users and addresses table, we get all the records from both tables.

SELECT users.name AS name, addresses.country AS country

FROM addresses

FULL JOIN users ON users.address_id = addresses.id

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 >>