Page 39 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes SELECT *.D * .P
1 1
FROM Department D , Project P
1 1
WHERE D .PNo = P .PNo
1 1
The result of this statement is as follows:

Dept_Mid DNo PNo (PNo) Pname
101 2 11 11 A
97 5 22 22 B
This table shows the simple join operation of two tables - only those rows are selected that
satisfied the condition. However, if we want to include those rows that do not satisfy the
condition, then we can use the concept of Outer joins.
There are three types of outer joins namely: (1) Left Outer Join, (2) Right Outer Join and (3) Full
Outer Join.
1. Left Outer Join: Left outer join lists all those rows which are common to both the tables
and also all those unmatched rows of the table which is specified at the left hand side.

Example: The application of left outer join will result is the following table.
SELECT * .D , * .P
l 1
FROM Department D , LEFT OUTER JOIN Project P
1 1
WHERE D .PNo = P .PNo.
l l
Result
Dept_Mid DNo (PNo) (PNo) Pname
101 2 11 11 A
97 5 22 22 B
120 4 33 NULL NULL
So, the left outer join resulted in a relation that have common rows from both the tables
and also the row which does not have match in the other table. The values of the attributes
corresponding to second table are NULL values.
2. Right Outer Join: Right outer join is same as the left outer join but the only difference is
the unmatched rows of second table (specified on the right hand side) are listed along with
the common rows of both the tables.
SELECT *.D , *.P
1 1
FROM Department D RIGHT OUTER JOIN Project P
1 1
WHERE D .PNo = P .PNo
1 1
Dept-mid DNo (PNo) (PNo) Pname
NULL NULL NULL 44 D
101 2 11 11 A
97 5 22 22 C

The values of attributes for the first table are declared as NULL.
3. Full Outer Join: This is same as the right outer join and left outer join but only difference
is unmatched rows of both tables are listed along with the common tows of the tables.

32 LOVELY PROFESSIONAL UNIVERSITY