Context

In reporting at Meta, the join type you choose can change business conclusions. For example, a report on Facebook Pages, Instagram creators, or ad accounts may either include entities with no activity or exclude them entirely depending on the join.

Core question

Explain how LEFT JOIN and INNER JOIN differ in a reporting context. In your answer, discuss:

1. What rows each join returns
2. How unmatched records affect counts, sums, and conversion-style metrics
3. When a business analyst should prefer LEFT JOIN over INNER JOIN
4. A simple reporting example where using the wrong join would produce a misleading result

Scope guidance

The interviewer is looking for a practical analytics explanation, not just syntax definitions. Focus on how join choice changes report completeness, NULL handling, and interpretation of KPIs in recurring business reporting.

INNER JOIN returns only matched rows

An INNER JOIN keeps only rows where the join condition matches in both tables. In reporting, this means entities with no related activity disappear from the result set, which can undercount the true population.

SELECT p.page_id, a.spend
FROM pages p
INNER JOIN ad_spend a
  ON p.page_id = a.page_id;

LEFT JOIN preserves the full reporting base

A LEFT JOIN returns all rows from the left table and matching rows from the right table. This is useful when the left table defines the reporting universe, such as all Pages, all advertisers, or all creators, including those with zero activity.

SELECT p.page_id, a.spend
FROM pages p
LEFT JOIN ad_spend a
  ON p.page_id = a.page_id;

NULLs represent missing matches

With a LEFT JOIN, unmatched rows from the right table appear as NULL. In reports, analysts often convert these to zeros with COALESCE, but they should understand that NULL means no matching record existed, not necessarily that the metric was explicitly recorded as zero.

SELECT p.page_id, COALESCE(a.spend, 0) AS spend
FROM pages p
LEFT JOIN ad_spend a
  ON p.page_id = a.page_id;

Join choice changes denominators

In reporting, the denominator often matters as much as the numerator. INNER JOIN can shrink the denominator by excluding inactive entities, while LEFT JOIN preserves it, which changes rates such as activation, conversion, or adoption.

SELECT COUNT(*) AS total_pages,
       COUNT(a.page_id) AS pages_with_spend
FROM pages p
LEFT JOIN ad_spend a
  ON p.page_id = a.page_id;

Aggregation behavior depends on the join

Aggregations like COUNT(*), COUNT(column), and SUM() behave differently after joins. A LEFT JOIN may keep rows with NULL values that still count in COUNT(*) but not in COUNT(right_table.column), which is often exactly what a reporting query needs.

SELECT COUNT(*) AS rows_after_join,
       COUNT(a.page_id) AS matched_rows
FROM pages p
LEFT JOIN ad_spend a
  ON p.page_id = a.page_id;