FULL OUTER JOIN in PostgreSQL - Step-by-Step Execution

Concept Flow - FULL OUTER JOIN

Start with Table A

↓

Match rows on join condition

↓

Keep matched rows from both

↓

Add unmatched rows from A with NULLs for B

↓

Add unmatched rows from B with NULLs for A

↓

Combine all rows into result

↓

Output Result

FULL OUTER JOIN combines rows from both tables matching on a condition, including unmatched rows from both sides with NULLs where no match exists.

Execution Sample

PostgreSQL

SELECT A.id, A.name, B.id, B.city
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, matching where ids are equal, and filling NULLs where no match.

Execution Table

Step	Table A Row	Table B Row	Join Condition (A.id = B.id)	Action	Output Row
1	A: (1, 'Alice')	B: (1, 'NY')	True	Match rows	(1, 'Alice', 1, 'NY')
2	A: (2, 'Bob')	B: (2, 'LA')	True	Match rows	(2, 'Bob', 2, 'LA')
3	A: (3, 'Charlie')	B: NULL	False	No match, add A row with NULLs for B	(3, 'Charlie', NULL, NULL)
4	A: NULL	B: (4, 'Chicago')	False	No match, add B row with NULLs for A	(NULL, NULL, 4, 'Chicago')
5	End of tables			Stop	All rows combined

💡 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	[]	[(1, 'Alice', 1, 'NY')]	[(1, 'Alice', 1, 'NY'), (2, 'Bob', 2, 'LA')]	[(1, 'Alice', 1, 'NY'), (2, 'Bob', 2, 'LA'), (3, 'Charlie', NULL, NULL)]	[(1, 'Alice', 1, 'NY'), (2, 'Bob', 2, 'LA'), (3, 'Charlie', NULL, NULL), (NULL, NULL, 4, 'Chicago')]	[(1, 'Alice', 1, 'NY'), (2, 'Bob', 2, 'LA'), (3, 'Charlie', NULL, NULL), (NULL, NULL, 4, 'Chicago')]

Key Moments - 2 Insights

Why do some rows have NULL values in the output?

Rows with NULLs come from one table that has no matching row in the other table, as shown in steps 3 and 4 of the execution_table.

Does FULL OUTER JOIN only return matched rows?

No, it returns all matched rows plus unmatched rows from both tables with NULLs for missing parts, as seen in the combined output after step 4.

Visual Quiz - 3 Questions

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

A (3, 'Charlie', 3, 'Chicago')

B (3, 'Charlie', NULL, NULL)

C (NULL, NULL, 3, 'Charlie')

D (NULL, 'Charlie', NULL, NULL)

💡 Hint

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

At which step does the join condition become false because there is no matching row in Table B?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Join Condition' column in the execution_table where it is False and Table B Row is NULL.

If Table A had an extra row with id=5 and no matching id in Table B, what would happen in the output?

A A new output row with id=5 and NULLs for Table B columns would be added

B No change, because FULL OUTER JOIN only shows matched rows

C The query would error out

D The row would be ignored

💡 Hint

FULL OUTER JOIN includes unmatched rows from both tables with NULLs, as shown in steps 3 and 4.

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,
matching where keys equal,
NULLs where no match exists.

Full Transcript

FULL OUTER JOIN takes two tables and combines them by matching rows on a condition. It keeps all rows from both tables. When a row in one table has no match in the other, it still appears in the result with NULLs for the missing side. This is useful to see all data from both tables together, including unmatched rows. The example query joins tables A and B on id, showing matched rows and unmatched rows with NULLs. The execution table shows step-by-step how rows are matched or added with NULLs. Key points are that unmatched rows appear with NULLs, and the join condition controls matching. This helps beginners understand how FULL OUTER JOIN works visually.

Practice

1. What does a FULL OUTER JOIN do in PostgreSQL?

easy

A. It returns all rows from the right table and matching rows from the left table.

B. It returns only rows that have matching values in both tables.

C. It returns all rows from the left table and matching rows from the right table.

D. It returns all rows from both tables, matching where possible, and NULLs where no match exists.

Solution

1. Step 1: Understand the join types

   INNER JOIN returns only matching rows; LEFT JOIN returns all left table rows plus matches; RIGHT JOIN returns all right table rows plus matches.

2. Step 2: Define FULL OUTER JOIN behavior

   FULL OUTER JOIN returns all rows from both tables, matching where possible, and fills unmatched columns with NULL.

3. Final Answer:

   It returns all rows from both tables, matching where possible, and NULLs where no match exists. -> Option D

4. Quick Check:

   FULL OUTER JOIN = all rows both tables [OK]

Hint: Remember: FULL OUTER JOIN = all rows from both tables [OK]

Common Mistakes:

• Confusing FULL OUTER JOIN with INNER JOIN
• Thinking FULL OUTER JOIN returns only left or right table rows
• Assuming unmatched rows are excluded

2. Which of the following is the correct syntax for a FULL OUTER JOIN in PostgreSQL?

easy

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

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

C. SELECT * FROM table1 FULL JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 OUTER FULL JOIN table2 ON table1.id = table2.id;

Solution

