Mastering LEFT JOIN: Common Pitfalls and Practical Solutions

Introduction

LEFT JOIN is a frequently used operation in SQL for combining rows from two tables while preserving all rows from the left (primary) table. The article presents the basic principle, typical pitfalls, and practical guidance, with examples tested on MySQL and Hive.

Basic LEFT JOIN Behavior

When performing a LEFT JOIN, rows from the left table are always returned; if no matching row exists in the right table, the right‑hand columns are filled with NULL. This behavior is illustrated with three relationship types:

1. One‑to‑One

Tables t_name (alias a) and t_age (alias b) are joined on id. The left table has 6 rows, the right table 3 rows, so the result contains 6 rows, with b fields null for the three unmatched rows.

2. One‑to‑Many

Using t_age as the left table and joining on dt, three rows with dt=20190905 match three rows in the right table, producing three result rows for that date. Additional dates generate more rows, demonstrating the expansion of result rows beyond the left‑table count.

3. Many‑to‑Many

When joining on dt with both tables containing multiple rows per date, the result can include additional rows (e.g., a row for 20190907) while still filling unmatched right‑hand fields with NULL.

Execution Principle of LEFT JOIN

MySQL implements LEFT JOIN using a nested‑loop algorithm. The logical flow can be expressed as:

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)

where P1 represents the ON filter (default TRUE) and P2 represents the WHERE filter (default TRUE). The pseudo‑code below shows how rows are processed:

FOR each row lt in LT { // traverse left table
  BOOL b = FALSE;
  FOR each row rt in RT such that P1(lt, rt) { // match ON condition
    IF P2(lt, rt) { // apply WHERE condition
      t := lt || rt; // output combined row
    }
    b = TRUE; // a matching right row exists
  }
  IF (!b) { // no match found
    IF P2(lt, NULL) { // apply WHERE to NULL‑filled row
      t := lt || NULL; // output left row with NULLs
    }
  }
}

Key conclusions:

If a condition that restricts the right table is placed in ON, rows without a match are still kept because the left table row is output with NULL values.

If the same condition is placed in WHERE, rows where the right side is NULL fail the filter and are removed, breaking the LEFT JOIN semantics.

Practical Examples

Three illustrative scenarios are provided:

Only one ON condition : The result follows the basic LEFT JOIN rule, keeping all left rows.

Two ON conditions (e.g., b.age=24 ) : Adding a filter in ON still preserves left rows; unmatched right fields become NULL.

WHERE clause usage : Moving the filter to WHERE eliminates rows where the right side is NULL, resulting in fewer rows than the left table.

Case Study: User Retention Analysis

A real‑world problem is presented: given a user activity table t_active, compute how many users active on a reference date remain active after 0, 1, 2, … days. The solution uses a self‑join with LEFT JOIN, calculating datediff between dates. Initial attempts produced NULL values for datediff because NULL dates from unmatched rows propagated through the calculation.

To fix the issue, a CASE WHEN expression replaces NULL dates with a concrete date, ensuring datediff yields valid numbers and the final counts match expectations.

Summary

The article clarifies LEFT JOIN mechanics, emphasizes the difference between placing filters in ON versus WHERE, discusses handling of NULL values, and demonstrates how to apply these concepts to real data‑analysis tasks such as retention calculations. Readers are encouraged to experiment with the provided MySQL/Hive scripts.