SQL: Find the Customers Who Stopped Ordering With a LEFT JOIN (and Why a Plain JOIN Hides the One You Most Need to Call)

Most small businesses don't lose customers in one dramatic phone call. They lose them quietly. A dental practice that ordered every month just stops, and nobody notices until the year-end review.

The fix is a list every Monday: customers with no order since a given date, with their account manager next to each name so someone can call.

That list is one query. But there's a trap in it, and the trap hides the customer you most need to call. Here's the query, and the trap, from my free lesson. You can paste everything into DB Fiddle (free, no account, pick MySQL).

Two tables

Lakeside Office Supply is a made-up shop. Paste both tables into the Schema SQL box:

CREATE TABLE customers (
name VARCHAR(40), manager VARCHAR(20));

INSERT INTO customers VALUES
('Maple Dental', 'Sam'), ('Cobalt Legal', 'Ana'),
('Northside Realty', 'Sam'), ('Harbor Accounting', 'Ana'),
('Elm Street Law', 'Ana');

CREATE TABLE orders (
id INT, order_date DATE,
customer VARCHAR(40), amount INT);

INSERT INTO orders VALUES
(1, '2026-07-02', 'Maple Dental', 1250),
(2, '2026-07-15', 'Cobalt Legal', 860),
(3, '2026-07-28', 'Northside Realty', 2140),
(4, '2026-08-06', 'Cobalt Legal', 2310),
(5, '2026-08-19', 'Harbor Accounting', 640),
(6, '2026-08-27', 'Maple Dental', 980),
(7, '2026-09-09', 'Northside Realty', 1890),
(8, '2026-09-22', 'Cobalt Legal', 1270);

Keep an eye on Elm Street Law. It signed up but has never ordered.

The obvious query, and what it misses

Put each customer next to their latest order:

SELECT c.name, c.manager, MAX(o.order_date) AS last_order
FROM customers c
JOIN orders o ON o.customer = c.name
GROUP BY c.name, c.manager;

c and o are short names for the two tables. JOIN ... ON matches each order to the customer with the same name, and MAX finds the latest date per customer.

Run it and you get four rows. Five customers went in. Elm Street Law is gone.

That's because a plain JOIN (an inner join) only keeps rows that have a match on both sides. A customer with no orders has nothing to match, so it's dropped. No error, no warning. And a customer who has never ordered is exactly who belongs at the top of the call list.

One word fixes it

Change JOIN to LEFT JOIN:

SELECT c.name, c.manager, MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer = c.name
GROUP BY c.name, c.manager;

A LEFT JOIN keeps every row from the left table (customers), whether it has orders or not. Five rows now. Elm Street Law is back, with NULL as its last order.

Keep only the quiet ones

Now filter to customers with nothing since 1 September, or nothing at all:

SELECT c.name, c.manager, MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer = c.name
GROUP BY c.name, c.manager
HAVING MAX(o.order_date) < '2026-09-01'
OR MAX(o.order_date) IS NULL
ORDER BY last_order;

name	manager	last_order
Elm Street Law	Ana	NULL
Harbor Accounting	Ana	2026-08-19
Maple Dental	Sam	2026-08-27

Two calls for Ana, one for Sam.

Two details are worth knowing:

• HAVING, not WHERE. WHERE filters rows before grouping. HAVING filters the totals after GROUP BY, and MAX(o.order_date) only exists after grouping.
• The IS NULL line is not optional. NULL < '2026-09-01' isn't true, it's unknown, so without the OR ... IS NULL line Elm Street Law disappears a second time. In MySQL, NULLs also sort first with ORDER BY, which puts the never-ordered accounts at the top.

The rule to remember

Any "who has not done X" question (not ordered, not paid, not opened, not renewed) needs a LEFT JOIN and an IS NULL check. A plain JOIN can only ever show you people who did something.

On real data, join on a customer ID rather than a name. Names get typed two ways; IDs don't. To reuse the list, change the date in the HAVING line and run it every Monday.

Practice it free

The lesson, SQL: Find Customers Who Have Gone Quiet with a LEFT JOIN, builds this call list in 17 steps on real DB Fiddle screens, including the moment Elm Street Law vanishes. You type every line yourself, it checks each step, and it takes about 15 minutes. It's module 3 of the free SQL for Business Reports course. If you're new to SQL, start with module 1, the monthly revenue report. The plain-words version of the idea is on the LEFT JOIN glossary page.

Practice it step by step, free: https://learn.ynteractive.com/content/sql-quiet-customers