How to use Self Join in SQL Server

SQL Server Self Join 
Joining the table to itself is called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table.
Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.
A self join could be an outer join or an inner join,depends on the requirement and how the query is written.

CREATE TABLE Employee (EId INT,Ename VARCHAR(15),Location VARCHAR(15));

INSERT INTO Employee 
VALUES(1,'Ravi','Hyderabad'), (2,'Rani','Bangalore'),

(3,'Johnny','Bangalore'), (4,'Mikel','Hyderabad'),

(5,'Lisa','Bangalore'), (6,'Ravi','Bangalore');

SELECT * FROM Employee;

Simple Query to display Employee details located in Hyderabad.

SELECT Eid,Ename,Location FROM Employee 
WHERE Location='Hyderabad';

Nested Query[Sub Query] to disply Employee details located  in Hyderabd.

SELECT EId,Ename,Location FROM Employee 
WHERE Location IN

(SELECT Location FROM Employee WHERE Ename='Mikel');

SELF JOIN to disply Employee details located in Hyderabad.

Joining the table to itself is called self join.Here in this example we join the table Employee as E1 to its alias E2.,the E1 and E2 are the Alias names of the same table.

SELECT E1.EId,E1.Ename,E1.Location FROM Employee E1,Employee E2

WHERE E1.Location=E2.Location AND E2.Ename='Mikel';

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------