What Is the Difference Between LEFT JOIN and RIGHT JOIN?

When working with SQL, understanding what is the difference between LEFT JOIN and RIGHT JOIN is crucial for retrieving data efficiently. Both joins allow you to combine data from multiple tables, but they handle unmatched records differently. In this guide, I’ll explain the key differences between LEFT JOIN and RIGHT JOIN with examples to help you understand when to use each. Personally, I don’t use RIGHT JOIN very often, but it is very important that you understand the difference.

Understanding Joins in SQL

Before diving into the differences, let’s define what JOINs do in SQL. A JOIN is used to retrieve data from multiple tables based on a common column. The most common types are INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Now, let’s focus on LEFT JOIN and RIGHT JOIN to understand how they differ.

What Is LEFT JOIN in SQL?

A LEFT JOIN returns all records from the left table and the matching records from the right table. If no match is found, NULL values are returned for columns from the right table.

LEFT JOIN Syntax:

SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

Example of LEFT JOIN:

Consider two tables:

1. Products (ProductID, ProductName)
2. Orders Details (OrderID, Quantity)

If we want a list of all products, including those for which we didn’t have any orders, we can use the following SQL query:

SELECT prod.ProductID, prod.ProductName, ordet.OrderID, ordet.Quantity
FROM Products prod
LEFT JOIN [Order Details] ordet ON prod.ProductID = ordet.ProductID

LEFT JOIN Result:

Here are some of the results from our query:

Since we didn’t have any orders for Vegie-spread, the order details appear as NULL.

This is what it looks like in SQL Server Management Studio:

What Is RIGHT JOIN in SQL?

A RIGHT JOIN is the opposite of a LEFT JOIN. It returns all records from the right table and only the matching records from the left table. If there’s no match, NULL values appear for columns from the left table.

RIGHT JOIN Syntax:

SELECT table1.column1, table2.column2
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

Example of RIGHT JOIN:

Using the same Products and Order Details tables, if we want to list all orders, including those without a matching product, we use:

SELECT prod.ProductID, prod.ProductName, ordet.OrderID, ordet.Quantity
FROM Products prod
RIGHT JOIN [Order Details] ordet ON prod.ProductID = ordet.ProductID

Of course, this will not work in our case, since all the records in Order Details table have a ProductID that really exists in Products table. But here is an example of how it could look:

RIGHT JOIN Result:

Here, if an order exists without a corresponding product (perhaps due to a deletion), the ProductName field will be NULL.

What Is the Difference Between LEFT JOIN and RIGHT JOIN?

Here are just a few key differences:

When to Use LEFT JOIN vs. RIGHT JOIN

• Use LEFT JOIN when you want to keep all records from the primary table and fetch related data if available. Example: Get all products, even those without orders.
• Use RIGHT JOIN when the secondary table is more important, and you want to include all its records. Example: Get all orders, even if no product record exists.

Conclusion

Now that you know what is the difference between LEFT JOIN and RIGHT JOIN, you can choose the right join type based on your data needs. LEFT JOIN is generally more common, but RIGHT JOIN can be useful in specific cases where you need all records from the right table.

You can also check one of my previous posts on How to Do INNER JOIN in SQL.

Would you like additional SQL examples or explanations? Let me know in the comments!

If you would like to learn more about SQL, check out this amazing book on Amazon.