Source Code Examples

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.

MySQL