LEFT JOIN vs. RIGHT JOIN: What’s the Difference in SQL?

Let's compare

When working with SQL, you often need to combine data from multiple tables, and that’s where JOINS come in. But sometimes, you’re stuck deciding between a LEFT JOIN and a RIGHT JOIN. What’s the difference between the two, and when should you use each one?

In this post, we’ll do a head-to-head comparison of LEFT JOIN vs. RIGHT JOIN and help you decide which one makes sense for your query.

What Are JOINS Again?

Before we dive in, let’s do a quick recap:

JOINS in SQL allow you to combine data from two or more tables based on common columns. The most common types are INNER, LEFT, RIGHT, and FULL OUTER JOINS, each with their own way of pulling together data.

In this comparison, we’ll focus on LEFT JOIN and RIGHT JOIN.

LEFT JOIN

A LEFT JOIN returns all the rows from the left table (the table before the JOIN keyword) and the matching rows from the right table. If there’s no match, the result will show NULL values for the columns from the right table.

Here’s a basic example of a LEFT JOIN:

SELECT 
	c.customerId, c.customerName, 
	o.orderId, o.orderDate
FROM customers c
LEFT JOIN orders o ON c.customerId = o.customerId 

What happens here? All rows from the customers table (left table) will be returned, and wherever there’s no matching customerId in the orders table, NULL values will appear in the result for those customer-related columns.

Like this:

Think of it this way: the LEFT JOIN keeps everything from the left table and only pulls what it can from the right table.

RIGHT JOIN

A RIGHT JOIN is just like a LEFT JOIN, but in reverse. It returns all rows from the right table (the table after the JOIN keyword) and the matching rows from the left table. If no match is found in the left table, the result will show NULL values for the columns from the left table.

Here’s how a RIGHT JOIN works:

SELECT 
	c.customerId, c.customerName, 
	o.orderId, o.orderDate
FROM customers c
RIGHT JOIN orders o ON c.customerId = o.customerId 

What happens here? All rows from the orders table (right table) will be returned, and where there’s no matching customerId in the customers table, NULL values will appear for the columns from the customers table.

Like this:

In this case, the RIGHT JOIN keeps everything from the right table and only pulls what it can from the left table.

LEFT JOIN vs. RIGHT JOIN: The Key Difference

LEFT and RIGHT JOINS might seem like they do the same thing but in opposite directions. The key difference is which table they prioritize.

• LEFT JOIN: Returns all rows from the left table and matches from the right table.

• RIGHT JOIN: Returns all rows from the right table and matches from the left table.

When would I need to use RIGHT over LEFT?

In practice, you’ll probably use LEFT JOIN more often, simply because we tend to start with the more important table (left) and then pull in related data. But if your logic needs to prioritize the right table, that’s when a RIGHT JOIN makes sense.

Imagine you’ve built a complex query starting with a FROM and adding a series of joins to pull together a detailed set of data. Then, suddenly, you realize you need to join another table at the end to grab a random value. That’s where a RIGHT JOIN can save you. Instead of rewriting the entire query to make that new table the starting point, you can simply use a RIGHT JOIN to tack it on at the end, avoiding the hassle of restructuring your whole query.

In Conclusion

While LEFT and RIGHT JOINS do similar things in SQL, the main difference is which table they prioritize. Think of them as two sides of the same coin. If you’re pulling all the rows from one table and only what matches from the other, it’s all about which table you consider more important.