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

Difference between self join and equi join in SQL





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

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.

5 comments:

  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.

    ReplyDelete
  2. Your query:
    select distinct e.emp_id, e.emp_name from employee e join employee m on e.emp_id=m.mgr_id;
    is equal to this another one:

    select emp_id, emp_name from employee where emp_id=mgr_id; ???

    how can i do know when i have to use a SELF JOIN or a simple query on the table??

    Thx

    ReplyDelete
    Replies
    1. Your query works in this simple example but in reality most managers are managed by someone else. Your query would probably produce one row and that would be the CEO.

      Delete
  3. excellent explanation.

    ReplyDelete

Feel free to comment, ask questions if you have any doubt.