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
———————————- —————————-
- 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
———————————————————————-
- 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
———————————————————————-
- 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
Join Expressions