Types of SQL Joins
Tags: sql, tutorial, database
Published: 2022-12-14 20:29:55

In SQL, a join is a way to combine data from multiple tables based on a related column between them. This allows you to query data from multiple tables in a single statement. In this article, we will explore the different types of joins in SQL.

There are several different types of joins in SQL, including **inner joins**, **outer joins**, **cross joins**. and **self joins**.

We will use the following `users` and `posts` tables for our examples:

users:

| id  | name   |
| --- | ------ |
| 1   | John   |
| 2   | Harold |
| 3   | Lionel |

posts:

| id | user_id | caption |
| -- | ------- | ------- |
| 1  | 1       | foo     |
| 2  | 1       | bar     |
| 3  | 2       | baz     |
| 4  | NULL    | qux     |

## Inner Join

Inner joins are the most common type of join and are used to combine rows from two or more tables based on a related column between them. It is the default join, which is used when you don't specify a join type in your SQL statement.

```sql
SELECT * FROM users
INNER JOIN posts
ON users.id = posts.user_id;
```

This statement would return all rows from `users` and `posts` where the `id` column is the same in both tables. The resulting table would include only the columns from `users` and `posts`, and would include a row for each pair of matching rows from the two tables.

Result:

| id | name   | id | user_id | caption |
| -- | ------ | -- | ------- | ------- |
| 1  | John   | 1  | 1       | foo     |
| 1  | John   | 2  | 1       | bar     |
| 2  | Harold | 3  | 2       | baz     |

Let's visualize the result as sets so we can understand it better. The blue are is our result set:

![Inner Join](https://dbmodeller.net/blog/assets/join_inner.png)

## Outer Joins

An outer join, on the other hand, is a type of join that combines rows from two or more tables and includes unmatched rows from one or more tables. There are three types of outer joins: left, right, and full.

## Left Outer Join

A left outer join includes all the rows from the left table, and the matched rows from the right table.

```sql
SELECT * FROM users
LEFT JOIN posts
ON users.id = posts.user_id;
```

Result:

| id | name   | id | user_id | caption |
| -- | ------ | -- | ------- | ------- |
| 1  | John   | 1  | 1       | foo     |
| 1  | John   | 2  | 1       | bar     |
| 2  | Harold | 3  | 2       | baz     |
| 3  | Lionel |    |         |         |

And as a set:

![Left Outer Join](https://dbmodeller.net/blog/assets/join_left.png)

Before we go on and talk about right joins, looking at the above picture, what if we want to get the difference of the left set from the right one without the intersection?

![Left Outer Join Without the intersection](https://dbmodeller.net/blog/assets/join_left_difference.png)

In that case we should add a where condition that eliminates the intersection from the result:

```sql
SELECT * FROM users
LEFT JOIN posts
ON users.id = posts.user_id
WHERE posts.user_id IS NULL;
```

Result:

| id | name   | id | user_id | caption |
| -- | ------ | -- | ------- | ------- |
| 3  | Lionel |    |         |         |

As you see, we have the previous result minus the inner join.

## Right Outer join

A right outer join includes **all the rows** from the right table, and the **matched rows** from the left table. It is basically the symmetrical to the left outer join.

```sql
SELECT * FROM users
RIGHT JOIN posts
ON users.id = posts.user_id;
```

Result:

| id | user_id | caption | id | name   |
| -- | ------- | ------- | -- | ------ |
| 1  | 1       | foo     | 1  | John   |
| 2  | 1       | bar     | 1  | John   |
| 3  | 2       | baz     | 2  | Harold |
| 4  | NULL    | qux     |    |        |

![Right Outer Join](https://dbmodeller.net/blog/assets/join_right.png)

## Full Outer Join

And a full outer join includes **all the rows** from **both** tables, whether they are matched or not.

```sql
SELECT * FROM users
FULL OUTER JOIN posts
ON users.id = posts.user_id;
```

Result:

| id | name   | id | user_id | caption |
| -- | ------ | -- | ------- | ------- |
| 1  | John   | 1  | 1       | foo     |
| 1  | John   | 2  | 1       | bar     |
| 2  | Harold | 3  | 2       | baz     |
| 3  | Lionel |    |         |         |
|    |        | 4  | NULL    | qux     |

![Full Outer Join](https://dbmodeller.net/blog/assets/join_full_outer.png)

## Cross Join

A cross join, also known as a _cartesian join_, is a type of join in SQL that combines every row from one table with every row from another table, resulting in a cartesian product. Here is an example of a cross join:

```sql
SELECT *
FROM users
CROSS JOIN posts;
```

Result:

| id | name   | id | user_id | caption |
| -- | ------ | -- | ------- | ------- |
| 1  | John   | 1  | 1       | foo     |
| 1  | John   | 2  | 1       | bar     |
| 1  | John   | 3  | 2       | baz     |
| 1  | John   | 4  |         | qux     |
| 2  | Harold | 1  | 1       | foo     |
| 2  | Harold | 2  | 1       | bar     |
| 2  | Harold | 3  | 2       | baz     |
| 2  | Harold | 4  |         | qux     |
| 3  | Lionel | 1  | 1       | foo     |
| 3  | Lionel | 2  | 1       | bar     |
| 3  | Lionel | 3  | 2       | baz     |
| 3  | Lionel | 4  |         | qux     |

This statement combines every row from `users` with every row from `posts`, resulting in a table with the columns from both `users` and `posts`. The number of rows in the resulting table would be the product of the number of rows in `users` and the number of rows in `posts`. It is important to note that a cross join does not have a join condition, and as such, it will return a cartesian product even if the tables have no matching rows. Because of this, cross joins are not often used in practice, and other types of joins, such as inner or outer joins, are generally preferred.

## Self-join

This type of join is used to join a table to itself, using an alias for the second instance of the table. It is often used to compare values within a single table.

Using our example tables for a self join wouldn't make a lot of sense, but let's write the code to see the syntax and the results anyway.

```sql
SELECT * FROM users u1
JOIN users u2
ON u1.id = u2.id;
```

Result:

| id  | name   | id  | name   |
| --- | ------ | --- | ------ |
| 1   | John   | 1   | John   |
| 2   | Harold | 2   | Harold |
| 3   | Lionel | 3   | Lionel |

## Conclusion

And that's all about the different types of _joins_ in SQL. I hope this will be useful for you to understand their differences.

Thank you for reading, and see you on another post 👋👋