Page 75 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes 4.5.2 Natural Join

Natural join combines two tables based on their common columns i.e. columns with the same
name. Therefore join condition is hidden and dependant on table structures at runtime. This
obviously creates potential future danger-as soon as table structure changes, result can become
unpredictable yet syntactically correct it offers a further specialization of equi-joins. The join
predicate arises implicitly by comparing all columns in both tables that have the same column-
name in the joined tables. The resulting joined table contains only one column for each pair of
equally-named columns.
As previously noted in RELATIONAL ALGEBRA, this operation when performed, forces an
equality on attributes, which are common in the relation specified. If we take a natural join of
borrower and loan, then equality is forced on the attribute loan_no. loan natural join borrower
The only attribute common to loan and borrower is loan_no. the result of the expression is
similar to the result of the inner join except that the attribute loan_no appears only once in the
result of the natural join.

4.5.3 Left Outer Join

In left outer join: rows satisfying selection criteria from both joined tables are selected as well
as all remaining rows from left joined table are being kept along with Nulls instead of actual
right joined table values. or we can say it returns all the values from the left table, plus matched
values from the right table (or NULL in case of no matching join predicate). If the right table
returns one row and the left table returns more than one matching row for it, the values in the
right table will be repeated for each distinct row on the left table.
The LEFT OUTER JOIN expression is written as follows:
loan left outer join borrower on loan.loan_no = borrower.loan_no

4.5.4 Full Outer Join

The full outer join type is a combination of the left and right outer-join types. After the result of
the inner join is computed, tuples form the left-hand-side relation that did not match with any
from the right- hand-side are extended with nulls and are added to the result. Similarly, tuples
from the right-hand-side relation that did not match with any tuples form the left-hand side
relation are also extended with nulls, and are added to the result.

loan full outer join borrower using (loan_no)

68 LOVELY PROFESSIONAL UNIVERSITY