MySQL Self Join

A Self Join is a way to combine rows from a single table with other rows of the same table based on a related column. It's essentially like considering the same table twice and joining it based on a common column.

Syntax 

The standard syntax for the Self Join clause in MySQL is:

SELECT 
    a.column_name, b.column_name...
FROM
    table_name AS a
JOIN 
    table_name AS b
ON 
    a.common_column = b.common_column

In this syntax: 

table_name: This is the name of the table you wish to self-join. 

a and b: These are the different aliases for the same table. 

a.column_name, b.column_name, ...: These are the columns you wish to select. 

common_column: This is the column of the table used to match rows with itself.

Demo Database

For demonstration purposes, let's consider a students table that lists students and their mentors, both of which are present in the same table.

ID	Name	Mentor_ID
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

In this table:

• Alice is a mentor but doesn't have a mentor herself.
• Bob and Charlie both have Alice as their mentor.
• David has Bob as his mentor.

Example 

Let's find out the names of all students and their respective mentors using the students table.

SELECT 
    s1.Name AS Student_Name, 
    s2.Name AS Mentor_Name
FROM 
    students s1
JOIN 
    students s2
ON 
    s1.Mentor_ID = s2.ID;

Result:

Student_Name	Mentor_Name
Bob	Alice
Charlie	Alice
David	Bob

When to Use Self Join

Use SELF JOIN when you need to combine rows with other rows in the same table based on a related column. 

Scenario: If you have an Employees table and you wish to list all pairs of employees who share the same job title, you'd use a SELF JOIN on the job title column.

Summary 

A Self Join is a powerful tool in SQL, allowing for complex queries on a single table by treating it as two separate entities. When faced with scenarios where the data relationship is within the same table, consider leveraging a Self Join to fetch the necessary data. Like all JOIN operations, it's crucial to ensure performance by indexing the columns involved appropriately.