[MySQL] What is SELF JOIN?

 

 

 

What is MySQL SELF JOIN?

MySQL을 공부하며 제일 헷갈리는게 조인인데 (left join, right join, inner join 등등등) SELF JOIN이라는 놈도 있다ㅋㅋ SELF JOIN이란 무엇인가 . .  쉽게 말하면 자기 자신을 조인하는 놈이다. 여기서 생기는 의문

The most confusing thing studying MySQL is JOIN (including left join, right join, inner join, etc.). There is also a thing called SELF JOIN. Then, "What is SELF JOIN??". Simply put, It is the one who joins itself literally. The question arises here:

 

 

joins itself?

I'll provide an example using the table below.

---

Example of SELF JOIN, MySQL

TABLE A

ID	NAME	SALARY	ID being managed
1	Self kim	5000	3
2	Join kim	4000	4
3	Inner Kim	3000	NULL
4	Right Kim	5000	NULL

In this example, ID1 manages ID3 and ID2 manages ID4. At this time what should we do to find a case where the managed person makes better money than the manager? To solve this problem, we need to create a new table and join around the 'ID being managed'. Because this is the criterion for determining a and b to compare.

SELECT * FROM A
INNER JOIN A AS Manager ON A.ID to be managed = Manager.ID

If you do this, the people on the left will be the ones being managed, and the people on the right will be the managers, like this.

 

ID	NAME	SALARY	ID being managed	ID	NAME	SALARY	ID being managed
1	Self Kim	5000	3	3	Inner Kim	3000	null
2	Join Kim	4000	4	4	Right Kim	5000	null

 

The two of them are met by making a declaration, INNER JOIN A AS Manager ON A.ID being managed = Manager.ID. Now, as the question asked, how do you find that the manager makes more than the manager?: Instead of "*" in "SELECT", we should put "NAME" in it. Then, declare below.

INNER JOIN A AS Manager ON A.ID being managed = Manager.ID
WHERE A.SALARY > Manager.SALARY