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.
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.
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.
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.