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

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

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 subtle difference between two. Any INNER Join with equal as join predicate is known as Equi Join. SQL Joins are fundamental concept of SQL similar to correlated and noncorrelated subqueries or using group by clause and good understanding of various types of SQL join is 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 equi join. Equi join or Self join are not a formal join or part of syntax, instead they are just popular way to refer certain join examples. One of the best example of Self Join, I have seen in SQL 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 instance 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)

In 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 example of INNER Join, SELF Join and EQUI Join at same time.

Self Join vs Equi Join

In 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 condition for comparison, it can occur in any INNER, OUTER or SELF join in SQL.

That’s all on 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 table contains same type of data e.g. here emp_id and dept_id are essentially same data.