SELF JOIN

 

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

 


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.