Introduction

INNER JOIN and OUTER JOIN differ in how they treat rows that do not have a match on the other side of the join condition. That one rule changes both correctness and row counts, which is why join bugs often show up as mysteriously missing records or inflated aggregates.

INNER JOIN Keeps Only Matching Rows

An INNER JOIN returns rows only when the join condition matches on both sides.

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

If a customer has no order, that customer does not appear in the result. This is the right join when unmatched rows are irrelevant to the question being asked.

For example, if the question is "show all orders with customer names," then an inner join is natural because the result is supposed to contain only real order-to-customer matches.

OUTER JOIN Preserves Unmatched Rows

An outer join keeps unmatched rows from one side or both sides, depending on which kind of outer join you choose.

• 'LEFT OUTER JOIN keeps all rows from the left table'
• 'RIGHT OUTER JOIN keeps all rows from the right table'
• 'FULL OUTER JOIN keeps unmatched rows from both sides'

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

In this query, every customer appears. If a customer has no order, the order columns are NULL.

That makes outer joins the right tool for questions such as "which customers have not placed an order" or "show every customer, even those with no related records."

Filter Placement Can Change the Meaning

A very common mistake is to write a LEFT JOIN and then place a filter on the right-hand table in the WHERE clause.

1SELECT c.id, c.name, o.id
2FROM customers c
3LEFT JOIN orders o ON o.customer_id = c.id
4WHERE o.status = 'PAID';

That filter removes rows where o is NULL, which means the query now behaves more like an inner join for that condition.

If you want to preserve unmatched left-side rows while filtering the joined table, move the filter into the ON clause.

1SELECT c.id, c.name, o.id
2FROM customers c
3LEFT JOIN orders o
4  ON o.customer_id = c.id
5 AND o.status = 'PAID';

This is one of the most important practical join habits to learn.

Aggregates and Row Multiplication

Join choice also affects aggregates. In one-to-many relationships, joining can multiply rows. If you then count rows without thinking about the business entity you actually meant to count, the result can be wrong.

That is why questions like "how many customers do we have" often require COUNT(DISTINCT c.id) or pre-aggregation instead of a raw count after a join.

Join correctness is not only about row presence. It is also about which unit of data each result row represents.

Performance Starts with Correct Semantics

Performance matters, but the first question is always semantic correctness. Choose the join type that matches the business rule, then improve indexing and query shape afterward.

Indexes on join keys help both inner and outer joins. But no amount of indexing can fix a query that chose the wrong join semantics.

Common Pitfalls

Using INNER JOIN by habit can accidentally remove valid unmatched business records.

Applying WHERE filters on the nullable side of a LEFT JOIN can undo the outer-join behavior you intended.

Ignoring row multiplication in one-to-many joins can break counts and totals.

Summary

• 'INNER JOIN returns only matching rows.'
• 'OUTER JOIN preserves unmatched rows from one side or both sides, depending on the join type.'
• 'LEFT JOIN plus a WHERE filter on the right table can accidentally behave like an inner join.'
• Always choose join type based on business meaning first, then optimize performance.