INNER JOIN vs LEFT JOIN in SQL

An INNER JOIN returns only rows with a match on both sides. That is often what you want, but it hides rows that have no related record.

A LEFT JOIN keeps every row from the left table. Missing matches from the right table appear as NULL.

INNER JOIN vs LEFT JOIN at a glance

select left_table.id, count(right_table.id) as match_count
from left_table
left join right_table
  on left_table.id = right_table.left_id
group by left_table.id

A concrete example

In the Garden Shop data, some customers have orders and some do not. AnINNER JOIN between customers andorders drops customers with no orders because there is no matching order row to return.

select c.customer_id,
       c.first_name,
       o.order_id
from customers c
inner join orders o
  on o.customer_id = c.customer_id
order by c.customer_id, o.order_id

A LEFT JOIN starts from customers and keeps every customer. If a customer has no matching order, the order columns areNULL.

select c.customer_id,
       c.first_name,
       o.order_id
from customers c
left join orders o
  on o.customer_id = c.customer_id
order by c.customer_id, o.order_id

Choose based on missing matches

Start with the question you are answering. If you only care about customers who placed orders, an inner join is fine. If you need every customer, including customers with no orders, put customers on the left and use a LEFT JOIN.

Count carefully after a left join. count(*) counts the kept left row even when the right side is missing; count(o.order_id) counts only matched orders, which is why no-order customers show 0.

Common JOIN gotchas

• A LEFT JOIN can accidentally behave like anINNER JOIN if you put a right-table filter inWHERE. Seewhy a LEFT JOIN turns into an INNER JOIN.
• For INNER JOIN, keep relationship logic in ONand row filters in WHERE. Seewhere INNER JOIN filters belong.
• If a join makes counts or sums too high, check whether the join multiplied rows. Seewhy COUNT or SUM is too high after a JOIN.