Differences between Left and Right Join

Left vs Right Join

When dealing with SQL, joins is a common name that will pop up every now and then. The join clause helps in the combination of records from different tables in a data set. The join in effect creates a table that can either be used as it is or rolled out with others.  It is therefore advisable to understand these joins and the differences between them so as to know the specific join, and whether right or left ought be taken in special instances.

To understand joins better and their differences, it is important to understand the types of joins available. There are generally three types of joins. These joins include the inner join, the cross join and the outer join. The inner join generally compares tables and will only give or return a result in the event that a match is found. The main function of an inner join is to reduce the size of result sets.

Cross joins on the other hand compare two tables and give a return of each and every possible combination coming from the rows of both tables. Numerous results are expected from this join and most of them might not even be meaningful. Use of this join should therefore be practiced cautiously.

The outer join takes a comparison of tables and returns data when a match is available. Just as seen in the inner join, the outer join duplicates rows in a given table in the event that matching records are seen. The results of outer joins are normally larger and thus the data sets are big because the set in itself is not removed from the set.

A left join refers to keeping all of the records from the 1st table irrespective of result, and the insertion of NULL values when the second table values do not match. A right join, on the other hand, refers to keeping all of the records coming from the 2nd table irrespective of what the result is, and the use of NULL values when the results do not match with those in the 1st table.

A left outer join therefore retains all the rows that are in the ‘left’ table irrespective of whether there is a row matching it on the ‘right’ table. The left table therefore refers to the table that comes first in the joint statement. It should appear to the left of the keyword ‘join’ occurring on the table. When a left outer join is done, all rows from the left should be returned. Non-matching columns in the table will be filled with a NULL whenever there is no match.

It is important to note that when dealing with these two joins, all the rows from the left of the table are shown in the table in a left outer join. The results display themselves irrespective of whether any matching columns are present in the right table.  In a right outer join, all rows from the right are shown and this is irrespective of whether there are any matching columns in the left side of the table.

The choice of whether to go for the left outer join or the right outer join does not matter as the same results are shown. The functionality that a right outer join presents is the same functionality that the left outer join presents. By simply switching the order in which the tables appear in the SQL statement, you can expect similar results, irrespective of the join used.

Summary

Joins come in three main groups – inner join, cross join and outer join

A left outer join displays all the rows from the left of the table

A right join displays all rows from the right of the table

Alternating the SQL sequence can eliminate use of both right and left outer joins and instead use only one.

Sharing is caring!