Difference Between LEFT JOIN and RIGHT JOIN in MySQL

Understanding the difference between a LEFT JOIN and a RIGHT JOIN in MySQL is fundamental for retrieving data accurately from multiple tables. While both are outer joins used to combine rows based on a related column, they differ in which table is treated as the primary source of truth. This article explains the behavioral differences between these two joins, how they handle unmatched data, and how to apply them in your SQL queries.

The Core Difference

The primary distinction between LEFT JOIN and RIGHT JOIN lies in the direction of the table dependency.

• LEFT JOIN (or LEFT OUTER JOIN): This join returns all rows from the left table (the table specified before the JOIN keyword), along with the matching rows from the right table (the table specified after the JOIN keyword). If there is no match for a row from the left table in the right table, the resulting columns from the right table will contain NULL.
• RIGHT JOIN (or RIGHT OUTER JOIN): This join does the exact opposite. It returns all rows from the right table, along with the matching rows from the left table. If there is no match for a row from the right table in the left table, the resulting columns from the left table will contain NULL.

How They Behave in Practice

To visualize the behavior, consider two database tables: Customers (left table) and Orders (right table).

Using LEFT JOIN

SELECT Customers.customer_name, Orders.order_id
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

• Behavior: This query retrieves all customers. If a customer has placed an order, their order ID is displayed. If a customer has never placed an order, the customer’s name still appears in the results, but the order_id column will display as NULL.

Using RIGHT JOIN

SELECT Customers.customer_name, Orders.order_id
FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

• Behavior: This query retrieves all orders. If an order is associated with an existing customer, the customer’s name is displayed. If there is an order in the database that is not linked to any valid customer (an orphaned record), the order will still appear in the results, but the customer_name column will display as NULL.

Syntactic Equivalence

From a functional standpoint, any RIGHT JOIN can be rewritten as a LEFT JOIN simply by reversing the order of the tables in the SQL statement.

The following two queries yield identical results:

/* Query A */
SELECT * FROM Table_A LEFT JOIN Table_B ON Table_A.id = Table_B.id;

/* Query B (Equivalent to Query A) */
SELECT * FROM Table_B RIGHT JOIN Table_A ON Table_B.id = Table_A.id;

Which One Should You Use?

In the database development community, LEFT JOIN is almost universally preferred over RIGHT JOIN.

Because Western languages are read from left to right, structuring queries using LEFT JOIN allows developers to easily follow the logic of starting with a primary table and bringing in optional, supplementary data. Furthermore, because the MySQL query optimizer automatically translates RIGHT JOIN queries into equivalent LEFT JOIN structures under the hood, there is no performance benefit to choosing one over the other. Stick to LEFT JOIN for consistency and readability.