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.
No comments:
Post a Comment