TryBench
Data Analysis & SQL · Practitioner lesson 10 of 19

SQL LEFT JOIN Example: Keep Every Customer

Join database tables without accidentally losing records or multiplying totals.

Why this matters

An INNER JOIN can hide customers who have not ordered yet. Your report then looks complete, but it is not.

INNER JOIN Can Hide Rows

An INNER JOIN drops customers with zero orders. If you report on all users, your count will be wrong.

In real life: INNER JOIN keeps only customers who have orders. A customer with zero orders disappears. LEFT JOIN keeps every customer from the first table.

Count a Column, Not Everything

Use COUNT(o.order_id), not COUNT(*). A customer with no orders then shows 0. COUNT(*) would wrongly show 1.

In real life: COUNT(o.order_id) counts real orders. COUNT(*) would count the empty joined row as 1, which is wrong.

Group by the ID Too

Group by the customer ID as well as the name, so two different people with the same name stay in separate rows.

In real life: Two different customers can both be named "Ada Obi". If you group only by name, they become one person. Their customer IDs are always different.

Your practice task

Generate a customer activity report showing every customer and their total order count. Ensure customers who haven't placed an order yet are still included in the report (showing 0 orders) rather than disappearing from the list, and name the order count total_orders.

How to do it

  1. Decide which table lists everyone you must keep.
  2. Join the orders so customers with no orders still appear.
  3. Count real orders, not empty rows.
  4. Group so each customer is one row, then press Run proof.

Practice it for free in the app. You get instant checks that show what you got right and what to fix. A lesson takes about two minutes and you can try as many times as you like.

Quick reference: Safe Null-Preserving Join

Guarantees users or entities with zero events remain visible in summary reports.

SELECT a.id, a.name, COUNT(b.id) AS activity_count
FROM primary_table a
LEFT JOIN event_table b ON a.id = b.primary_id
GROUP BY a.id, a.name;

Practice this lesson free

More lessons in this course

See all 19 lessons in Data Analysis & SQL