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.
1CREATE TABLE users (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 email text NOT NULL UNIQUE,
4 name text NOT NULL,
5 password text NOT NULL,
6 address_id int UNIQUE REFERENCES addresses(id)
7);
8
9CREATE TABLE posts (
10 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
11 title text NOT NULL,
12 post_content text NOT NULL,
13 author_id int REFERENCES users(id) NOT NULL
14);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.
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.
1SELECT posts.title AS post_title, users.email AS user_email
2FROM posts
3JOIN users ON posts.author_id = users.id
4LIMIT 2We 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.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostWithDetails from './postWithDetails.model';
4
5@Injectable()
6class PostsRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getWithDetails(postId: number) {
10 const postResponse = await this.databaseService.runQuery(
11 `
12 SELECT
13 posts.id AS id, posts.title AS title, posts.post_content AS post_content, posts.author_id as author_id,
14 users.id AS user_id, users.email AS user_email, users.name AS user_name, users.password AS user_password
15 FROM posts
16 JOIN users ON posts.author_id = users.id
17 WHERE posts.id=$1
18 `,
19 [postId],
20 );
21 const postEntity = postResponse.rows[0];
22 if (!postEntity) {
23 throw new NotFoundException();
24 }
25
26 return new PostWithDetails(postEntity);
27 }
28
29 // ...
30}
31
32export 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.
1CREATE TABLE addresses (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 street text,
4 city text,
5 country text
6);Let’s create one user with the address and one without it.
1WITH created_address AS (
2 INSERT INTO addresses (
3 street,
4 city,
5 country
6 ) VALUES (
7 'Amphitheatre Parkway',
8 'Mountain View',
9 'USA'
10 ) RETURNING *
11)
12INSERT INTO users (
13 email,
14 name,
15 password,
16 address_id
17) VALUES (
18 'adam@wilson.com',
19 'Adam',
20 'strongPassword123',
21 (SELECT id FROM created_address)
22);
23
24INSERT INTO users (
25 email,
26 name,
27 password,
28 address_id
29) VALUES (
30 'amanda@williams.com',
31 'Amanda',
32 'strongPassword123',
33 null
34);Now, let’s try using the inner join to find all users and their addresses.
1SELECT users.name AS name, addresses.country AS country
2FROM users
3JOIN addresses ON users.address_id = addresses.idThe 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.
If we perform the left join on users and addresses, we get all of the users, regardless of whether they have the address.
1SELECT users.name AS name, addresses.country AS country
2FROM users
3LEFT JOIN addresses ON users.address_id = addresses.idWe use the above approach when fetching all of the details about a particular user.
1import { Injectable, NotFoundException } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from './user.model';
4
5@Injectable()
6class UsersRepository {
7 constructor(private readonly databaseService: DatabaseService) {}
8
9 async getByEmail(email: string) {
10 const databaseResponse = await this.databaseService.runQuery(
11 `
12 SELECT users.*,
13 addresses.street AS address_street, addresses.city AS address_city, addresses.country AS address_country
14 FROM users
15 LEFT JOIN addresses ON users.address_id = addresses.id
16 WHERE email=$1
17 `,
18 [email],
19 );
20 const entity = databaseResponse.rows[0];
21 if (!entity) {
22 throw new NotFoundException();
23 }
24 return new UserModel(entity);
25 }
26
27 // ...
28}
29
30export 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.
1SELECT users.name AS name, addresses.country AS country
2FROM addresses
3RIGHT JOIN users ON users.address_id = addresses.idPlease 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.
1INSERT INTO addresses (
2 street,
3 city,
4 country
5) VALUES (
6 'Niagara Parkway',
7 'Niagara Falls',
8 'Canada'
9)The outer joins we’ve done so far in this article will not return the above record.
1SELECT users.name AS name, addresses.country AS country
2FROM users
3LEFT JOIN addresses ON users.address_id = addresses.idIf 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.
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.
1SELECT users.name AS name, addresses.country AS country
2FROM addresses
3FULL JOIN users ON users.address_id = addresses.idAs 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.