What is the difference between LEFT JOIN vs RIGHT JOIN and other joins?

General Discussions
1

What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN in MySQL? How do they affect the result set, and when should each be used?

2

Having worked with SQL for over a decade now, one of the most common points of confusion I’ve seen is around the left join vs right join. So here’s the simple difference:

  • A LEFT JOIN returns all rows from the left table and matched rows from the right. If there’s no match, you get NULLs for the right table.
  • A RIGHT JOIN flips that — it returns all rows from the right table and the matching ones from the left.

Here’s an example to make it real:

SELECT customers.id, customers.name, orders.id, orders.amount  
FROM customers  
LEFT JOIN orders ON customers.id = orders.customer_id;

This query shows all customers, even those who haven’t placed any orders. If you changed it to a RIGHT JOIN, you’d get all orders, even if no matching customer exists — though in good schema design, that’s rare.

3

@raimavaswani made a great point. Building on that, after working with relational databases for years in enterprise environments, I’ve noticed that understanding left join vs right join is just the start — it gets even clearer when you compare it with other joins like INNER JOIN, FULL JOIN, or CROSS JOIN.

  • INNER JOIN only pulls the records where there’s a match in both tables.
  • FULL JOIN returns everything from both tables, with NULLs for non-matches.
  • CROSS JOIN? That one’s the wild card — it returns every combination possible (not often needed unless you’re doing matrix-style analysis).

Here’s how a FULL JOIN looks:

SELECT A.id, A.name, B.id, B.value  
FROM A  
FULL JOIN B ON A.id = B.id;

So, if left join vs right join determines what side you’re prioritizing, FULL JOIN says, ‘I want it all, no matter what.’

4

Totally agree with both of you. I’ve been in data engineering roles where selecting the right join literally made or broke reports. So, let’s talk real-world scenarios — when to use which one.

Use INNER JOIN when you only want rows that match on both sides.

  • Choose LEFT JOIN when the left table holds your ‘main’ data and you just want to check for related info on the right.
  • Go with RIGHT JOIN when the right table is more important in the context (which is less common, honestly).
  • If you’re looking to merge everything regardless of matches, that’s a FULL JOIN job.
  • And CROSS JOIN — that’s mostly academic unless you’re doing all possible combinations for analysis.

So ultimately, understanding left join vs right join helps you frame your data — it’s about what data matters most and what gaps you’re okay with.