Self Join with an example in sql server

There are 3 different types of joins available in sql server, and they are
1. Cross Join 
2. Inner Join or Join 
3. Outer Join

Outer Join is again divided into 3 types as shown below.
1. Left Outer Join or Left Join 
2. Right Outer Join or Right Join 
3. Full Outer Join or Full Join 

Self join means joining a table with itself. We can have an inner self join or outer self join

We use Self Join, if we have a table that references itself. For example, In the Employee Table below MANAGERID column references EMPLOYEEID column. So the table is said to referencing itself. This is the right scenario where we can use Self Join. Now I want to write a query that will give me the list of all Employee Names and their respective Manager Names. In order to achieve this I can use Self Join. In the Table below,Raj is the manager for Pete,Prasad and Ben. Ravi is the manager for Raj and Mary. Ravi does not have a manager as he is the president of the Company.The query below is an example of Self Join. Both E1 and E2 refer to the same Employee Table. In this query we are joining the Employee Table with itself.  SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME] FROM EMPLOYEE E1  INNER JOIN EMPLOYEE E2  ON E2.EMPLOYEEID =E1.MANAGERID If we run the above query we only get 5 rows out of the 6 rows as shown below. Inner Self Join If we want to get all the rows then we can use LEFT OUTER JOIN as shown below. SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME] FROM EMPLOYEE E1  LEFT OUTER JOIN EMPLOYEE E2  ON E2.EMPLOYEEID =E1.MANAGERID Left Outer Self Join Let us now slightly modify the above query using COALESCESELECT E1.[NAME],COALESCE(E2.[NAME],'No Manager') AS [MANAGER NAME] FROM EMPLOYEE E1  LEFT JOIN EMPLOYEE E2  ON E2.EMPLOYEEID =E1.MANAGERID If we execute the above query the output will be as shown in the image below. This is how COALESCE can be used. Left Outer Self Join with COALESCE