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_conditionSyntax 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:
Leave a Reply