FULL OUTER JOIN availability across databases in SQL - Step-by-Step Execution

Concept Flow - FULL OUTER JOIN availability across databases

Start with two tables

↓

Check matching rows

↓

Include matched rows

↓

Include unmatched rows from left

↓

Include unmatched rows from right

↓

Combine all rows

↓

Result: FULL OUTER JOIN

FULL OUTER JOIN combines all rows from two tables, matching where possible and including unmatched rows from both sides.

Execution Sample

SQL

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

This query returns all rows from tables A and B, matching rows where ids are equal, and including unmatched rows from both tables.

Execution Table

Step	Action	Table A Row	Table B Row	Result Row Included	Reason
1	Check A.id=1 with B.id=1	A.id=1	B.id=1	Yes	Match found, include combined row
2	Check A.id=2 with B.id=2	A.id=2	B.id=2	Yes	Match found, include combined row
3	Check A.id=3 with B.id=3	A.id=3	No matching B row	Yes	No match in B, include A row with NULLs for B
4	Check B.id=4 with A.id=4	No matching A row	B.id=4	Yes	No match in A, include B row with NULLs for A
5	No more rows	N/A	N/A	No	All rows processed, stop

💡 All rows from both tables checked and included if matched or unmatched.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Result Rows Included	0	1	2	3	4	4

Key Moments - 2 Insights

Why do unmatched rows from both tables appear in the result?

Because FULL OUTER JOIN includes all rows from both tables, matched or not, as shown in execution_table rows 3 and 4.

What happens if a database does not support FULL OUTER JOIN?

You cannot directly use FULL OUTER JOIN syntax; you must simulate it using UNION of LEFT JOIN and RIGHT JOIN, unlike the direct inclusion shown in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows are included in the result after step 3?

A 4

B 2

C 3

D 1

💡 Hint

Check the 'Result Row Included' column up to step 3.

At which step does the join include a row from table B that has no matching row in table A?

A Step 2

B Step 4

C Step 3

D Step 1

💡 Hint

Look for a step where 'Table A Row' is 'No matching A row' and 'Table B Row' has a value.

If a database does not support FULL OUTER JOIN, which approach can simulate it?

A Use UNION of LEFT JOIN and RIGHT JOIN

B Use CROSS JOIN

C Use INNER JOIN only

D Use only LEFT JOIN

💡 Hint

Refer to key_moments explanation about simulating FULL OUTER JOIN.

Concept Snapshot

FULL OUTER JOIN returns all rows from both tables.
Matched rows are combined.
Unmatched rows from either table appear with NULLs.
Not all databases support it directly.
Simulate with LEFT JOIN UNION RIGHT JOIN if needed.

Full Transcript

FULL OUTER JOIN combines two tables by including all rows from both, matching rows where possible and including unmatched rows from either side with NULLs for missing data. The example query selects all rows from tables A and B joined on id. The execution table shows step-by-step how matched and unmatched rows are included. Some databases do not support FULL OUTER JOIN syntax directly; in those cases, you can simulate it by combining LEFT JOIN and RIGHT JOIN results with UNION. This ensures you get the full set of rows from both tables, just like FULL OUTER JOIN would provide.