Why outer joins are needed in SQL - Visual Breakdown

Concept Flow - Why outer joins are needed

Start with two tables

↓

Perform INNER JOIN

↓

Check matching rows

Yes↓

Include matched rows

↓

Check unmatched rows

No↓

Exclude unmatched rows

↓

Result

↓

Outer Join

↓

Include matched rows

↓

Include unmatched rows with NULLs

↓

Result with all rows from one or both tables

This flow shows how inner joins only keep matched rows, while outer joins keep unmatched rows too by filling with NULLs.

Execution Sample

SQL

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

This query returns all rows from table A, matching rows from B, and NULLs where no match exists.

Execution Table

Step	Action	Table A Row	Table B Row	Join Condition	Result Row
1	Check A.id=1 with B.id=1	A(1)	B(1)	1=1 True	(1, 'B1')
2	Check A.id=2 with B.id=2	A(2)	B(2)	2=2 True	(2, 'B2')
3	Check A.id=3 with B.id=3	A(3)	No B row	No match	(3, NULL)
4	No more rows in A	End	End	End	Query ends

💡 All rows from A processed; unmatched rows from A included with NULLs for B columns

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current A row	None	A(1)	A(2)	A(3)	End
Current B row	None	B(1)	B(2)	None	End
Result rows	Empty	(1, 'B1')	(1, 'B1'), (2, 'B2')	(1, 'B1'), (2, 'B2'), (3, NULL)	(1, 'B1'), (2, 'B2'), (3, NULL)

Key Moments - 2 Insights

Why does the third row from table A appear with NULLs in the result?

Because there is no matching row in table B for A.id=3, the LEFT JOIN includes it with NULLs for B columns, as shown in execution_table row 3.

Why wouldn't an INNER JOIN include the unmatched row from table A?

INNER JOIN only includes rows where the join condition is true. Since A.id=3 has no match in B, it is excluded, unlike the LEFT JOIN shown here.

Visual Quiz - 3 Questions

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

A (3, 'B3')

B (2, NULL)

C (2, 'B2')

D (1, 'B1')

💡 Hint

Check the 'Result Row' column in execution_table at step 2

At which step does the join condition fail to find a matching row?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Join Condition' column in execution_table where it says 'No match'

If we changed the join to INNER JOIN, what would happen to the result rows?

A All rows from A would appear with NULLs for unmatched B rows

B Only rows with matching B rows would appear

C All rows from B would appear with NULLs for unmatched A rows

D No rows would appear

💡 Hint

Recall that INNER JOIN excludes unmatched rows, unlike LEFT JOIN shown in variable_tracker

Concept Snapshot

Outer joins include unmatched rows from one or both tables by filling missing columns with NULLs.
Syntax example: SELECT * FROM A LEFT JOIN B ON A.id = B.id;
Use outer joins when you want to keep all rows from a table even if no match exists.
Inner joins exclude unmatched rows, showing only matches.
Outer joins help answer questions like 'show me all customers, even those without orders.'

Full Transcript

This visual execution shows why outer joins are needed in SQL. When joining two tables, an inner join only keeps rows where the join condition matches in both tables. Rows without matches are excluded. Outer joins, like LEFT JOIN, keep all rows from one table and fill unmatched columns from the other table with NULLs. The example query selects all rows from table A and matches rows from table B by id. The execution table traces each row from A checked against B. For A.id=3, no matching B row exists, so the result includes (3, NULL). This shows how outer joins preserve unmatched rows, which inner joins do not. This is useful when you want to see all data from one table regardless of matches in the other.