EasyConcept

What is the difference between INNER JOIN and LEFT JOIN in SQL?

Technology:
SQL
Experience:
Intern,
Junior
Topics:
SQL,
Database

Quick answer

INNER JOIN returns only rows that have a match in both tables, while LEFT JOIN returns every row from the left table and fills the right table's columns with NULL when there is no match.

Detailed explanation

Use INNER JOIN when you only want records that exist on both sides, such as orders that have a customer. Use LEFT JOIN when the left table is the main list and the related data is optional, such as all customers including those who have never placed an order.

A common trick is to find rows with no match by left joining and filtering on WHERE right.id IS NULL. Be careful: putting a condition on the right table in the WHERE clause turns a left join back into an inner join, so put it in the ON clause instead. RIGHT JOIN is the mirror image of LEFT JOIN, and FULL OUTER JOIN keeps unmatched rows from both sides.

Example

sql
-- every customer, with order count (0 if none)
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- customers who never ordered
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

How to explain it in an interview

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table and fills the right side with NULL when there is no match. I reach for LEFT JOIN when missing data matters, for example customers with zero orders.

Key points

  • INNER: matches only. LEFT: all left rows plus matches
  • Unmatched right-side columns are NULL
  • Filter the right table in ON, not WHERE, to keep left-join behaviour

Common mistakes

  • Filtering the right table in the WHERE clause after a LEFT JOIN, which removes the NULL rows and turns it back into an inner join. Put that condition in the ON clause.
  • Forgetting that a one-to-many join multiplies rows, which inflates SUM and COUNT results.

Follow-up questions

  • What is a self join?
  • What is a CROSS JOIN?