Back to basics: self joins

A self join is a join of a table to itself. This table appears twice (or more) in the FROM clause and is followed by table aliases that qualify column names in the join condition and the SELECT clause. Take for example the employees table, the manager of one employee is also an employee. The rows for both are in the same employees table.

To get information about an employee and her manager, you have to join the employee table to itself, thereby treating employees as if it were two separate tables. The following example query uses a self join to return the name of each employee along with the name of the employee’s manager: 

SELECT e.last_name employee, m.last_name manager
  FROM employees e INNER JOIN employees m
       ON e.manager_id = m.employee_id;


         EMPLOYEE                  MANAGER          
------------------------- ------------------------- 
Kochhar                   King                      
De Haan                   King                      
Hunold                    De Haan                   
Ernst                     Hunold                    
Austin                    Hunold                    
Pataballa                 Hunold                    

...                   

106 row(s) retrieved

But

SELECT COUNT (*)
  FROM employees;

               COUNT(*)                
-------------------------------------- 
                                   107

Even though the employees table has 107 rows, the previous query returned only 106 rows. This is because there is an employee without a manager_id. Oracle excludes that employee’s row from the result set while performing the self inner join. To include that employee without a manager, you need an outer join:

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


         EMPLOYEE                  MANAGER          
------------------------- ------------------------- 
King                     
Kochhar                   King                      
De Haan                   King                      
Hunold                    De Haan                   
Ernst                     Hunold                    
Austin                    Hunold                    
Pataballa                 Hunold                    

...                   

107 row(s) retrieved

The previous examples showed self equijoins. However, there are situations when you need to perform self non-equijoins. For example, let’s assume that you are in charge of organizing an interdepartmental soccer competition for the following departments:

SELECT department_name
  FROM departments
 WHERE department_id IN (10, 20, 30, 40);

        DEPARTMENT_NAME        
------------------------------ 
Administration                 
Marketing                      
Purchasing                     
Human Resources                

4 row(s) retrieved

You decide that each department plays against the other three departments only once, which means that the output of the query you are going to write cannot contain one combination Administration, Marketing and another Marketing, Administration because that means that each department plays against the others twice. The solution is to use a self non-equijoin query like this:

SELECT d1.department_name dept1, d2.department_name dept2
  FROM departments d1 INNER JOIN departments d2
       ON d1.department_id < d2.department_id
 WHERE d1.department_id IN (10, 20, 30, 40)
   AND d2.department_id IN (10, 20, 30, 40);

             DEPT1                          DEPT2             
------------------------------ ------------------------------ 
Administration                 Marketing                      
Administration                 Purchasing                     
Administration                 Human Resources                
Marketing                      Purchasing                     
Marketing                      Human Resources                
Purchasing                     Human Resources                

6 row(s) retrieved

That’s it for self joins :)

Sources:

Possibly related:

Tagged , | Post a Comment