Reflexive Joins

Contact Us or call 1-877-932-8228
Reflexive Joins

Reflexive Joins

A reflexive join (or self-join), is a relationship in which a table is joined to itself. The employees table has both employees and employee managers in it. A self join can be made using this table and using the manager_id and the employee_id in the join.

Code Sample:

Joining-Tables/Demos/self_join.sql
SELECT first_name, last_name 
FROM employees 
WHERE employee_id=103;

SELECT first_name, last_name 
FROM employees 
WHERE manager_id=103;

--
--If we want to return information about the manager and his employees
--in the same query, we must use a self-join or reflexive join
--

SELECT	e.first_name, 
		e.last_name, 
		m.first_name AS manager_first_name, 
		m.last_name AS manager_last_name 
FROM employees e 
INNER JOIN employees m 
ON e.manager_id = m.employee_id;

The first query is a simple query to return the manager with employee id 103's first name and last name. The next query shows the names of the people that are managed by this manager. The third query is a reflexive query in which the employee table is joined to itself in order to return each employee's name and their manager name in the same row.

Hierarchical Queries

A special type of self join is available in Oracle called a hierarchical query. This type of query is useful for representing recursive relationships which might exist within the data.

Code Sample:

Joining-Tables/Demos/hierarchical_query.sql
SELECT employee_id, manager_id
FROM employees
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id;

SELECT employee_id, manager_id, sys_connect_by_path(last_name,'->') AS path
FROM employees
START WITH manager_id is null
CONNECT BY PRIOR employee_id = manager_id;

The first query produces hierarchical query results by using the CONNECT BY PRIOR clause. It orders the results of the employee table so that employess are nested under their assigned managers. The next query uses the SYS_CONNECT_BY_PATH function to provide a representation of the hierarchy using last names.

Next