Left Join vs Right Join

Difference Between Left Join vs Right Join

In this article, we will learn about the key differences between the left and the right outer join. There are two types of any join i.e. inner and outer. When we just say left join what we mean is the left outer join and when specified as right join, it stands for right outer join. When we have to use the inner joins then we need to explicitly mention it in case of left and right joins such as left inner join and right inner join. By simply mentioning the left join vs right join what we actually mean is left outer join versus right outer join.

Head to Head Comparison Between Left Join vs Right Join  (Infographics)

Below are the top differences between Left Join and Right Join

Top Key Differences Between Left Join vs Right Join

Below are the respective key differences between Left Join and Right Join:

When we consider outer joins simply left join and right join the key difference lies in which unmatched records are added to the matched records to prepare the final result. In the case of left join, all the matched records plus unmatched records from the left table are considered in the result set.

The left table here refers to the table used in the from clause i.e. the table that lies to the left of the join clause and the right table is the table that lies to the right side of the join clause in the SQL query. The right join will consider all the matched records of both tables in join and additional unmatched records of the right table in the final result set.

The following Venn diagram illustrates how left and right joins work to form result set –

Example –

Let us consider one example –

We have two tables named teams and developers having the following structure –

For developers table –

For teams table –

The relation between the two tables is that the team_id of the “developers” table is the same as the id column of the “teams” table. Besides this, the developer’s table has the developer_id as the primary key column for the developer’s table and other required columns such as name, position, and technology. The team’s table has the column id as the primary key that helps in identifying the team record uniquely and other details like team_count and department.

we have inserted some records in both the tables. Let us retrieve and see the contents.

For developers table –

select * from developers;

For teams table –

select * from teams;

Note that there are some of the records in both the tables that do not have matching records. For example, the developer’s table contains a record with name Siddharth with team id 7 but there is no record in the team’s table with id 7. Similarly, the team’s table has the record with department CRM, Bug Solver, and Document with ids 4,5 and 6 respectively that do not have any matching records in table developers.

Now, let us perform the left join on tables teams and developers in the following way –

SELECT * FROM teams t LEFT JOIN developers d ON t.id=d.team_id;

that gives the following output –

Note that all the matched records of teams and developers table are retrieved and along with that as the join is the left join and teams table lies in the left side of the join all the unmatched records of the left table teams are also retrieved and the columns for the right table, in that case, are filled as NULL.

Now, let us perform the right join on tables teams and developers in the following way –

SELECT * FROM teams t RIGHT JOIN developers d ON t.id=d.team_id;

that gives the following output –

In case of right join, all the matching records of the teams and developers table are retrieved and additionally unmatched records from the right table i.e table residing on the right side of the join that in our case is developers are also retrieved. In our example, the unmatched records from the developer’s record with Siddharth name are retrieved in the right join query and the columns of the left table are filled as null.

Comparison table of Left Join vs Right Join

Let’s see some more differences between Left Join vs Right Join through a comparison table for clear understanding:

Left Join	Right Join
The left join contains the matched records of the two tables and additional unmatched records of the left table that resides to the left of the join clause. For this unmatched records, the columns of the right table are filled as null.	The right join contains the matched records of the two tables and additional unmatched records of the right table that resides to the right of the join clause. For this unmatched records, the columns of the left table are filled as null.
Through Venn diagram of the above example, the left join can be demonstrated with below Venn diagram – For the query – SELECT * FROM teams t LEFT JOIN developers d ON t.id = d.team_id ;	Through Venn diagram of the above example, the right join can be demonstrated with below Venn diagram – For the query – SELECT * FROM teams t RIGHT JOIN developers d ON t.id = d.team_id ;
The left join is a left outer join in ANSI SQL.	The right join is a right outer join in ANSI SQL.
Left Join = All the matching records + unmatched records of the left table	Left Join = All the matching records + unmatched records of the right table
While the execution of the query containing the left join all the records of the left table are retrieved first and then using nested loops the matched records of the right table column values are retrieved and for other unmatched records, the NULL value is filled.	While the execution of the query containing the right join all the records of the right table are retrieved first and then using nested loops the matched records of the left table column values are retrieved and for other unmatched records NULL value is filled.
*= is used to represent the left outer join in Transact-SQL.	=* is used to represent the right outer join in Transact-SQL.

Conclusion

The most substantial difference between the left and right outer join lies in the unmatched records that are obtained besides matched records. The left join takes all matching records and unmatched records of the left table while the right join takes all matching records and unmatched records of the right table.

Recommended Articles

This is a guide to Left Join vs Right Join. Here we discuss the Left Join vs Right Join key differences with infographics and comparison table. You may also have a look at the following articles to learn more –

1. Inner Join vs Outer Join
2. Cassandra vs Couchbase
3. Jira vs ServiceNow
4. Redis vs Memcached