Well, one classic example is where you wanted to get a list of employees and their immediate managers:
select e.employee as employee, b.employee as boss
from emptable e, emptable b
where e.manager_id = b.empolyee_id
order by 1
It's basically used where there is any relationship between rows stored in the same table.
And so on...
You'd use a self-join on a table that "refers" to itself - e.g. a table of employees where managerid is a foreign-key to employeeid on that same table.
Example:
SELECT E.name, ME.name AS manager
FROM dbo.Employees E
LEFT JOIN dbo.Employees ME
ON ME.employeeid = E.managerid
SQL self-join simply is a normal join which is used to join a table to itself.
Example:
Select *
FROM Table t1, Table t2
WHERE t1.Id = t2.ID
A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.
It is useful when you want to correlate pairs of rows from the same table, for example a parent - child relationship. The following query returns the names of all immediate subcategories of the category 'Kitchen'.
SELECT T2.name
FROM category T1
JOIN category T2
ON T2.parent = T1.id
WHERE T1.name = 'Kitchen'
Source: Stackoverflow.com