How the join engine matches rows in SQL - Visual Walkthrough

Concept Flow - How the join engine matches rows

Start with Table A row

↓

Compare join key with Table B rows

↓

Match found?

No→Move to next Table B row

|Yes↓

Output combined row

↓

More Table B rows?

Yes→Compare next Table B row

|No↓

More Table A rows?

Yes→Next Table A row

|No↓

End

The join engine takes each row from the first table and compares its join key to each row in the second table. When keys match, it outputs the combined row. It repeats this until all rows are processed.

Execution Sample

SQL

SELECT * FROM A JOIN B ON A.id = B.id;

This query joins tables A and B by matching rows where the id column is the same.

Execution Table

Step	Current A row	Current B row	Join Key A.id	Join Key B.id	Match?	Action	Output Row
1	A1 (id=1)	B1 (id=1)	1	1	Yes	Output combined row	A1+B1
2	A1 (id=1)	B2 (id=2)	1	2	No	Skip
3	A2 (id=2)	B1 (id=1)	2	1	No	Skip
4	A2 (id=2)	B2 (id=2)	2	2	Yes	Output combined row	A2+B2
5	A3 (id=3)	B1 (id=1)	3	1	No	Skip
6	A3 (id=3)	B2 (id=2)	3	2	No	Skip
7	No more A rows					End

💡 All rows from Table A have been compared with all rows from Table B.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 4	After Step 7
Current A row	A1 (id=1)	A1 (id=1)	A2 (id=2)	A3 (id=3)	No more A rows
Current B row	B1 (id=1)	B2 (id=2)	B1 (id=1)	B1 (id=1)
Output Rows	[]	[A1+B1]	[A1+B1]	[A1+B1, A2+B2]	[A1+B1, A2+B2]

Key Moments - 2 Insights

Why does the join engine compare each row of Table A with every row of Table B?

Because the join needs to find all matching pairs based on the join key. As shown in execution_table rows 1-6, each A row is checked against all B rows to find matches.

What happens when the join keys do not match?

The engine skips outputting a combined row and moves to the next B row or A row. This is shown in execution_table rows 2, 3, 5, and 6 where no output is produced.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after step 4?

A Only the combined row A1+B1

B Combined rows A1+B1 and A2+B2

C No output yet

D Combined rows A1+B2 and A2+B1

💡 Hint

Check the 'Output Rows' variable in variable_tracker after step 4.

At which step does the join engine finish processing all rows?

A Step 4

B Step 6

C Step 7

D Step 1

💡 Hint

Look at the exit_note and the last row in execution_table.

If Table B had an extra row with id=3, what would happen at step 6?

A A3 would match with B3 and output a combined row

B No change, still no output at step 6

C The join would stop early

D A1 would match with B3

💡 Hint

Consider how the join engine compares each A row with all B rows as shown in execution_table.

Concept Snapshot

JOIN engine matches rows by:
- Taking each row from Table A
- Comparing its join key with every row in Table B
- Outputting combined rows when keys match
- Repeating until all rows are processed
This is how INNER JOIN works step-by-step.

Full Transcript

The join engine works by taking one row from the first table and comparing its join key to each row in the second table. If the keys match, it outputs a combined row with data from both tables. It repeats this process for every row in the first table until all rows have been checked. This step-by-step matching ensures all matching pairs are found and combined in the result.