Self Join

      A table can be joined to itself in a self join. In self join left and right tables are same with different alias name.If we need to create a result set that joins records in a table with other records in the same table, then we can use self join.

Example: I need to display all employee name along with their manager name.
Sample Emp table:
E_ID    DEPT_ID    E_NAME    MANAGER_ID
1          10                A                 NULL
2          20                B                 1
3          30                C                 2


Query:
       SELECT
          a1.E_NAME
           ,b2.E_NAME AS MANAGER_NAME
          FROM EMP a1
          LEFT JOIN EMP b2
          ON a1.MANAGER_ID = b2.E_ID

Output: