MySQL SELF JOIN

Joins are very important for effective data retrieval and analysis. The ‘JOIN’ clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like inner join, outer join, left join, right join, full join, and self join.

In this article, we will discuss the concept of MySQL SELF JOIN, exploring various examples to illustrate its usage. A SELF JOIN is a special type of join operation where a table is joined with itself. We will demonstrate how to implement MySQL SELF JOIN using both INNER JOIN and LEFT JOIN clauses, providing clear and concise examples to showcase the different scenarios in which SELF JOIN can be applied.

MySQL SELF JOIN

Generally joins are used to combine data from two or more tables based on a common column, but in Self Join we will combine data within the same table itself. Self Join is done by combing rows from the same table based on some specific conditions. To perform Self Join Alias names are used with the Join condition based on the columns that define the relationship. There is no keyword as ‘Self Join’, but it is an ordinary ‘Join’ where a table is joined to itself. Self Join is a special type of join and it is commonly used for creating hierarchy by simplifying the retrieval of interconnected data within the same table.

MySQL SELF JOIN Syntax:

SELECT *

FROM table_name AS t1

JOIN table_name AS t2 ON t1.column_name = t2.column_name;

MySQL SELF JOIN Examples

Table Creation and Insertion of Values

We are going to create a table named employees and we are inserting five employees and their details.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES (1, 'John', NULL);
INSERT INTO employees VALUES (2, 'Jane', 1);
INSERT INTO employees VALUES (3, 'Bob', 2);
INSERT INTO employees VALUES (4, 'Alice', 1);
INSERT INTO employees VALUES (5, 'Charlie', 3);

select * from employees;

The above table displays the employees and their details inserted in the table.

Example 1: MySQL SELF JOIN Using INNER JOIN Clause

SELECT e1.employee_id AS employee_id, 
       e1.employee_name AS employee_name, 
       e2.employee_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

In the above example, e1 and e2 are aliases of the same table(employees) and we are joining the columns manager_id and employee_id of the same table using inner join which includes all data that are common from both e1 and e2 aliases. Since we use inner join here, all rows from the employees table with e1 and e2 will be displayed in the result only if they match the condition e1.manager_id = e2.employee_id will be displayed in the result.

Output:

Output-SELF JOIN using INNER JOIN

Example 2: MySQL SELF JOIN Using LEFT JOIN Clause

SELECT e1.employee_id AS employee_id, 
       e1.employee_name AS employee_name, 
       e2.employee_name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

In the above example, e1 and e2 are aliases of the same table and we are joining the columns manager_id and employee_id of the same table using left join which includes all data from alias e1. Since we use left join here, all rows from the employees table with e1 will be displayed in the result and only the matching rows from the same table with alias e2 based on the condition e1.manager_id = e2.employee_id will be displayed in the result.

Output:

Output-SELF JOIN using LEFT JOIN

Applications of SQL Self Join

SQL Self Join can be used in many different kinds of scenarios like:

1. Hierarchical Structures: When dealing with hierarchical structures, such as organizational charts, where individuals relate to one another within the same table, self joins are helpful.
2. Comparing Data Within a Table: Self join makes it easier to analyze relationships between various rows by doing comparisons of data within a single table.
3. Recursive Relationships: When handling recursive relationships—like a table that references itself—inside a table, self join is crucial.
4. Data Partitioning: When there exist relationships between rows in a table, self join can be used to partition and analyze data within the table.
5. Tracking Historical modifications: Self Join helps keep track previous changes by enabling you to compare different versions of records within the same table.
6. Aliases Creation: It makes it possible to create aliases for a table, giving users a mechanism for differentiating between multiple instances of the same table when joining them.

Conclusion

Through this article, we’ve explored the fundamentals of MySQL SELF JOIN, from understanding its syntax to practical applications using INNER JOIN and LEFT JOIN clauses.Whether it’s creating organizational charts, tracking historical changes, or unraveling social networks, the SELF JOIN proves to be a valuable join, providing a clearer picture of relationships within a single table.