/    /  DBMS – Join Expressions

 Join Expressions

 

We already know how to combine two tables into one. Let’s take an example to see how it was written before and also how we can use it using the join expressions.

 

Example: Write SQL query to combine STUD and DEPT relations

 

   Select *

   From STUD, DEPT

   Where STUD.DNo = DEPT.DNo ;

 

The same query also can be written as :

  

   Select *

   From STUD join DEPT

 on STUD.Dno = DEPT.DNo ;

 

Outer Join 

There are three categories of outer join.

Let’s take the table below and understand the three categories.

R1 :  STUD  R2 :     COURSE

SNo SName CNo   CNo   CName

121 xyz 61 61               BMS

123 pqr null 62               DAA

126 mnp 65 64               SE

128 abc  67 65  CO

130 jkl null 67  OS

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

 

  1. a) Natural Left Outer Join

The result of this operation retains all the tuples in the left relation. If any tuple from right relation is not available with a common value, those attributes are filled with Null values.

Example-1 : Find the output of the following SQL query.

 

   Select  *

   From STUD natural left outer join COURSE ;

Output :

SNo SName CNo  CNo   CName

121 abc  61 61 DBMS

123 def  null null null

126 ghi  65   65 CO

128 jkl  67 67 OS

130 mno  null null null

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

 

 

  1. b) Natural Right Outer Join

The result of this operation retains all the tuples in the right relation. If any tuple from the left relation is not available for common attributes, those attributes are filled with Null values.

Example-2 : Find the output of the following SQL query.

 

   Select  *

   From STUD natural right outer join COURSE ;

Output :

SNo SName CNo  CNo   CName

121 abc  61 61 DBMS

null null null   62 DAA

null null null 64 SE

126 ghi  65 65 CO

128 jkl  67 67 OS

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

 

  1. a) Full Outer Join :

This operation is the combination of both ‘natural left outer join’ and ‘natural right outer join’.

Example-3 : Find the output of the following SQL query.

 

   Select  *

   From STUD full outer join COURSE ;

Output :

SNo SName CNo  CNo   CName

121 abc  61 61 DBMS

null null null 62 DAA

123 def  null null null

null null null   64 SE

126 ghi  65   65 CO

128 jkl  67 67 OS

130 mno  null null null

 

Example-4 : Find all the students who have not taken a course.

 

 Select  SNo, SName

 From STUD natural outer join COURSE

 Where STUD.CNo is null ;

Output :

   SNo SName

 123           def

 130 mno 

 

Reference 

 Join Expressions