SQL Self Join is a type of join where a table is joined with itself. In other words, it is a way to combine rows from the same table based on a related column between them. This type of join is useful when we need to compare data within the same table.
Self Join is performed by using the same table name with different aliases. The aliases are used to differentiate between the two instances of the same table. The syntax for Self Join is as follows:
SELECT t1.column_name1, t2.column_name2 FROM table_name t1, table_name t2 WHERE t1.related_column = t2.related_column;
Let's take an example to understand Self Join better. Consider a table named "employees" with the following data:
| id | name | manager_id |
|---|---|---|
| 1 | John | 3 |
| 2 | Jane | 3 |
| 3 | Mike | 4 |
| 4 | Tom | NULL |
In this table, the "id" column represents the employee id, the "name" column represents the employee name, and the "manager_id" column represents the id of the employee's manager. If an employee does not have a manager, the "manager_id" column will be NULL.
Now, let's say we want to find the name of each employee along with the name of their manager. We can use Self Join to achieve this. The SQL query for this would be:
SELECT e1.name AS employee_name, e2.name AS manager_name FROM employees e1, employees e2 WHERE e1.manager_id = e2.id;
The output of this query would be:
| employee_name | manager_name |
|---|---|
| John | Mike |
| Jane | Mike |
| Mike | Tom |
In this output, we can see that John and Jane have the same manager, which is Mike. And Mike's manager is Tom.
Self Join can also be used to find hierarchical data. For example, if we have a table of categories with a parent-child relationship, we can use Self Join to find the parent category of each category. The SQL query for this would be:
SELECT c1.category_name AS child_category, c2.category_name AS parent_category FROM categories c1, categories c2 WHERE c1.parent_category_id = c2.category_id;
Reference: