Troubleshooting Missing Rows with Joins

Context

At companies like Stripe or Airbnb, join choice often determines whether missing or unmatched records are visible during analysis. This becomes especially important when troubleshooting row-count drops, nulls, or unexpected gaps in reports.

Core question

Explain the difference between an INNER JOIN and a LEFT JOIN in SQL. In your answer, cover:

1. What rows each join returns
2. How unmatched rows are handled
3. When you would use each join in normal analysis
4. Why LEFT JOIN is often useful when debugging a data issue such as missing orders, failed mappings, or incomplete dimension data

Scope guidance

The interviewer expects a practical explanation, not just definitions. You should describe the result-set behavior, mention NULL handling, and give a simple troubleshooting example showing how the wrong join can hide the problem you are trying to investigate.

INNER JOIN

An INNER JOIN returns only rows where the join condition matches in both tables. If a row from either side has no match, that row is excluded from the result.

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.customer_id;

LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match on the right side, the right-table columns appear as NULL.

SELECT o.order_id, c.customer_name
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id;

Troubleshooting with Unmatched Rows

When debugging data issues, you often need to see records that failed to match. A LEFT JOIN helps surface those rows so you can identify missing foreign keys, bad IDs, or incomplete reference data.

SELECT o.order_id, o.customer_id, c.customer_name
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

NULL Interpretation

In a LEFT JOIN, NULL values in right-side columns usually indicate no match was found. That makes LEFT JOIN especially useful for audits, reconciliation, and root-cause analysis.

SELECT p.payment_id, i.invoice_id
FROM payments p
LEFT JOIN invoices i
  ON p.invoice_id = i.invoice_id;