LEFT JOIN execution behavior in SQL - Deep Dive

Overview - LEFT JOIN execution behavior

What is it?

A LEFT JOIN is a way to combine rows from two tables in a database. It returns all rows from the first table, and the matching rows from the second table. If there is no match, the result still includes the first table's row, but with NULL values for the second table's columns. This helps keep all data from the first table while adding related information from the second.

Why it matters

Without LEFT JOIN, you might lose important data when combining tables because only matching rows would appear. LEFT JOIN ensures you keep all records from the main table, even if related data is missing. This is crucial for reports, data analysis, and applications that need complete information from one source with optional details from another.

Where it fits

Before learning LEFT JOIN, you should understand basic SELECT queries and simple JOINs like INNER JOIN. After mastering LEFT JOIN, you can explore more complex joins like RIGHT JOIN, FULL OUTER JOIN, and advanced query optimization techniques.

Mental Model

Core Idea

LEFT JOIN returns all rows from the first table and matches rows from the second table, filling with NULL values when no match exists.

Think of it like...

Imagine you have a guest list for a party (first table) and a list of gifts they brought (second table). A LEFT JOIN is like showing everyone invited, and next to each name, showing the gift they brought if any. If someone didn’t bring a gift, their name still appears but with an empty gift slot.

┌───────────────┐     ┌───────────────┐
│   Table A     │     │   Table B     │
│ (All guests)  │     │ (Gifts list)  │
└──────┬────────┘     └──────┬────────┘
       │                     │
       │ LEFT JOIN           │
       │                     │
┌──────▼─────────────────────▼───────┐
│ Result: All guests + their gifts    │
│ If no gift, gift columns are NULL   │
└─────────────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic table joins

Concept: Introduce the idea of combining rows from two tables based on a related column.

In databases, tables often relate by a common column, like an ID. Joining tables means pairing rows where these columns match. The simplest join is INNER JOIN, which only shows rows with matches in both tables.

Result

You get rows where both tables have matching data.

Understanding how tables relate is the foundation for all join types, including LEFT JOIN.

2

Foundation

What is a LEFT JOIN?

Concept: LEFT JOIN returns all rows from the first (left) table, and matching rows from the second (right) table.

When you write a LEFT JOIN, the database keeps every row from the left table. For each row, it looks for matching rows in the right table. If it finds matches, it combines them. If not, it fills the right table's columns with NULLs.

Result

All rows from the left table appear, with matching data or NULLs from the right table.

LEFT JOIN ensures no data from the main table is lost, even if related data is missing.

3

Intermediate

How LEFT JOIN handles no matches

🤔Before reading on: do you think LEFT JOIN excludes rows without matches or includes them with empty values? Commit to your answer.

Concept: LEFT JOIN includes rows without matches by filling missing columns with NULL.

If a row in the left table has no matching row in the right table, LEFT JOIN still returns that row. The columns from the right table are filled with NULL to show no data was found.

Result

Rows without matches appear with NULLs in right table columns.

Knowing how NULLs represent missing matches helps you interpret query results correctly.

4

Intermediate

Execution order of LEFT JOIN

🤔Before reading on: do you think the database processes the left or right table first in a LEFT JOIN? Commit to your answer.

Concept: The database starts with the left table and then looks for matches in the right table.

When executing a LEFT JOIN, the database scans each row of the left table. For each row, it searches the right table for matching rows. This order ensures all left rows appear in the result.

Result

The result set contains all left table rows, matched or unmatched.

Understanding execution order clarifies why LEFT JOIN preserves all left table rows.

5

Intermediate

LEFT JOIN with multiple matches

🤔Before reading on: if a left table row matches multiple right table rows, how many result rows appear? Commit to your answer.

Concept: LEFT JOIN returns one result row for each matching right table row, duplicating left rows as needed.

If a row in the left table matches several rows in the right table, the LEFT JOIN creates multiple rows in the result—one for each match. This can increase the number of rows returned.

Result

Multiple result rows for one left table row if multiple matches exist.

Knowing this helps prevent surprises when counting rows or aggregating data after a LEFT JOIN.

6

Advanced

LEFT JOIN with filtering on right table

🤔Before reading on: does adding a WHERE condition on the right table columns after a LEFT JOIN remove unmatched rows? Commit to your answer.

Concept: Filtering on right table columns in WHERE can unintentionally exclude unmatched rows, changing LEFT JOIN behavior.

When you add a WHERE clause that filters right table columns, rows with NULLs (unmatched) may be excluded. To keep unmatched rows, filters on right table columns should be in the JOIN condition (ON clause) instead.

Result

Incorrect filtering can turn LEFT JOIN into INNER JOIN behavior.

Understanding filter placement prevents losing unmatched rows unintentionally.

7

Expert

LEFT JOIN execution optimization surprises

🤔Before reading on: do you think databases always scan the entire right table for each left row in a LEFT JOIN? Commit to your answer.

Concept: Databases use indexes and query plans to optimize LEFT JOIN, avoiding full scans and improving performance.

Modern databases analyze the query and use indexes on join columns to quickly find matching rows. They may use hash joins, merge joins, or nested loops depending on data size and indexes. This means LEFT JOIN can be very efficient even on large tables.

Result

LEFT JOIN queries can run fast with proper indexing and query plans.

Knowing how databases optimize LEFT JOIN helps write performant queries and troubleshoot slow joins.

Under the Hood

