INNER JOIN vs LEFT JOIN in SQL

Quick answer

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table, plus matching rows from the right table.

That means the real difference is what happens to rows that do not have a match:

• INNER JOIN: unmatched rows disappear
• LEFT JOIN: unmatched left-side rows stay, with NULL on the right side

In this guide

Sample tables

Suppose you have these two tables:

customers

orders

What INNER JOIN does

INNER JOIN keeps only rows where the join condition matches in both tables.

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

This query returns:

What LEFT JOIN does

LEFT JOIN keeps every row from the left table, even when there is no match in the right table.

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

This query returns:

Compare the results

Why NULL values appear

In a LEFT JOIN, when SQL cannot find a matching row on the right side, it still keeps the left-side row. Since there is no right-side data to show, SQL fills those right-side columns with NULL.

That is why Chris and Dana appear in the LEFT JOIN result with NULL for order_id and amount.

Finding missing matches

One of the most useful LEFT JOIN patterns is finding rows that do not have a match in another table.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This returns customers who have no orders.

When to use each one

Use INNER JOIN when:

• you only care about rows that definitely match
• unmatched rows should be excluded
• you want related records only

Use LEFT JOIN when:

• you need all rows from the main table
• you want to preserve missing relationships
• you want to find rows with no match

Common mistakes

• Using INNER JOIN when you actually needed to keep unmatched rows
• Forgetting that LEFT JOIN can produce NULL values on the right side
• Putting filters in the wrong place and accidentally turning a LEFT JOIN into an INNER JOIN
• Joining on the wrong key column
• Assuming one row on the left always matches only one row on the right

Practice questions

Bottom line

INNER JOIN returns only matching rows. LEFT JOIN keeps everything from the left table and fills missing right-side values with NULL.

If you remember that one rule, join behavior gets much easier to predict.

Related SQL tutorials