LEFT JOIN execution behavior in SQL - Step-by-Step Execution

Concept Flow - LEFT JOIN execution behavior

Start with Left Table

↓

For each row in Left Table

↓

Find matching rows in Right Table

Yes No↓

Combine rows

↓

Add combined row to result

↓

Repeat for next Left row

↓

End

The LEFT JOIN goes through each row in the left table, tries to find matching rows in the right table, and combines them. If no match is found, it fills the right side with NULLs and still includes the left row.

Execution Sample

SQL

SELECT A.id, A.name, B.score
FROM Students A
LEFT JOIN Scores B ON A.id = B.student_id;

This query lists all students and their scores if available; if no score exists, it shows NULL.

Execution Table

Step	Left Table Row	Matching Right Rows Found?	Action	Output Row
1	id=1, name='Alice'	Yes (score=85)	Combine left and right rows	1, 'Alice', 85
2	id=2, name='Bob'	No	Combine left row with NULLs for right	2, 'Bob', NULL
3	id=3, name='Charlie'	Yes (score=92)	Combine left and right rows	3, 'Charlie', 92
4	id=4, name='Diana'	No	Combine left row with NULLs for right	4, 'Diana', NULL
5	No more rows	N/A	Stop execution	End of result set

💡 All rows from left table processed; query ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
Current Left Row	None	id=1, name='Alice'	id=2, name='Bob'	id=3, name='Charlie'	id=4, name='Diana'	None
Matching Right Rows	None	score=85	None	score=92	None	None
Output Rows Count	0	1	2	3	4	4

Key Moments - 2 Insights

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

When no matching row is found in the right table for a left table row, the right side columns are filled with NULLs to keep the left row in the result (see execution_table rows 2 and 4).

Does LEFT JOIN exclude any rows from the left table?

No, LEFT JOIN always includes all rows from the left table regardless of matches in the right table, as shown by all left rows appearing in the output (execution_table all steps).

Visual Quiz - 3 Questions

Look at the execution table, what is the output row at step 2?

A 2, 'Bob', 85

B NULL, NULL, NULL

C 2, 'Bob', NULL

D 2, 'Bob', 92

💡 Hint

Check the 'Output Row' column at step 2 in the execution_table.

At which step does the LEFT JOIN find no matching right table row for the left row?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look at the 'Matching Right Rows Found?' column in the execution_table.

If the right table had a matching row for 'Diana', how would the output change at step 4?

A Output row would include Diana's score instead of NULL

B Output row would be removed

C Output row would have NULLs on both sides

D No change, still NULL for right columns

💡 Hint

Refer to how matched rows are combined in execution_table steps 1 and 3.

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 condition met
- If no match, RightTable columns are NULL

Use to keep all left rows with optional right data.

Full Transcript

The LEFT JOIN starts by taking each row from the left table. For each left row, it looks for matching rows in the right table based on the join condition. If it finds matches, it combines the left and right rows into one output row. If no match is found, it still outputs the left row but fills the right side columns with NULL values. This process repeats for every row in the left table until all are processed. The result is a table that always includes all rows from the left table, with matching data from the right table when available, or NULLs otherwise.