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.