LEFT JOIN preserving all left rows in SQL - Step-by-Step Execution

Concept Flow - LEFT JOIN preserving all left rows

Start with LEFT table

↓

For each row in LEFT table

↓

Find matching rows in RIGHT table

↓

Combine LEFT and RIGHT row

↓

Combine LEFT row with NULLs for RIGHT

↓

Add combined row to result

↓

Repeat for all LEFT rows

↓

Result: All LEFT rows preserved

LEFT JOIN keeps every row from the left table and adds matching rows from the right table or NULL if no match.

Execution Sample

SQL

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

This query joins tables A and B on id, keeping all rows from A and adding scores from B or NULL if no match.

Execution Table

Step	Left Table Row	Right Table Matching Rows	Action	Output Row
1	A.id=1, A.name='Alice'	B.id=1, B.score=90	Match found, combine rows	1, 'Alice', 90
2	A.id=2, A.name='Bob'	No match	No match, combine with NULLs	2, 'Bob', NULL
3	A.id=3, A.name='Carol'	B.id=3, B.score=85	Match found, combine rows	3, 'Carol', 85
4	A.id=4, A.name='Dave'	No match	No match, combine with NULLs	4, 'Dave', NULL
5	All rows processed		End of join	Result complete with all left rows

💡 All rows from left table A processed; right table B rows matched or NULLs added.

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='Carol'	id=4, name='Dave'	All processed
Matching Right Rows	None	id=1, score=90	None	id=3, score=85	None	N/A
Output Rows Count	0	1	2	3	4	4

Key Moments - 2 Insights

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

When no matching row is found in the right table for a left row (see execution_table rows 2 and 4), LEFT JOIN fills right table columns with NULL to preserve the left row.

Does LEFT JOIN remove any rows from the left table?

No, LEFT JOIN always keeps all rows from the left table, as shown by the output rows count increasing each step and all left rows appearing in the output.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the output row?

A 2, 'Bob', NULL

B 2, 'Bob', 90

C NULL, 'Bob', NULL

D 2, NULL, NULL

💡 Hint

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

At which step does the LEFT JOIN add a row with NULLs for the right table columns?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Look for 'No match, combine with NULLs' in the 'Action' column of execution_table.

If table B had a matching row for A.id=2, how would the output rows count change after step 2?

A It would increase

B It would decrease

C It would stay the same

D It would be zero

💡 Hint

Output rows count increases by one each left row processed regardless of match, see variable_tracker.

Concept Snapshot

LEFT JOIN syntax:
SELECT columns FROM left_table
LEFT JOIN right_table ON condition;

Behavior:
- Keeps all rows from left_table
- Adds matching right_table rows
- Uses NULLs if no match

Key rule: No left rows are lost.

Full Transcript

LEFT JOIN is a way to combine two tables in SQL. It keeps every row from the left table and tries to find matching rows in the right table based on a condition. If a match is found, it combines the data from both tables. If no match is found, it still keeps the left row but fills the right table columns with NULL. This ensures no rows from the left table are lost. The example query selects id and name from table A and score from table B, joining on id. The execution steps show each left row checked, matched or not, and added to the result. This visual helps understand how LEFT JOIN preserves all left rows.