FULL OUTER JOIN behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of FULL OUTER JOIN with partial matches

Consider two tables:

Employees
id | name
1 | Alice
2 | Bob
3 | Carol

Departments
id | dept_name
2 | Sales
3 | HR
4 | IT

What is the result of this query?

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

SQL

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

A

1 | Alice | NULL
2 | Bob | Sales
3 | Carol | HR
NULL | NULL | IT

B

1 | Alice | NULL
2 | Bob | Sales
3 | Carol | HR

C

2 | Bob | Sales
3 | Carol | HR
4 | NULL | IT

D

1 | Alice | NULL
2 | Bob | Sales
3 | Carol | HR
4 | NULL | IT

💡 Hint

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

The FULL OUTER JOIN returns all rows from both Employees and Departments. For id=1, only Employees has a row, so Departments columns are NULL. For id=4, only Departments has a row, so Employees columns are NULL. For ids 2 and 3, rows match and show combined data.

🧠 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 true?

A NULLs appear only in columns from the left table when there is no match.

B NULLs appear only in columns from the right table when there is no match.

C NULLs never appear in FULL OUTER JOIN results.

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

💡 Hint

Think about what happens when a row exists in one table but not the other.

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

📝 Syntax

advanced

Identify the syntax error in FULL OUTER JOIN query

Which of the following SQL queries will cause a syntax error?

SELECT A.id, B.name
FROM TableA A
FULL OUTER JOIN TableB B ON A.id = B.id

A SELECT A.id, B.name FROM TableA A FULL OUTER JOIN TableB B ON A.id = B.id;

B SELECT A.id, B.name FROM TableA A FULL JOIN TableB B ON A.id = B.id;

C SELECT A.id, B.name FROM TableA A OUTER FULL JOIN TableB B ON A.id = B.id;

D SELECT A.id, B.name FROM TableA A LEFT JOIN TableB B ON A.id = B.id;

💡 Hint

Check the order of keywords in the JOIN clause.

The correct syntax is FULL OUTER JOIN or FULL JOIN (depending on the database). 'OUTER FULL JOIN' is invalid syntax.

❓ optimization

advanced

Optimizing FULL OUTER JOIN queries for large tables

You have two large tables with millions of rows each. You want to perform a FULL OUTER JOIN on a key column. Which approach can improve query performance?

A Create indexes on the join key columns in both tables before running the FULL OUTER JOIN.

B Use CROSS JOIN instead of FULL OUTER JOIN to get all combinations and filter later.

C Remove indexes on join keys to speed up the join operation.

D Use a subquery to select all rows from one table and then LEFT JOIN the other table.

💡 Hint

Indexes help the database find matching rows faster.

Creating indexes on join keys helps the database quickly locate matching rows, improving FULL OUTER JOIN performance. CROSS JOIN generates all combinations and is very slow. Removing indexes slows down joins. Using subqueries with LEFT JOIN may not cover all rows from both tables.

🔧 Debug

expert

Diagnose unexpected NULLs in FULL OUTER JOIN result

You run this query:

SELECT A.id, A.value, B.value
FROM TableA A
FULL OUTER JOIN TableB B ON A.id = B.id
WHERE A.value > 10;

You notice that rows with NULL in A.value do not appear in the result. Why?

A The WHERE clause filters after the FULL OUTER JOIN, so rows with NULL A.value pass the filter.

B The condition A.value > 10 excludes rows where A.value is NULL, so those rows should not appear.

C The WHERE clause filters before the FULL OUTER JOIN, so NULLs in A.value are ignored.

D The FULL OUTER JOIN duplicates rows with NULL values in A.value.

💡 Hint

Think about when the WHERE clause is applied in relation to the JOIN.

The WHERE clause is applied after the FULL OUTER JOIN. Rows from TableB without matching TableA rows have NULL in A.value. The condition A.value > 10 evaluates to UNKNOWN for NULLs, so those rows are excluded. However, if the condition is in WHERE, rows with NULL A.value are filtered out. If you want to keep all rows, use the condition in the JOIN or use OR A.value IS NULL.