Types of SQL Joins
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
posts tables for our examples:
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
posts where the
id column is the same in both tables. The resulting table would include only the columns from
posts, and would include a row for each pair of matching rows from the two tables.
Let’s visualize the result as sets so we can understand it better. The blue are is our result set:
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;
And as a set:
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?
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;
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;
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;
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;
This statement combines every row from
users with every row from
posts, resulting in a table with the columns from both
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.
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;
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 👋👋