Self Joins

A Self-Join, joining a table to itself, can be a difficult concept for beginners to SQL to comprehend.  Although one is working with one table, 2 instances of the table are created in memory, basically making the self-join a regular inner join using 2 tables.  In addition, to execute a self-join, the table must hold a foreign key that points back to the primary key in the same table. This means that the table must be specifically designed for a Self-Join.

Self-Joins are common when it comes to multi-leveled organizational structures. Lets take a look at a simple example: The table below contains employees, some of whom are supervised by other employees(Managers). These employees are in turn supervised by higher level managers.

emp_ID	l_name	f_name	mgr_ID
001	Able	John
002	Bess	Kerri	1
003	Trent	Bob	2
004	Carr	Lloyd	2

Here, Employees 003 and 004 are supervised by Employee 002(Kerri Bess), who is supervised by Employee 001(John Able).  To write a SQL query to show employees and their managers, you would use the following statement construct:

SELECT E.l_name, E.f_name, M.l_name
FROM Employees E INNER JOIN Employees M ON E.empID=M.emp_ID;

This Self-Join uses 2 instances of the Employees table in memory as follows:

EMPLOYEES E

emp_ID	l_name	f_name	mgr_ID
002	Bess	Kerri	1
003	Trent	Bob	2
004	Carr	Lloyd	2

EMPLOYEES M

emp_ID	l_name	f_name	mgr_ID
1	Able	John
2	Bess	Kerri	1
2	Bess	Kerri	1

If you run this query, the result set will show all employees less 1(employee who has no manager). This is because we have joined the 2 tables using an inner join on matching rows. To also show the employee not managed by anyone, you would use an outer join.