Difference between Left Join and Right Join

MySQL has mainly two kinds of joins named LEFT JOIN and RIGHT JOIN. The main difference between these joins is the inclusion of non-matched rows. The LEFT JOIN includes all records from the left side and matched rows from the right table, whereas RIGHT JOIN returns all rows from the right side and unmatched rows from the left table. In this section, we are going to know the popular differences between LEFT and RIGHT join. Before exploring the comparison, let us first understand JOIN, LEFT JOIN, and RIGHT JOIN clause in MySQL.

What is JOIN Clause?

A join is used to query data from multiple tables and returns the combined result from two or more tables through a condition. The condition in the join clause indicates how columns are matched between the specified tables.

What is the LEFT JOIN Clause?

The Left Join clause joins two or more tables and returns all rows from the left table and matched records from the right table or returns null if it does not find any matching record. It is also known as Left Outer Join. So, Outer is the optional keyword to use with Left Join.

We can understand it with the following visual representation:

To read more information about the LEFT join, click here.

What is the RIGHT JOIN Clause?

The Right Join clause joins two or more tables and returns all rows from the right-hand table, and only those results from the other table that fulfilled the specified join condition. If it finds unmatched records from the left side table, it returns Null value. It is also known as Right Outer Join. So, Outer is the optional clause to use with Right Join.

We can understand it with the following visual representation.

To read more information about RIGHT JOIN, click here.

Syntax of LEFT JOIN Clause

The following is the general syntax of LEFT JOIN:

The following is the general syntax of LEFT OUTER JOIN:

Syntax of RIGHT JOIN Clause

The following is the general syntax of RIGHT JOIN:

The following is the general syntax of RIGHT OUTER JOIN:

LEFT JOIN vs. RIGHT JOIN

The following comparison table explains their main differences in a quick manner:

Example

Let us understand the differences between both joins through examples. Suppose we have a table named "customer" and "orders" that contains the following data:

Table: customer

Table: orders

LEFT JOIN Example

Following SQL statement returns the matching records from both tables using the LEFT JOIN query:

After successful execution of the query, we will get the output as follows:

RIGHT JOIN Example

Following SQL statement returns the matching records from both tables using the RIGHT JOIN query:

After successful execution of the query, we will get the output as follows: