LEFT JOIN with NULL result rows in SQL - Step-by-Step Execution

Concept Flow - LEFT JOIN with NULL result rows

Start with LEFT table

↓

For each row in LEFT table

↓

Find matching rows in RIGHT table

↓

Match?

↓

Fill RIGHT columns with NULL

↓

Combine LEFT row with RIGHT row or NULLs

↓

Add combined row to result

↓

Repeat for all LEFT rows

↓

END

The LEFT JOIN takes each row from the left table and tries to find matching rows in the right table. If no match is found, it fills the right side columns with NULL and still includes the left row in the result.

Execution Sample

SQL

SELECT A.id, A.name, B.order_id
FROM Customers A
LEFT JOIN Orders B ON A.id = B.customer_id;

This query lists all customers and their orders. Customers without orders show NULL in the order_id column.

Execution Table

Step	Current LEFT row (Customers)	Matching RIGHT rows (Orders)	Action	Result row added
1	Customer(id=1, name='Alice')	Order(order_id=101, customer_id=1)	Match found, combine rows	(1, 'Alice', 101)
2	Customer(id=2, name='Bob')	No matching order	No match, fill RIGHT columns with NULL	(2, 'Bob', NULL)
3	Customer(id=3, name='Charlie')	Order(order_id=102, customer_id=3)	Match found, combine rows	(3, 'Charlie', 102)
4	Customer(id=4, name='Diana')	No matching order	No match, fill RIGHT columns with NULL	(4, 'Diana', NULL)
END	-	-	-	All LEFT rows processed

💡 All rows from Customers processed; unmatched rows have NULLs in Orders columns

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Current LEFT row	None	Customer 1	Customer 2	Customer 3	Customer 4	None
Matching RIGHT rows	None	Order 101	None	Order 102	None	None
Result rows count	0	1	2	3	4	4

Key Moments - 2 Insights

Why do some rows have NULL values in the RIGHT table columns?

Because LEFT JOIN includes all rows from the LEFT table even if there is no matching row in the RIGHT table. When no match is found (see steps 2 and 4 in execution_table), the RIGHT columns are filled with NULL.

Does LEFT JOIN exclude any rows from the LEFT table?

No, LEFT JOIN always includes every row from the LEFT table regardless of matches in the RIGHT table, as shown by all LEFT rows appearing in the result (execution_table rows 1 to 4).

Visual Quiz - 3 Questions

Look at the execution_table, what is the result row added at step 2?

A (2, 'Bob', 102)

B (2, 'Bob', NULL)

C (2, 'Bob', 101)

D (NULL, 'Bob', NULL)

💡 Hint

Check the 'Result row added' column at step 2 in the execution_table

At which step does the LEFT JOIN fill RIGHT columns with NULL because no match was found?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Action' column in execution_table for steps with 'No match, fill RIGHT columns with NULL'

If a new customer with id=5 and no orders is added, how will the result rows count change?

A It will decrease by 1

B It will stay the same

C It will increase by 1

D It will double

💡 Hint

Refer to variable_tracker row 'Result rows count' and how LEFT JOIN includes all LEFT rows

Concept Snapshot

LEFT JOIN syntax:
SELECT columns
FROM LeftTable
LEFT JOIN RightTable ON condition;

Behavior:
- Includes all rows from LeftTable
- Matches rows from RightTable
- If no match, RightTable columns are NULL

Key rule: No LEFT row is dropped, unmatched RIGHT columns become NULL.

Full Transcript

This visual execution shows how a LEFT JOIN works in SQL. We start with each row from the left table (Customers). For each customer, the database looks for matching rows in the right table (Orders) based on the join condition (customer id). If a match is found, the rows are combined and added to the result. If no match is found, the left row is still included, but the right side columns are filled with NULL. This ensures all customers appear in the result, even those without orders. The execution table traces each step, showing which rows match and when NULLs appear. The variable tracker follows the current row and result count. Key moments clarify why NULLs appear and that no left rows are excluded. The quiz tests understanding of these steps. The snapshot summarizes the syntax and behavior for quick reference.