Oracle Self Join FAQs with Examples

1. What is a Self Join in Oracle?

A Self Join is a join in which a table is joined with itself. It is commonly used when rows in the same table are related to one another, such as employees and their managers.

A Self Join uses table aliases to treat the same table as two different tables.

Syntax

SELECT a.column_name,
       b.column_name
FROM table_name a
JOIN table_name b
ON a.common_column = b.common_column;

2. Why do we use a Self Join?

A Self Join is used to:

• Find employee-manager relationships.
• Display hierarchical data.
• Compare rows within the same table.
• Find duplicate records.
• Compare current and previous records.

3. How does a Self Join work?

Suppose the EMPLOYEES table contains:

EMPLOYEES

EMPLOYEE_ID   EMPLOYEE_NAME   MANAGER_ID
101           John            103
102           David           103
103           Scott           NULL

Query

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Output

Employee   Manager
John       Scott
David      Scott
Scott      NULL

4. What is the syntax of a Self Join?

SELECT a.column_name,
       b.column_name
FROM table_name a
JOIN table_name b
ON a.column_name = b.column_name;

Example:

SELECT e.employee_name,
       m.employee_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

5. Why are table aliases required in a Self Join?

Since the same table is used twice, aliases distinguish each role.

Example:

employees e
employees m

Here:

• e → Employee
• m → Manager

Without aliases, Oracle cannot distinguish between the two references.

6. Can a Self Join use INNER JOIN?

Yes.

SELECT e.employee_name,
       m.employee_name AS manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;

Only employees with managers are returned.

7. Can a Self Join use LEFT JOIN?

Yes.

SELECT e.employee_name,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Employees without managers are also returned.

8. Can a Self Join use RIGHT JOIN?

Yes, although it is less common.

SELECT e.employee_name,
       m.employee_name
FROM employees e
RIGHT JOIN employees m
ON e.manager_id = m.employee_id;

All rows from the manager side are returned.

9. Can a Self Join use FULL OUTER JOIN?

Yes.

SELECT e.employee_name,
       m.employee_name
FROM employees e
FULL OUTER JOIN employees m
ON e.manager_id = m.employee_id;

Returns matching and non-matching rows from both sides.

10. Can a Self Join use the old Oracle (+) syntax?

Yes.

SELECT e.employee_name,
       m.employee_name
FROM employees e,
     employees m
WHERE e.manager_id = m.employee_id(+);

The ANSI JOIN syntax is recommended for new development because it is more readable and portable.

11. Can a Self Join compare rows?

Yes.

Example: Find employees with the same salary.

SELECT e1.employee_name,
       e2.employee_name,
       e1.salary
FROM employees e1
JOIN employees e2
ON e1.salary = e2.salary
AND e1.employee_id < e2.employee_id;

The condition e1.employee_id < e2.employee_id avoids returning the same pair twice and prevents matching a row with itself.

12. Can a Self Join find duplicate records?

Yes.

SELECT e1.employee_name,
       e2.employee_name
FROM employees e1
JOIN employees e2
ON e1.email = e2.email
AND e1.employee_id <> e2.employee_id;

13. Can a Self Join use a WHERE clause?

Yes.

SELECT e.employee_name,
       m.employee_name AS manager
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE m.employee_name = 'Scott';

14. Can a Self Join use aggregate functions?

Yes.

SELECT m.employee_name,
       COUNT(e.employee_id) AS employee_count
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
GROUP BY m.employee_name;

15. Can a Self Join use subqueries?

Yes.

SELECT *
FROM (
  SELECT e.employee_name,
         m.employee_name AS manager
  FROM employees e
  LEFT JOIN employees m
  ON e.manager_id = m.employee_id
);

16. What is the difference between a Self Join and an Inner Join?

Self Join	Inner Join
Joins a table to itself	Joins two different tables
Uses aliases	Usually different tables
Used for hierarchical or related data in one table	Used for related data across tables

17. What are common uses of a Self Join?

• Employee-manager hierarchy
• Family relationships
• Organizational charts
• Finding duplicate records
• Comparing rows in the same table
• Bill of Materials (parent-child relationships)

18. What are the advantages of a Self Join?

• Retrieves hierarchical data.
• Compares rows in the same table.
• Finds duplicate records.
• Uses standard SQL syntax.
• Supports all join types (INNER, LEFT, RIGHT, FULL).

19. What are the disadvantages of a Self Join?

• Can become difficult to read with many aliases.
• Performance may decrease on large tables if appropriate indexes are missing.
• Complex hierarchical queries may be easier to express using Oracle hierarchical query features (CONNECT BY) or recursive subquery factoring (WITH ...).

20. What are common errors with a Self Join?

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00918	Column ambiguously defined (often due to missing aliases or column qualification)
ORA-00933	SQL command not properly ended

21. Real-Time Example – Employee and Manager

EMPLOYEES

EMPLOYEE_ID   EMPLOYEE_NAME   MANAGER_ID
101           John            103
102           David           103
103           Scott           NULL

Query

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Output

Employee   Manager
John       Scott
David      Scott
Scott      NULL

22. Self Join vs Other Joins

Join Type	Purpose
INNER JOIN	Matching rows between two tables
LEFT JOIN	All rows from the left table and matching rows from the right
RIGHT JOIN	All rows from the right table and matching rows from the left
FULL OUTER JOIN	All matching and non-matching rows
CROSS JOIN	Every possible combination of rows
SELF JOIN	Joins a table with itself

23. What is the difference between a Self Join and Oracle's CONNECT BY?

Self Join	CONNECT BY
Joins a table to itself	Designed specifically for hierarchical queries
Best for one-level parent-child relationships	Best for multi-level hierarchies
Uses standard SQL joins	Uses START WITH and CONNECT BY clauses

Example of a hierarchical query:

SELECT employee_id,
       employee_name,
       manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

This returns the entire employee hierarchy across multiple levels.