Difference between Self and Equi Join in 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 a subtle difference between the 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 a must for any programmer.

By the way, If you have written INNER join using where clause then using a comparison operator as = will be known as an equijoin. Equi joins or Self-join is not a formal JOIN or part of the syntax, instead, they are a just popular way to refer to certain join examples.

One of the best examples 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 the second highest salary of employee or questions related to joining three tables in one SQL query.

In this SQL tutorial, we will learn to self-join by example while solving this SQL query. 

Btw, If you are new to SQL and don't understand fundamentals like JOINs or co-related sub-queries then I highly recommend you go through a comprehensive SQL course like The Complete SQL Bootcamp by Jose Portilla on Udemy. It's one of the best and also most affordable courses to learn SQL online.

Self Join Example in MySQL

In order to solve this query let's first see schema and data of the 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)

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 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:

Self Join vs Equi Join in SQL and MySQL

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 a 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 techniques to solve many SQL query related problems where two columns of the table contain the same type of data e.g. here emp_id and dept_id are essentially the same data.

Other SQL tutorials and examples you may like

• Difference between primary key and candidate key in table or SQL
• Difference between View and Materialized View in Database or SQL
• What is the difference between clustered and nonclustered index in SQL
• 10 ALTER command example in SQL
• 10 Oracle Interview Questions for SQL developer
• 10 SQL Query Interview Questions from Java Interviews

P.S. - If you are looking for online training/courses to learn SQL from scratch, I suggest you joining free SQL courses for beginners. It's one of the best courses to learn SQL fundamentals e.g. join, subquery, aggregate functions, window functions, grouping data, advanced filtering, and SQL query optimization.