MySQL Self Join: INNER JOIN a Table to Itself

A self join uses the same table twice with different aliases. Here the employee table is joined to itself so each employee can be matched to the row representing that employee's manager.

INNER JOIN supplies the matching behavior; aliases such as e and m distinguish the two roles played by the same table.

INNER join SQL command is mostly used to join one table to it self. The biggest advantage of doing this is to get linking information from the same table.

The best example of INNER join will be employee table where we will keep the employee and its manager as a single record. This way by linking to the table it self we will generate a report displaying it as two linked tables. Each record will have one additional field storing the data of the manager by keeping the employee ID and we will use M_ID ( manager ID ) to link with main employee ID. This way we will link two virtual tables generated from one main table. Here is the table. You can download /copy the sql dump file to create your own MySQL table for testing.

Main table Managers Employee

id	name	m_id
1	John	2
2	Greek Tor	3
3	Alex John	`NULL`
4	Mike tour	1
5	Brain J	3
6	Ronald	3
7	Kin	4
8	Herod	3
9	Alen	2
10	Ronne	1

id	emp_name
2	Greek Tor
3	Alex John
1	John
3	Alex John
3	Alex John
4	Mike tour
3	Alex John
2	Greek Tor
1	John

id	emp_name
1	John
2	Greek Tor
3	Alex John
4	Mike tour
5	Brain J
6	Ronald
7	Kin
8	Herod
9	Alen
10	Ronne

Main Table (emp ): Table with id , name and m_id. Each employ has one unique id and one m_id ( manager id which is part of id field )

Note that we have only one table main table and other two Managers and Employee reports are generated out of the main table only.

In the table you can see every record has one manager id field known as m_id. We have used the unique id of the employee in the m_id field to mark who is the manager for the employee.

Employee Manager report

Now let us use inner join to create one report to display who is the manager of which employee. Check this SQL

SELECT e.id, e.name AS employee, m.name AS manager
FROM emp AS e
INNER JOIN emp AS m ON m.id = e.m_id
ORDER BY e.id;

id	emp_name	manager
1	John	Greek Tor
2	Greek Tor	Alex John
4	Mike tour	John
5	Brain J	Alex John
6	Ronald	Alex John
7	Kin	Mike tour
8	Herod	Alex John
9	Alen	Greek Tor
10	Ronne	John

The INNER JOIN omits Alex John because that row has no manager. Use a LEFT JOIN when you want employees without managers to remain in the result.

INNER JOIN with DISTINCT Query

To generate the manager table we have used this SQL ( List all the managers )

SELECT DISTINCT m.id, m.name AS manager
FROM emp AS e
INNER JOIN emp AS m ON m.id = e.m_id
ORDER BY m.id;

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

MySQL Self Join: INNER JOIN a Table to Itself

Employee Manager report

INNER JOIN with DISTINCT Query

Related JOIN tutorials

Subhendu Mohapatra