Scenario for Self Join :-
Suppose you're tasked with writing a SQL query to retrieve a list of employees and their managers. You can't write a basic SQL SELECT statement to retrieve this information, as you need to cross reference information contained in other records within the same table. Fortunately, you can use a self-join to solve this dilemma by joining the table to itself.
Self
join in sql means joining the single table to itself. It creates the partial
view of the single table and retrieves the related records. You can use aliases
for the same table to set a self join between the single table and retrieve the
records satisfying the condition in where clause.
For self join in sql you can try the following example:
Create table employees:
Now to get the names of managers from the above single table you can use sub queries or simply the self join.
Self Join SQL Query to get the names of manager and employees:
select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Result:
Understanding the Self Join Example
In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.
For self join in sql you can try the following example:
Create table employees:
| emp_id | emp_name | emp_manager_id |
| 1 | John | Null |
| 2 | Tom | 1 |
| 3 | Smith | 1 |
| 4 | Albert | 2 |
| 5 | David | 2 |
| 6 | Murphy | 5 |
| 7 | Petra | 5 |
Now to get the names of managers from the above single table you can use sub queries or simply the self join.
Self Join SQL Query to get the names of manager and employees:
select e1.emp_name 'manager',e2.emp_name 'employee'
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Result:
| manager | employee |
| John | Tom |
| John | Smith |
| Tom | Albert |
| Tom | David |
| David | Murphy |
| David | Petra |
Understanding the Self Join Example
In the above self join query, employees table is joined with itself using table aliases e1 and e2. This creates the two views of a single table.
from employees e1 join employees e2
on e1.emp_id=e2.emp_manager_id
Here e.emp_manager_id passes the manager id from the 2nd view to the first aliased e1 table to get the names of managers.
