Oracle FULL OUTER JOIN FAQs with Examples

1. What is a FULL OUTER JOIN in Oracle?

A FULL OUTER JOIN returns:

• All matching rows from both tables.
• All unmatched rows from the left table.
• All unmatched rows from the right table.
• NULL values where no matching row exists.

It combines the results of a LEFT OUTER JOIN and a RIGHT OUTER JOIN.

Syntax

SELECT column_list
FROM table1 t1
FULL OUTER JOIN table2 t2
ON t1.column_name = t2.column_name;

You can also write:

SELECT column_list
FROM table1 t1
FULL JOIN table2 t2
ON t1.column_name = t2.column_name;

The keyword OUTER is optional.

2. Why do we use a FULL OUTER JOIN?

A FULL OUTER JOIN is used to:

• Retrieve all rows from both tables.
• Find matching and non-matching records.
• Compare two datasets.
• Identify missing records.
• Create reconciliation reports.

3. How does a FULL OUTER JOIN work?

Suppose we have:

EMPLOYEES

EMPLOYEE_ID   EMPLOYEE_NAME   DEPARTMENT_ID
101           John            10
102           David           20
103           Scott           40

DEPARTMENTS

DEPARTMENT_ID   DEPARTMENT_NAME
10              Sales
20              HR
30              Finance

Query

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

Output

Employee   Department
John       Sales
David      HR
Scott      NULL
NULL       Finance

Explanation:

• John and David have matching departments.
• Scott has no matching department.
• Finance has no matching employee.

4. What is the syntax of a FULL OUTER JOIN?

SELECT *
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

Or:

SELECT *
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;

5. What is the difference between FULL JOIN and FULL OUTER JOIN?

There is no difference.

FULL JOIN and FULL OUTER JOIN produce the same results.

6. What happens when there is no matching row?

Oracle returns:

• Values from the existing table.
• NULL values for the missing table.

Example:

Employee   Department
Scott      NULL
NULL       Finance

7. Can a FULL OUTER JOIN use aliases?

Yes.

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id;

Aliases improve readability.

8. Can a FULL OUTER JOIN use a WHERE clause?

Yes.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

To return only unmatched rows from either table:

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
OR d.department_id IS NULL;

9. Can a FULL OUTER JOIN use GROUP BY?

Yes.

SELECT d.department_name,
       COUNT(e.employee_id) AS employee_count
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name;

10. Can a FULL OUTER JOIN use ORDER BY?

Yes.

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id
ORDER BY department_name;

11. Can a FULL OUTER JOIN use multiple conditions?

Yes.

SELECT *
FROM orders o
FULL JOIN shipments s
ON o.order_id = s.order_id
AND o.customer_id = s.customer_id;

12. Can a FULL OUTER JOIN use multiple tables?

Yes.

SELECT e.employee_name,
       d.department_name,
       l.city
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id;

13. Can a FULL OUTER JOIN return duplicate rows?

Yes. If multiple matching rows exist in either table, duplicate combinations may appear.

14. How do you find unmatched rows?

Use IS NULL.

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
OR d.department_id IS NULL;

This returns:

• Employees without departments.
• Departments without employees.

15. Can FULL OUTER JOIN be written using UNION?

Yes. A FULL OUTER JOIN can be simulated using a LEFT OUTER JOIN, a RIGHT OUTER JOIN, and UNION (or UNION ALL with appropriate filtering).

SELECT e.employee_name,
       d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id

UNION

SELECT e.employee_name,
       d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

Using the native FULL OUTER JOIN is generally simpler and easier to maintain.

16. What are common uses of a FULL OUTER JOIN?

• Data reconciliation.
• Data migration validation.
• Comparing two systems.
• Employee vs department reports.
• Inventory comparisons.
• Customer vs order analysis.

17. Real-Time Example – Comparing Customers and Orders

CUSTOMERS

CUSTOMER_ID   CUSTOMER_NAME
1             Alice
2             Bob
3             Charlie

ORDERS

ORDER_ID   CUSTOMER_ID
100        1
101        2
102        4

Query

SELECT c.customer_name,
       o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;

Output

Customer   Order
Alice      100
Bob        101
Charlie    NULL
NULL       102

Explanation:

• Charlie has no orders.
• Order 102 belongs to a customer not found in the CUSTOMERS table.

18. What are the advantages of a FULL OUTER JOIN?

• Returns every row from both tables.
• Finds missing records on either side.
• Excellent for reconciliation.
• Reduces the need for multiple queries.
• Easy to identify unmatched data.

19. What are the disadvantages of a FULL OUTER JOIN?

• Can return large result sets.
• May be slower than inner or left joins.
• More memory-intensive for large datasets.
• Can produce duplicate rows when multiple matches exist.

20. Common Errors with FULL OUTER JOIN

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00918	Column ambiguously defined
ORA-00933	SQL command not properly ended

21. FULL OUTER JOIN vs LEFT OUTER JOIN

FULL OUTER JOIN	LEFT OUTER JOIN
Returns all rows from both tables	Returns all rows from the left table
Includes unmatched rows from both tables	Includes only unmatched rows from the left table

22. FULL OUTER JOIN vs RIGHT OUTER JOIN

FULL OUTER JOIN	RIGHT OUTER JOIN
Returns all rows from both tables	Returns all rows from the right table
Includes unmatched rows from both tables	Includes only unmatched rows from the right table

23. FULL OUTER JOIN vs INNER JOIN

FULL OUTER JOIN	INNER JOIN
Returns matching and non-matching rows	Returns only matching rows
Includes NULL values for missing matches	Excludes unmatched rows

24. Does the old Oracle (+) syntax support FULL OUTER JOIN?

No.

The legacy (+) operator supports left and right outer joins but does not support full outer joins.

Use ANSI syntax:

SELECT *
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

25. Interview Scenario

Question: Compare two tables and display:

• Matching rows.
• Rows existing only in the first table.
• Rows existing only in the second table.

Answer: Use a FULL OUTER JOIN.

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

26. How can you return only unmatched rows from a FULL OUTER JOIN?

Filter for NULL values in the join columns.

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
OR d.department_id IS NULL;

This excludes matched rows and returns only records that exist in one table but not the other.

---

FULL OUTER JOIN is useful for reconciliation, comparisons, and finding unmatched data from both sides.