Hello! Today, we want to talk about Self Join in SQL. We might also discuss different types of JOINs like INNER JOIN and OUTER JOIN. However, Self Join is a specific type of JOIN that allows you to connect a table to itself. This is used when we have information within a table that is related to itself, and we want to maintain these relationships within the same table.
Suppose we have a table 'employees' where each employee may have a supervisor who is also an employee of this company. In this case, to retrieve information such as who is under whose supervision, we need a Self Join. The powerful and flexible nature that SQL provides allows us to easily access this data.
It is important to note that in Self Join, we use distinct identifiers such as an alias for the table to avoid overlap between data and columns. To put it simply, suppose we have a temporary version of a table that contains some information about employees and other information related to the supervisors.
Now, a practical example of Self Join will help clarify this concept for you. Suppose we have a table called 'employees' that contains columns including 'id', 'name', and 'manager_id'. The 'manager_id' actually refers to the same 'id' of the supervisor.
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;
In the example you see above:
- The first line selects the employee name and the supervisor name from the chosen table.
- From the expression
FROM employees e1, we are using the table itself and linking it to itself and giving it an alias 'e1'. - The next line,
JOIN employees e2, allows us to introduce the table again for the second time, giving it an alias 'e2', which previously defined that table for identifying the supervisor. - The statement
ON e1.manager_id = e2.idspecifies how these two tables are connected; that is, the 'manager_id' in 'e1' must correspond to the 'id' in 'e2'.
I hope these explanations help you better understand Self Join and that you can use this in your own projects.