FULL OUTER JOIN in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of FULL OUTER JOIN with matching and non-matching rows

Consider two tables Employees and Departments:

Employees:
+----+----------+-----------+
| id | name     | dept_id   |
+----+----------+-----------+
| 1  | Alice    | 10        |
| 2  | Bob      | 20        |
| 3  | Charlie  | NULL      |
+----+----------+-----------+

Departments:
+----+------------+
| id | dept_name  |
+----+------------+
| 10 | HR         |
| 30 | Marketing  |
+----+------------+

What is the result of this query?

SELECT e.name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.dept_id = d.id
ORDER BY e.name NULLS LAST, d.dept_name NULLS LAST;

PostgreSQL

SELECT e.name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.dept_id = d.id
ORDER BY e.name NULLS LAST, d.dept_name NULLS LAST;

A

+---------+------------+
| name    | dept_name  |
+---------+------------+
| Alice   | HR         |
| Bob     | NULL       |
| Charlie | NULL       |
| NULL    | Marketing  |
+---------+------------+

B

+---------+------------+
| name    | dept_name  |
+---------+------------+
| Alice   | HR         |
| Bob     | NULL       |
| NULL    | Marketing  |
+---------+------------+

C

+---------+------------+
| name    | dept_name  |
+---------+------------+
| Alice   | HR         |
| Bob     | NULL       |
| Charlie | NULL       |
+---------+------------+

D

+---------+------------+
| name    | dept_name  |
+---------+------------+
| Alice   | HR         |
| NULL    | Marketing  |
+---------+------------+

💡 Hint

FULL OUTER JOIN returns all rows from both tables. If there is no match, NULL fills the missing side.

The FULL OUTER JOIN returns all rows from Employees and Departments. Alice matches HR (dept_id=10). Bob's dept_id=20 has no matching department, so dept_name is NULL. Charlie has NULL dept_id, so no match on department side. Marketing department (id=30) has no matching employee, so name is NULL.

🧠 Conceptual

intermediate

Understanding NULLs in FULL OUTER JOIN results

When using FULL OUTER JOIN between two tables, which statement about NULL values in the result is always true?

A NULL values never appear in the result of a FULL OUTER JOIN.

B NULL values appear only in columns from the table on the left side of the join.

C NULL values appear only in columns from the table on the right side of the join.

D NULL values appear in columns from either table when there is no matching row in the other table.

💡 Hint

Think about what happens when a row in one table has no matching row in the other.

FULL OUTER JOIN returns all rows from both tables. When a row from one table has no match in the other, the columns from the unmatched table are filled with NULLs.

📝 Syntax

advanced

Identify the syntax error in FULL OUTER JOIN query

Which of the following FULL OUTER JOIN queries will cause a syntax error in PostgreSQL?

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

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

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

D SELECT * FROM A FULL OUTER JOIN B USING (id);

💡 Hint

Check the operator used for equality in the ON clause.

PostgreSQL uses a single equals sign (=) for equality comparison. Using double equals (==) causes a syntax error.

❓ optimization

advanced

Optimizing FULL OUTER JOIN with large tables

You have two large tables, Orders and Shipments, joined by FULL OUTER JOIN on order_id. Which approach can improve query performance?

A Add indexes on order_id columns in both tables before running the FULL OUTER JOIN.

B Replace FULL OUTER JOIN with CROSS JOIN to reduce complexity.

C Remove WHERE clause filters to allow the database to optimize better.

D Use SELECT * to fetch all columns to avoid missing data.

💡 Hint

Indexes help speed up join operations on large tables.

Indexes on join columns allow the database to quickly match rows, improving FULL OUTER JOIN performance. CROSS JOIN produces a Cartesian product and is much slower. Removing filters usually increases data size. SELECT * fetches unnecessary data, slowing query.

🔧 Debug

expert

Diagnose unexpected NULLs in FULL OUTER JOIN result

You run this query:

SELECT e.id, e.name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.dept_id = d.id
WHERE d.dept_name = 'Sales';

You expect to see all employees in Sales department, but the result is empty. Why?

A The ON condition is incorrect; it should be e.id = d.id.

B FULL OUTER JOIN does not work with WHERE clauses.

C The WHERE clause filters out rows where d.dept_name is NULL, removing unmatched employees.

D Employees table has no rows with dept_id matching any department.

💡 Hint

Think about how WHERE filters affect NULLs from unmatched rows.

The WHERE clause filters rows after the join. Rows where d.dept_name is NULL (employees without matching department) are excluded, so no rows appear if no department named 'Sales' exists. To include all employees with or without matching departments, use the condition in the JOIN ON clause or use LEFT JOIN.

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