/    /  DBMS – Natural Join

Natural Join

 

Let there be two relations. This operator considers a pair of tuples one from each relation and combines when the common attribute values in both the tuples are the same.

Let us consider the following relations: 

r1 :  STUD r2 : DEPT ———————————- ————————————

SNo SName DNo  DNo  DName

———————————- ————————————

121 xyz  21 21 CSE

123 pqr  21 22 IT

126 mnp  22 23 ECE

128 abc 22 24 ME

130 jkl  23 25 EEE

 

Example 1 : Find the result of the following SQL command.

Select *

From STUD natural join DEPT;

  Output :

  DNo  SNo SName DName

 21 121 xyz  CSE

 21 123 pqr  CSE

 22 126 mnp  IT

 22 128 abc IT

 23 130 jkl  ECE

 

Example 2 : Find the result of the following SQL command.

Select  *

From DEPT natural join FACULTY;

 

   Output :

FNo D-No   D-Name FName Qual 

522 21 CSE MM Ph.D. 

524 22 IT NN  MTech

525 22 IT VV MTech

527 23 ECE PP MTech

530 23 ECE QQ  MTech

533 24 ME  RR Ph.D.

535 24 ME  SS  Ph.D.

537 25 EEE TT  MTech

539 25 EEE WW  MTech

 

Reference

Natural Join