What is SELF JOIN and when would you use it?
You use a self join when a table references data in itself.
E.g., an Employee
table may have a SupervisorID
column that points to the employee that is the boss of the current employee.
To query the data and get information for both people in one row, you could self join like this:
select e1.EmployeeID,
e1.FirstName,
e1.LastName,
e1.SupervisorID,
e2.FirstName as SupervisorFirstName,
e2.LastName as SupervisorLastName
from Employee e1
left outer join Employee e2 on e1.SupervisorID = e2.EmployeeID
What is a self join for? (in english)
The reason why the employee-manager example is so common, is because it hits the nail on the head. A self join on a table looks for pairs of rows, like any join, but with both rows coming from the same table. Nothing special really.
Understanding Order of ON Clause in Self-Joins (SQL)
The functionally the order doesn't matter (so, 'ON e.ManagerId = m.Id' is the same as 'ON m.Id = e.ManagerId').
What you are doing here is joining on different columns, which represent different things.
In the incorrect query, you are saying "the managers managerID is the same as the employees ID", which isn't true. Managers (as you've got it in your table) don't have managers themselves.
What you've essentially done is inverse the join. If you were to swap your sign around in you where
statement, so WHERE e.Salary > m.Salary
to WHERE e.Salary < m.Salary
you'd get the same answer as your correct query
Explanation of self-joins
You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join.
Suppose you have two tables:
Table emp1
Id Name Boss_id
1 ABC 3
2 DEF 1
3 XYZ 2
Table emp2
Id Name Boss_id
1 ABC 3
2 DEF 1
3 XYZ 2
Now, if you want to get the name of each employee with his or her boss' names:
select c1.Name , c2.Name As Boss
from emp1 c1
inner join emp2 c2 on c1.Boss_id = c2.Id
Which will output the following table:
Name Boss
ABC XYZ
DEF ABC
XYZ DEF
Related Topics
SQL Server:Dynamic Pivot Over 5 Columns
Does SQLite3 Not Support Foreign Key Constraints
Mixing Implicit and Explicit Joins
Top N Records Per Group SQL in Access
Is There an Alternative to Top in MySQL
Inner Join VS Natural Join VS Using Clause: Are There Any Advantages
How to Find the Employee with the Second Highest Salary
Search Text in Stored Procedure in SQL Server
Option (Recompile) Is Always Faster; Why
How to Find Which Tables Reference a Given Table in Oracle SQL Developer
Run All SQL Files in a Directory
SQL Server Select into Existing Table
Sqlite Database Default Time Value 'Now'
SQL - Many-To-Many Table Primary Key