A SELF-JOIN joins a table with itself. This is useful when you want to compare values within the same column. SELF-JOIN can be classified under inner-join, outer-join, and cross-join.

Let's understand it with an example. Suppose you have an employee table which contains data for employee as well as employees manager within the same column. If you want to find out the employee along with his manager, you need to use SELF-JOIN.
       
CREATE TABLE Employee(
	EmpID INT PRIMARY KEY,
	EmpName NVARCHAR(50),
	ManagerID INT
)

GO

INSERT INTO Employee
SELECT 1, 'Kapil', 3 UNION ALL
SELECT 2, 'Rahul', 3 UNION ALL
SELECT 3, 'Shyam', NULL UNION ALL
SELECT 4, 'Ramesh',2 UNION ALL
SELECT 5, 'Satish',2 UNION ALL
SELECT 7, 'Vipin',2

GO

SELECT * FROM Employee
Result:-



Now we will use inner join query to find the employees along with their managers’ names. 
SELECT e1.EmpName [Employee Name], e2.EmpName AS [Manager Name]
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmpID
Result:-

This inner SELF-JOIN returns only those employees who have a manager. However, it's not returning top manager of the company. The reason is, ManagerID for employee "Shyam" is NULL, Hence INNER JOIN excluded it from the result set based on joining criteria (e1.ManagerID = e2.EmpID). Let's convert INNER JOIN TO LEFT OUTER JOIN and see the result.
         
SELECT e1.EmpName EmployeeName, ISNULL(e2.EmpName, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT OUTER JOIN Employee e2
ON e1.ManagerID = e2.EmpID
As LEFT OUTER JOIN returns non-matching rows from left table + matching rows from both table so now in result set you can see top manager record also. So we have seen how SELF-JOIN work with INNER JOIN and OUTER JOIN. In the same way you can use it with CROSS JOIN.

Result:-
           
SELECT e1.EmpName EmployeeName, ISNULL(e2.EmpName, 'Top Manager') AS ManagerName
FROM Employee e1
CROSS JOIN Employee e2
It returns the Cartesian product of the employee table with itself.