Difference between Self and Equi Join in SQL - INNER Join example MySQL

The main difference between Self Join and Equi Join is that In Self Join we join one table to itself rather than joining two tables. Both Self Join and Equi Join are types of INNER Join in SQL, but there is the subtle difference between two. Any INNER Join with equal as join predicate is known as Equi Join. SQL Joins are the fundamental concept of SQL similar to correlated and noncorrelated subqueries or using group by clause and a good understanding of various types of SQL join is the must for any programmer. By the way, If you have written INNER join using where clause than using comparison operator as = will be known as an equijoin. Equi join or Self join are not a formal JOIN or part of the syntax, instead they are a just popular way to refer certain join examples.

One of the best example of Self Join, I have seen in an SQL query Interview questions is "How do you find all Employees who are Managers in Employee table", which is commonly asked along with another popular question how to find second highest salary of employee or questions related to join three tables in one SQL query. In this SQL tutorial, we will learn self-join by example while solving this SQL query.

Self Join Example in SQL

In order to solve this query let's first see schema and data of Employee table.

mysql> select * from employee;
| emp_id | emp_name | dept_id | salary | mgr_id |
|    103 | Jack     |       2 |   1400 |    104 |
|    104 | John     |       2 |   1450 |    104 |
|    105 | Johnny   |       3 |   1050 |    104 |
|    108 | Alan     |       3 |   1150 |    104 |
|    106 | Virat    |       4 |    850 |    105 |
|    107 | Vina     |       4 |    700 |    105 |
|    109 | joya     |       4 |    700 |    105 |
7 rows in set (0.00 sec)

In above  table all employees who are managers has there emp_id as mgr_id in other employees and by using SELF JOIN  i.e. join two instances of employee table and comparing, we can find all employees who are managers. Here is the SELECT query example using self-join:

mysql> select distinct e.emp_id, e.emp_name from employee e join employee m on e.emp_id=m.mgr_id;
| emp_id | emp_name |
|    104 | John     |
|    105 | Johnny   |
2 rows in set (0.00 sec)

difference between Self Join vs Equi Join MySQL exampleIn this example of self join, we have joined employee table to itself by using two table aliases e and m. We have also used distinct keyword to remove duplicates here. You can also say this is an example of EQUI JOIN because in join predicate we have used = or equal condition. In fact, this one is an example of INNER Join, SELF Join, and EQUI Join at the same time.

If you are confused about syntax of all possible join in SQL, here is a handy diagram:

Difference between self join and equi join in SQL

Self Join vs Equi Join
In the short major difference between Self Join and Equi Join in SQL is that Self Join requires only one table while most of Equi join is condition used in join predicate. Since Equi Join is based on the condition for comparison, it can occur in any INNER, OUTER or SELF join in SQL.

That’s all on the difference between Self Join and Equi Join in SQL. Self Join is one of the important technique to solve many SQL query related problem where two columns of the table contain the same type of data e.g. here emp_id and dept_id are essentially same data.

Other SQL tutorials and examples you may like


  1. self join is quite useful when you want to join two instances of same table. Like in your Employee schema, how do you write query to print name of employee and their manager? You can do this using self join as shown below :

    SELECT e.emp_name, m.emp_name as manager FROM Employee where e.emp_id = m.mgr_id;

    Equi join is about condition, if your WHERE clause or ON JOIN condition uses = equal operator it's known as equi join, but if you use != or less than, greater than or any other condition, it will not call equi join.