First — the two sources of NULLs, treated differently:

Customers with no purchases — they exist in customers but have no matching row in purchases. Whether you keep them or drop them depends on your question.
Purchases with missing customer_id — they're orphan transactions. They may be data-quality issues, anonymous guests, or canceled signups. Always investigate before silently dropping them.

Join options, by what they do:

INNER JOIN customers c ON c.id = p.customer_id — only customers who have at least one purchase. Drops both kinds of NULLs silently. Use when your question is "average spend per buyer."
LEFT JOIN purchases p ON p.customer_id = c.id — every customer appears, with NULL-filled purchase columns for non-buyers. Use when your question is "average spend per customer, including non-buyers at $0." Remember to wrap sums in COALESCE(SUM(p.amount), 0), not SUM(COALESCE(p.amount, 0)) (SUM already ignores NULLs, but the row might be entirely NULL if the customer has no purchases).
FULL OUTER JOIN — both orphan customers and orphan purchases appear. Use for data-quality audits. Never use by default — usually you want one side authoritative.

The diagnostic pattern:

SELECT
  COUNT(*) AS total_customers,
  COUNT(p.customer_id) AS with_purchases,
  COUNT(*) - COUNT(p.customer_id) AS never_purchased
FROM customers c
LEFT JOIN purchases p ON p.customer_id = c.id
GROUP BY c.id

Always run this before you start aggregating. If 40% of your customers have no purchases and you did an INNER JOIN, you silently dropped 40% of your denominator.

For orphan purchases (missing customer_id):

SELECT COUNT(*) FROM purchases WHERE customer_id IS NULL;

Before dropping them, ask:

Are they all recent? (pipeline bug last week)
Are they all the same merchant? (integration issue with one partner)
Do they correlate with a specific action? (guest checkout that doesn't require signup)

Each answer points to a different remedy. If it's guest checkouts, assign them to an anonymous_guest sentinel row. If it's a pipeline bug, fix the pipeline before silently excluding 3% of revenue from every report.

The senior instinct: before writing the JOIN, ask what the denominator of the question is. "Customers" means LEFT JOIN. "Buyers" means INNER JOIN. Getting this wrong silently shifts your metric by 20-50% in most real datasets.

How do you handle NULLs when joining two tables?

Draft your answer

Stuck? Peek at a hint

More data cleaning questions