FULL OUTER JOIN behavior in SQL - Step-by-Step Execution

Concept Flow - FULL OUTER JOIN behavior

Start with Table A and Table B

↓

Match rows on join condition

↓

Combine matched rows into result

↓

Add unmatched rows from Table A with NULLs for B

↓

Add unmatched rows from Table B with NULLs for A

↓

Final Result Set

FULL OUTER JOIN returns all rows from both tables, matching rows where possible, and fills NULLs where no match exists.

Execution Sample

SQL

SELECT A.id, A.value, B.id, B.value
FROM A
FULL OUTER JOIN B ON A.id = B.id;

This query joins tables A and B on id, returning all rows from both tables with NULLs where no match exists.

Execution Table

Step	Table A Row	Table B Row	Join Condition (A.id = B.id)	Action	Output Row
1	A.id=1, A.value='Apple'	B.id=1, B.value='Red'	True	Combine matched rows	1, 'Apple', 1, 'Red'
2	A.id=2, A.value='Banana'	B.id=2, B.value='Yellow'	True	Combine matched rows	2, 'Banana', 2, 'Yellow'
3	A.id=3, A.value='Cherry'	No matching B row	False	Add A row with NULLs for B	3, 'Cherry', NULL, NULL
4	No matching A row	B.id=4, B.value='Green'	False	Add B row with NULLs for A	NULL, NULL, 4, 'Green'
5	No more rows	No more rows	N/A	End of join	Result complete

💡 All rows from both tables processed; unmatched rows included with NULLs.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Output Rows Count	0	1	2	3	4	4

Key Moments - 2 Insights

Why do some columns have NULL values in the output?

Because FULL OUTER JOIN includes unmatched rows from both tables, it fills missing side columns with NULLs as shown in steps 3 and 4 of the execution_table.

Does FULL OUTER JOIN only return rows where the join condition is true?

No, it returns all rows from both tables. Matched rows are combined (steps 1 and 2), but unmatched rows from either table are also included with NULLs (steps 3 and 4).

Visual Quiz - 3 Questions

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

A NULL, NULL, 3, 'Cherry'

B 3, 'Cherry', NULL, NULL

C 3, 'Cherry', 3, 'Cherry'

D NULL, NULL, NULL, NULL

💡 Hint

Check the 'Output Row' column for step 3 in the execution_table.

At which step does the join condition evaluate to False because there is no matching row in Table B?

A Step 1

B Step 4

C Step 3

D Step 2

💡 Hint

Look at the 'Join Condition' column in the execution_table for where Table B row is missing.

If Table B had an extra row with id=5, how would the output rows count change after step 4?

A It would increase by 1

B It would stay the same

C It would decrease by 1

D It would double

💡 Hint

Refer to variable_tracker for how output rows count increases with unmatched rows from Table B.

Concept Snapshot

FULL OUTER JOIN syntax:
SELECT columns
FROM A
FULL OUTER JOIN B ON A.key = B.key;

Returns all rows from A and B.
Matches rows where keys equal.
Unmatched rows show NULLs for missing side.

Full Transcript

FULL OUTER JOIN returns all rows from both tables involved in the join. It matches rows where the join condition is true and combines their columns. For rows in one table without a match in the other, it includes them with NULL values for the missing columns. This ensures no data is lost from either table. The execution steps show matching rows combined first, then unmatched rows from each table added with NULLs. The output row count increases with each added row. This join is useful when you want a complete view of both tables, including unmatched data.