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.

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

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.

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

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

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

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.

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

Full Outer Join

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

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

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:

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.

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