Left Join VS Right Join

[vishrut-singhal]

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:

SELECT column_list FROM table_name1
LEFT JOIN table_name2
ON column_name1 = column_name2
WHERE join_condition

The following is the general syntax of LEFT OUTER JOIN:

SELECT column_list FROM table_name1
LEFT OUTER JOIN table_name2
ON column_name1 = column_name2
WHERE join_condition

Syntax of RIGHT JOIN Clause

The following is the general syntax of RIGHT JOIN:

SELECT column_list FROM table_name1
RIGHT JOIN table_name2
ON column_name1 = column_name2
WHERE join_condition

The following is the general syntax of RIGHT OUTER JOIN:

SELECT column_list FROM table_name1
RIGHT OUTER JOIN table_name2
ON column_name1 = column_name2
WHERE join_condition

LEFT JOIN vs. RIGHT JOIN

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

LEFT JOIN	RIGHT JOIN
It joins two or more tables, returns all records from the left table, and matching rows from the right-hand table.	It is used to join two or more tables, returns all records from the right table, and matching rows from the left-hand table.
The result-set will contain null value if there is no matching row on the right side table.	The result-set will contain null value if there is no matching row on the left side table.
It is also known as LEFT OUTER JOIN.	It is also called as RIGHT OUTER JOIN.

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:

SELECT cust_id, cust_name, order_num, order_date
FROM customer LEFT JOIN orders
ON customer.cust_id = orders.order_id
WHERE order_date < ‘2020-04-30’;

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:

SELECT cust_id, cust_name, occupation, order_num, order_date
FROM customer
RIGHT JOIN orders ON cust_id = order_id
ORDER BY order_date;

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