Lesson 5.3

INNER JOIN vs LEFT JOIN in SQL

INNER JOIN keeps matches only. LEFT JOIN keeps every row from the left table.

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.

Pattern

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

Ready to run it?

Open the DuckDB playground with the matching dataset and query already filled in.

Try INNER JOIN in the playground Try LEFT JOIN in the playground

See the difference on Garden Shop data

Some customers have orders and some don't, which is what makes this a useful place to watch the two joins behave differently. An INNER JOIN between customers and orders drops customers with no orders, because there is no matching order row to return.

INNER JOIN: customers with orders only

select c.customer_id,
       c.first_name,
       o.order_id
from customers as c
inner join orders as 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 are NULL.

LEFT JOIN: every customer stays

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

Count the right column

This is the part the exercise turns on. 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 an INNER JOIN if you put a right-table filter in WHERE: the kept rows have NULL on the right side, so a condition like o.status = 'shipped' throws them away. Put it in ON instead. See why a LEFT JOIN turns into an INNER JOIN.
For INNER JOIN, keep relationship logic in ON and row filters in WHERE. See where INNER JOIN filters belong.
If a join makes counts or sums too high, check whether the join multiplied rows. See why COUNT or SUM is too high after a JOIN.

Syntax reference

Need syntax details? See the SQL JOIN types reference

Schema · Garden Shop Tables · customers, orders 2 tables

Table · customers

One row per customer. Some customers have no phone on file.

9 columns · 20 rows

customer_id int first_name text last_name text email text phone text city text state text signup_date date is_active bool

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Return every customer with their total order count. Include customers who have placed no orders. Sort by order count so customers with no orders appear first, then by customer id.