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.

Practice

1. What does a LEFT JOIN do in SQL?

easy

A. Returns only rows that have matching values in both tables

B. Returns all rows from the left table and matching rows from the right table, NULL if no match

C. Returns all rows from the right table and matching rows from the left table

D. Returns rows only from the left table without any matching

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens when no match exists

   If no matching row exists in the right table, the result shows NULL for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, NULL if no match -> Option B

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

Hint: LEFT JOIN keeps all left rows, fills right with NULL if no match [OK]

Common Mistakes:

• Confusing LEFT JOIN with INNER JOIN
• Thinking LEFT JOIN returns only matching rows
• Assuming NULLs never appear in results

2. Which of the following is the correct syntax for a LEFT JOIN in SQL?

easy

A. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id;

B. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

D. SELECT * FROM table1 LEFT JOIN table2 USING id;

Solution

1. Step 1: Recall correct LEFT JOIN syntax

   The correct syntax uses LEFT JOIN followed by ON clause to specify join condition.

2. Step 2: Check each option

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; uses correct syntax: LEFT JOIN with ON condition. SELECT * FROM table1 JOIN LEFT table2 ON table1.id = table2.id; has incorrect order. SELECT * FROM table1 LEFT JOIN table2 WHERE table1.id = table2.id; uses WHERE instead of ON. SELECT * FROM table1 LEFT JOIN table2 USING id; uses USING without parentheses, which is invalid syntax.

3. Final Answer:

   SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id; -> Option C

4. Quick Check:

   LEFT JOIN ... ON condition is standard syntax [OK]

Hint: Use LEFT JOIN ... ON condition for correct syntax [OK]

Common Mistakes:

• Using WHERE instead of ON for join condition
• Swapping JOIN and LEFT keywords
• Confusing USING with ON without proper column names

3. Given these tables:
Employees(id, name)
Departments(id, dept_name, manager_id)
What will this query return?

SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.id = d.manager_id;

medium

A. All employees with their department names if they are managers, NULL otherwise

B. Only employees who are managers with their department names

C. All departments with their managers' names

D. Only departments with managers matching employee ids

Solution

1. Step 1: Analyze LEFT JOIN condition

   The query LEFT JOINs Employees (left) with Departments (right) on employee id matching department manager_id.

2. Step 2: Understand output rows

   All employees appear. If an employee is a manager (id matches manager_id), department name shows; else department columns are NULL.

3. Final Answer:

   All employees with their department names if they are managers, NULL otherwise -> Option A

4. Quick Check:

   LEFT JOIN keeps all employees, adds department if manager [OK]

Hint: LEFT JOIN keeps all left rows, adds right data if matched [OK]

Common Mistakes:

• Thinking only managers appear in result
• Confusing which table is left or right
• Expecting departments without managers to appear

4. Identify the error in this SQL query:

SELECT a.id, b.value FROM A a LEFT JOIN B b ON a.id = b.id WHERE b.value > 10;

medium

A. The ON clause is missing a join condition

B. The SELECT clause must include all columns from both tables

C. LEFT JOIN should be INNER JOIN for this query

D. The WHERE clause filters out rows where b.value is NULL, negating LEFT JOIN effect

Solution

1. Step 1: Understand LEFT JOIN with WHERE filter

   The WHERE clause filters rows after join. Filtering on b.value > 10 excludes rows where b.value is NULL.

2. Step 2: Effect on LEFT JOIN

   This filtering removes rows without matches in B, making LEFT JOIN behave like INNER JOIN.

3. Final Answer:

   The WHERE clause filters out rows where b.value is NULL, negating LEFT JOIN effect -> Option D

4. Quick Check:

   Filtering on right table in WHERE breaks LEFT JOIN [OK]

Hint: Use ON for right table filters, not WHERE, to keep LEFT JOIN effect [OK]

Common Mistakes:

• Filtering right table columns in WHERE after LEFT JOIN
• Confusing ON and WHERE clauses
• Assuming LEFT JOIN always keeps all left rows regardless of WHERE

5. You want to list all customers and their last order date if any. Which query correctly uses LEFT JOIN to achieve this?

Customers(id, name)
Orders(id, customer_id, order_date)

hard

A. SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

B. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders);

C. SELECT c.name, o.order_date FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id;

D. SELECT c.name, MAX(o.order_date) FROM Customers c INNER JOIN Orders o ON c.id = o.customer_id GROUP BY c.name;

Solution

1. Step 1: Understand requirement for all customers

   We want all customers listed, even those without orders, so LEFT JOIN is needed.

2. Step 2: Aggregate last order date per customer

   Using MAX(o.order_date) with GROUP BY c.name gives last order date or NULL if no orders.

3. Step 3: Check options

   SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; uses LEFT JOIN and GROUP BY correctly. SELECT c.name, o.order_date FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.order_date = (SELECT MAX(order_date) FROM Orders); filters in WHERE, excluding customers without orders. Options A and C use INNER JOIN, excluding customers without orders.

4. Final Answer:

   SELECT c.name, MAX(o.order_date) FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id GROUP BY c.name; -> Option A

5. Quick Check:

   LEFT JOIN + GROUP BY + MAX gets last order date including customers without orders [OK]

Hint: Use LEFT JOIN with GROUP BY and MAX to include all left rows [OK]

Common Mistakes:

• Using INNER JOIN excludes customers without orders
• Filtering right table in WHERE removes unmatched rows
• Not grouping when using aggregate functions