Self-Join is a Join where we join the same table with itself. Self-Join can be sub divided into three types:

1. Inner Self Join
   
2. Outer Self Join( Left, Right and Full)
   
3. Cross Self-Join
   

Inner Self Join

There are scenarios where we have to apply self-join in order to achieve the result. For example we have Person table which consist of following columns as shown below:

create table Person

(

PERSONID INT PRIMARY KEY IDENTItY(1,1),

FIRSTNAME NVARCHAR(50),

MOTHERID int

)

So the requirement is to show the Person FirstName and MotherName. So now in order to retrieve the same we can make use of Self Join.

SELECT

PERSON.FIRSTNAME ‘STUDENT’,

COALESCE(MOTHER.FIRSTNAME,’UNDEFINED’) ‘MOTHER’

FROM PERSON PERSON

LEFT JOIN

PERSON MOTHER

ON PERSON.MOTHERID=MOTHER.PERSONID

If I check the query designer for design we can see the actual how columns are mapped to each other

So now the same table is referred as two different table and left join are applied on based on the Person Table where we are applying self join on MotherId Column to table Mother e to PersonId.

Output:

SELF INNER JOIN

Now if we want to display the MotherName of the students whose MotherId has been defined than we can make use of Self Inner Join.

SELECT

PERSON.FIRSTNAME ‘STUDENT’,

COALESCE(MOTHER.FIRSTNAME,’UNDEFINED’) ‘MOTHER’

FROM PERSON PERSON

INNER JOIN

PERSON MOTHER

ON PERSON.MOTHERID=MOTHER.PERSONID

CROSS SELF JOIN

Mapping each each records from Person table to each records of Mother table.

SELECT

PERSON.FIRSTNAME ‘STUDENT’,

COALESCE(MOTHER.FIRSTNAME,’UNDEFINED’) ‘MOTHER’

FROM PERSON PERSON

CROSS JOIN

PERSON MOTHER

ORDER BY PERSON.FIRSTNAME