MySQL: Self Join

Joining a table with itself is pretty much the same as joining the table with another table the only difference is we have to use aliases and we have to prefix each column with these aliases.

This is pretty much self-join.

Syntax

SELECT * from TableName t JOIN TableName tb ON t.columnname = tb.columnname;

So to see this in action in real-time we will be using the employees table where we will do some things.

So we will be doing the following things:

• The table has a list of employees working and who they are reporting to.
• The problem is the reporting manager is also in the list of employees and in the report_to column, there is manager id.
• So we will use self join here to match the manager id with that column and fetch the name of the manager to whom all these managers are reporting.

So let us dive into this.

Example

select * from employees e JOIN employees m ON e.reports_to = m.employee_id;

• So from the query, we can understand that we have matched the manager to whom they are reporting from their list with the employee id which matches with the report_to id.
• So in this way, it displays the self-join of the employees table with the respective matching reprots_to id.

Now to spice things up let us make this display only employee_id, first_name of the employees, and the name of the manager alone from this clumsy table.
So that we can see the clear table of employees and to whom they are reporting.

SELECT e.employee_id, e.first_name, m.first_name AS Manager from employees e JOIN employees m ON e.reports_to = m.employee_id;

So from here, we have used the following columns as prefixes using the alias we set before and named the self joined first name using AS clause as manager.

So in this way we have filtered out the manager to whom they are reporting with ease.