• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Self Join

← Previous Next →

In relational databases, you sometimes need to compare rows within the same table. For instance, you might want to find employees who share the same manager, or students who have the same grade as others.

A MySQL Self Join allows a table to join itself. Although the concept is similar to regular joins, you treat the same table as two separate tables using aliases. This helps in comparing rows, analyzing hierarchical relationships, and performing certain analytical tasks.

What Is a Self Join?

A Self Join is when a table is joined with itself. The same table appears twice in the query, with different aliases representing different roles.

  • One alias represents the “left” instance of the table.

  • Another alias represents the “right” instance.

This is particularly useful for hierarchical or relational data stored in the same table.

Self Join Syntax

The basic syntax of a Self Join is:

SELECT a.column_name, b.column_name
FROM table_name a
JOIN table_name b
ON a.common_field = b.common_field;

Explanation

  • a → Alias for the first instance of the table.

  • b → Alias for the second instance of the table.

  • ON → Defines the relationship or comparison between the two instances.

Example Table

Consider an employees table:

employees

emp_id emp_name manager_id
1 Ananya NULL
2 Priya 1
3 Isha 1
4 Riya 2
5 Sunita 2

Explanation:

  • manager_id refers to emp_id of the employee’s manager.

  • For example, Priya and Isha report to Ananya (manager_id = 1).

Basic Self Join Example

To find the names of employees along with their manager names:

SELECT e.emp_name AS Employee, m.emp_name AS Manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

Result:

Employee Manager
Ananya NULL
Priya Ananya
Isha Ananya
Riya Priya
Sunita Priya

Explanation:

  • e is the alias for employees as employees themselves.

  • m is the alias representing the managers.

  • Employees without a manager (like Ananya) show NULL.

Self Join with INNER JOIN

You can also use an INNER JOIN if you want only employees who have managers:

SELECT e.emp_name AS Employee, m.emp_name AS Manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.emp_id;

Result:

Employee Manager
Priya Ananya
Isha Ananya
Riya Priya
Sunita Priya

Explanation:

  • Employees without a manager are excluded because INNER JOIN only returns matching rows.

Self Join for Hierarchical Queries

Self joins are useful to explore hierarchical structures. For instance, to find employees and their direct subordinates:

SELECT m.emp_name AS Manager, e.emp_name AS Subordinate
FROM employees m
LEFT JOIN employees e
ON m.emp_id = e.manager_id;

Result:

Manager Subordinate
Ananya Priya
Ananya Isha
Priya Riya
Priya Sunita
Isha NULL
Riya NULL
Sunita NULL

Explanation:

  • Every manager is listed with their direct reports.

  • Employees without subordinates show NULL.

Self Join with Conditions

You can add conditions to filter results. For example, to find employees whose manager is Ananya:

SELECT e.emp_name AS Employee
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.emp_id
WHERE m.emp_name = 'Ananya';

Result:

Employee
Priya
Isha

Explanation:

  • The query joins employees with their managers and filters where the manager name is Ananya.

Self Join for Comparison Between Rows

Self join can also compare rows in the same table. For example, consider a students table:

students

student_id student_name score
1 Ananya 85
2 Priya 90
3 Isha 85
4 Riya 70

To find students with the same score:

SELECT s1.student_name AS Student1, s2.student_name AS Student2, s1.score
FROM students s1
INNER JOIN students s2
ON s1.score = s2.score
AND s1.student_id < s2.student_id;

Result:

Student1 Student2 score
Ananya Isha 85

Explanation:

  • s1 and s2 represent two instances of the same table.

  • s1.student_id < s2.student_id prevents duplicate pairings and self-comparison.

Performance Considerations

  • Self joins can be heavy on large tables because the table is effectively scanned twice.

  • Indexing the join columns (emp_id, manager_id) significantly improves performance.

  • Avoid unnecessary self joins if the data can be retrieved with other methods like subqueries.

Common Mistakes to Avoid

  1. Not using aliases:
    Without aliases, SQL cannot distinguish between the two instances of the same table.

  2. Self-comparison without filtering:
    Can result in duplicate or unwanted matches, especially when comparing rows in the same table.

  3. Using INNER JOIN when LEFT JOIN is needed:
    LEFT JOIN ensures all rows from the main table are included even if no match exists.

When to Use a Self Join

  • To compare rows within the same table.

  • To explore hierarchical data, like managers and subordinates.

  • To find matching attributes or relationships within the same table.

  • For analytical queries such as finding duplicates, similarities, or related entities.

Summary of the Tutorial

A MySQL Self Join is a technique that allows a table to join with itself using aliases. It’s extremely useful for hierarchical relationships, row comparisons, and analytical tasks.

By mastering self joins, you can analyze data within a table in ways that aren’t possible with standard joins alone. Understanding how to use aliases, filters, and conditions is key to writing efficient self join queries.

Practice Questions

  1. Write a query to display all employees and their manager names using a self join.

  2. Using a self join, list all employees who share the same manager.

  3. Write a query to find employees without any subordinates using a self join.

  4. Using a self join, display each manager and the count of their direct subordinates.

  5. Create a query to find all pairs of students with the same score using a self join.

  6. Using a self join, list employees whose manager is “Ananya”.

  7. Write a query to find employees whose salary is higher than at least one other employee using a self join.

  8. Using a self join, display all employees along with their indirect manager (manager of their manager).

  9. Write a query to find all employees who report to the same manager as “Priya”.

  10. Using a self join, display pairs of products that have the same price from a products table.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top