1. Step 1: Recall PostgreSQL FULL OUTER JOIN syntax

   The correct syntax is: SELECT ... FROM table1 FULL OUTER JOIN table2 ON condition;

2. Step 2: Check each option

   The syntax 'SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id;' matches exactly. Others err: 'JOIN FULL OUTER' wrong order; 'FULL JOIN ... WHERE' misuses WHERE for join; 'OUTER FULL JOIN' wrong sequence.

3. Final Answer:

   SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   FULL OUTER JOIN syntax = FULL OUTER JOIN ON [OK]

Hint: FULL OUTER JOIN always uses 'FULL OUTER JOIN ... ON' syntax [OK]

Common Mistakes:

• Swapping JOIN and FULL OUTER keywords
• Omitting ON clause
• Using WHERE instead of ON for join condition

3. Given these tables:
Table A:
ID | Name
1 | Alice
2 | Bob
4 | Diana

Table B:
ID | City
2 | Paris
3 | London
4 | Berlin

What is the result of:
SELECT COALESCE(A.ID, B.ID) AS ID, A.Name, B.City FROM A FULL OUTER JOIN B ON A.ID = B.ID ORDER BY ID;

medium

A. [{2, 'Bob', 'Paris'}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}]

B. [{1, 'Alice', NULL}, {2, 'Bob', NULL}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}]

C. [{1, 'Alice', NULL}, {2, 'Bob', 'Paris'}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}]

D. [{1, 'Alice', NULL}, {2, 'Bob', 'Paris'}, {4, 'Diana', 'Berlin'}]

Solution

1. Step 1: Identify matching and unmatched IDs

   Matching IDs: 2 and 4. Unmatched in A: 1. Unmatched in B: 3.

2. Step 2: Construct FULL OUTER JOIN result

   Rows:
   - ID 1: Name 'Alice', City NULL (no match in B)
   - ID 2: Name 'Bob', City 'Paris' (match)
   - ID 3: Name NULL, City 'London' (no match in A)
   - ID 4: Name 'Diana', City 'Berlin' (match)

3. Final Answer:

   [{1, 'Alice', NULL}, {2, 'Bob', 'Paris'}, {3, NULL, 'London'}, {4, 'Diana', 'Berlin'}] -> Option C

4. Quick Check:

   FULL OUTER JOIN includes all rows with NULLs for unmatched [OK]

Hint: Match IDs, include all rows, NULL for missing side [OK]

Common Mistakes:

• Excluding unmatched rows from either table
• Mixing up NULL placement
• Ordering rows incorrectly

4. Consider this query:
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id WHERE A.name = 'Alice';
Why might this query return fewer rows than expected?

medium

A. Because the WHERE clause filters out rows where A.name is NULL, excluding unmatched rows from B.

B. Because FULL OUTER JOIN only returns matching rows.

C. Because the ON condition is incorrect syntax.

D. Because the query needs GROUP BY to include all rows.

Solution

1. Step 1: Understand FULL OUTER JOIN with WHERE clause

   FULL OUTER JOIN returns all rows, but WHERE filters after join. Filtering on A.name excludes rows where A.name is NULL (unmatched in A).

2. Step 2: Effect of filtering on unmatched rows

   Rows unmatched in A have NULL for A.name, so WHERE A.name = 'Alice' removes them, reducing rows.

3. Final Answer:

   Because the WHERE clause filters out rows where A.name is NULL, excluding unmatched rows from B. -> Option A

4. Quick Check:

   WHERE filters after join, can exclude NULLs [OK]

Hint: Use WHERE carefully; it filters after join, may exclude NULLs [OK]

Common Mistakes:

• Thinking FULL OUTER JOIN ignores WHERE filters
• Assuming ON clause filters rows after join
• Believing GROUP BY affects join row count

5. You have two tables:
Employees(emp_id, name) and Departments(dept_id, emp_id, dept_name).
You want a list of all employees and their departments if any, plus all departments and their employees if any.
Which query correctly uses FULL OUTER JOIN to achieve this?

hard

A. SELECT e.emp_id, e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.emp_id = d.emp_id;

B. SELECT e.emp_id, e.name, d.dept_name FROM Employees e FULL OUTER JOIN Departments d ON e.emp_id = d.emp_id;

C. SELECT e.emp_id, e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.emp_id = d.emp_id;

D. SELECT e.emp_id, e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.emp_id = d.emp_id;

Solution

1. Step 1: Understand the requirement

   We want all employees and their departments if any, plus all departments and their employees if any. This means all rows from both tables.

2. Step 2: Choose the correct join type

   FULL OUTER JOIN returns all rows from both tables, matching where possible. LEFT or RIGHT JOIN only return all rows from one table.

3. Final Answer:

   SELECT e.emp_id, e.name, d.dept_name FROM Employees e FULL OUTER JOIN Departments d ON e.emp_id = d.emp_id; -> Option B

4. Quick Check:

   FULL OUTER JOIN returns all rows both tables [OK]

Hint: Use FULL OUTER JOIN to include all rows from both tables [OK]

Common Mistakes:

• Using LEFT or RIGHT JOIN excludes unmatched rows from one table
• Using INNER JOIN excludes unmatched rows
• Forgetting ON condition