CodePark/ questions / SQL queries & joins
START PRACTICINGPRACTICE
Backend question · Databases & SQL › SQL queries & joins

What is the difference between an INNER JOIN and a LEFT JOIN? When would you pick each?

An INNER JOIN returns only the row pairs that match in both tables. A LEFT JOIN returns every row from the left table, plus the matching rows from the right table.

INNER JOIN: the join condition must match on both sides. For example, a customer with no orders disappears from customers INNER JOIN orders.

LEFT JOIN: when a left row has no match, the right-side columns come back as NULL. So customers LEFT JOIN orders keeps the customers who never ordered.

When to pick each:

  • Use an inner join when you only care about matched pairs.
  • Use a left join when every left row must appear. For example, you list all customers with their order count.

Watch out for one thing with both joins. If one left row matches several right rows, the left row is repeated once per match. This is a common cause of counts and sums that are too big.

  • An INNER JOIN keeps only the rows that match in both tables.
  • A LEFT JOIN keeps all left rows, with NULLs where the right row is missing.
  • Pick the join based on whether unmatched left rows must appear.
  • In a one-to-many join, the left row is repeated once per match.