Internally, the database engine processes a LEFT JOIN by iterating over each row in the left table. For each row, it searches the right table for matching rows using indexes or scans. If matches are found, it combines the rows; if not, it creates a result row with NULLs for the right table columns. The engine builds the final result set by collecting all these combined rows.

Why designed this way?

LEFT JOIN was designed to preserve all data from the primary table while optionally adding related data. This design solves the problem of losing important rows when no matching data exists in the second table. Alternatives like INNER JOIN exclude unmatched rows, which is not always desirable. LEFT JOIN balances completeness with relational matching.

┌───────────────┐
│   Left Table  │
│  (All rows)   │
└──────┬────────┘
       │ iterate each row
       ▼
┌─────────────────────┐
│ Search Right Table   │
│ for matching rows    │
└──────┬──────────────┘
       │
   ┌───┴────┐
   │        │
┌──▼──┐  ┌──▼──┐
│Match│  │No   │
│Rows │  │Match│
└──┬──┘  └──┬──┘
   │        │
┌──▼──┐  ┌──▼─────────────┐
│Combine│ │Left row + NULLs│
│Rows   │ │for right table │
└───────┘ └────────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Does a LEFT JOIN always return fewer rows than the left table? Commit to yes or no.

Common Belief: LEFT JOIN returns fewer or equal rows compared to the left table because it only adds matching rows.

Reality: LEFT JOIN can return more rows than the left table if multiple matches exist in the right table, duplicating left rows.

Why it matters: Assuming fewer rows can cause wrong assumptions about data size and lead to errors in counting or aggregation.

Quick: Does filtering right table columns in WHERE keep unmatched rows in LEFT JOIN? Commit to yes or no.

Common Belief: Filtering on right table columns in WHERE after a LEFT JOIN keeps all left rows, including unmatched ones.

Reality: Filtering right table columns in WHERE removes unmatched rows because NULLs fail the filter, effectively turning LEFT JOIN into INNER JOIN.

Why it matters: This mistake causes loss of unmatched rows, breaking the purpose of LEFT JOIN and leading to incomplete results.

Quick: Does the database always scan the entire right table for each left row in a LEFT JOIN? Commit to yes or no.

Common Belief: The database scans the entire right table for every left table row during a LEFT JOIN.

Reality: Databases use indexes and optimized join algorithms to avoid full scans, improving performance significantly.

Why it matters: Believing in full scans may discourage writing LEFT JOIN queries or indexing properly, hurting performance.

Expert Zone

1

LEFT JOIN results can be affected by NULL values in join columns, which may cause unexpected matches or misses.

2

Placing filters on the right table in the ON clause preserves unmatched rows, while placing them in WHERE filters them out.

3

Query planners may rewrite LEFT JOINs internally for optimization, sometimes changing execution order without affecting results.

When NOT to use

LEFT JOIN is not suitable when you only want rows with matching data in both tables; use INNER JOIN instead. For including unmatched rows from both tables, use FULL OUTER JOIN. When performance is critical and unmatched rows are irrelevant, INNER JOIN is preferable.

Production Patterns

LEFT JOIN is commonly used in reporting to include all main records with optional related details, in data warehousing for slowly changing dimensions, and in web applications to show users with or without related data like orders or profiles.

Connections

INNER JOIN

LEFT JOIN builds on INNER JOIN by including unmatched left rows, while INNER JOIN only returns matched rows.

Understanding INNER JOIN helps grasp what LEFT JOIN adds: the preservation of all left table rows.

Null Handling in SQL

LEFT JOIN uses NULL to represent missing matches from the right table.

Knowing how NULL works in SQL is essential to correctly interpret LEFT JOIN results and write accurate filters.

Set Theory

LEFT JOIN corresponds to a left outer join operation in set theory, combining sets with preservation of all elements from the first set.

Recognizing LEFT JOIN as a set operation clarifies its behavior and helps in reasoning about query results.

Common Pitfalls

#1 Filtering right table columns in WHERE removes unmatched rows.

Wrong approach:

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id WHERE B.value = 'X';

Correct approach:

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id AND B.value = 'X';

Root cause:

Placing filters on the right table in WHERE applies after the join, excluding rows where right table columns are NULL.

#2 Assuming LEFT JOIN never increases row count beyond left table.

Wrong approach:

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id; -- expecting row count <= A

Correct approach:

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id; -- understand row count can be > A if multiple matches

Root cause:

Not realizing that multiple matches in the right table cause row duplication.

#3 Not indexing join columns causing slow LEFT JOIN queries.

Wrong approach:

SELECT * FROM large_table_A LEFT JOIN large_table_B ON A.key = B.key; -- no indexes

Correct approach:

CREATE INDEX idx_B_key ON large_table_B(key);
SELECT * FROM large_table_A LEFT JOIN large_table_B ON A.key = B.key;

Root cause:

Ignoring the importance of indexes for join performance.

Key Takeaways

LEFT JOIN returns all rows from the left table and matches from the right, filling with NULLs when no match exists.

Filtering on right table columns must be done carefully to avoid losing unmatched rows; use ON clause filters for this.

LEFT JOIN can increase the number of rows if multiple matches exist in the right table, duplicating left rows.

Databases optimize LEFT JOIN execution using indexes and join algorithms, so proper indexing is crucial for performance.

Understanding LEFT JOIN behavior is essential for accurate data retrieval, reporting, and avoiding common query mistakes.