MySQL SELF JOIN

Last Updated : 10 Jun, 2024

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'll explore the concept of MySQL SELF JOIN, using simple examples to show how it works. A SELF JOIN is a type of join where a table is joined with itself, allowing you to compare rows within the same table. We'll explain how to use MySQL SELF JOIN with both INNER JOIN and LEFT JOIN clauses, highlighting different scenarios where SELF JOIN can be useful. By the end, you'll understand how to apply SELF JOIN in practical situations to manage and analyze your data effectively.

MySQL SELF JOIN

Generally joins in MySQL 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 combining 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 a 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;

Output:

Explanation: 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.

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;

Output:

Explanation: 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.

Applications of SQL SELF JOIN

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

Hierarchical Structures: When dealing with hierarchical structures, such as organizational charts, where individuals relate to one another within the same table, SELF JOIN are helpful.
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.
Recursive Relationships: When handling recursive relationships—like a table that references itself—inside a table, self join is crucial.
Data Partitioning: When there exist relationships between rows in a table, self join can be used to partition and analyze data within the table.
Tracking Historical Modifications: SELF JOIN helps keep track of previous changes by enabling you to compare different versions of records within the same table.
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.

MySQL CROSS JOIN

julietmaria

Improve

Article Tags :

MySQL SELF JOIN

MySQL SELF JOIN

MySQL SELF JOIN Examples

Table Creation and Insertion of Values

Example 1: MySQL SELF JOIN Using INNER JOIN Clause

Example 2: MySQL SELF JOIN Using LEFT JOIN Clause

Applications of SQL SELF JOIN

Conclusion

Similar Reads

MySQL Basics

MySQL User Management

MySQL Managing Databases

MySQL Managing Tables

MySQL Query

MySQL Clauses

MySQL Aggregate Functions

MySQL Data Constraints

MySQL Joining Data