Page 74 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 4: Advanced SQL

4.5.1 Inner Join Notes

This is the simplest of all join operations.
Inner joins return all rows from multiple tables where the join condition is met. There must be
a matching value in a field common to both tables. An Inner Join cannot be nested inside a Left
Join or Right Join, it creates a new result table by combining column values of two tables based
upon the join-predicate. The join condition determines whether both records are matched or
not. If there is no match found, no records is returned.
loan inner join borrower on loan.loan-no = borrower.loan-no

The expression computes the theta join of the loan and the borrower relations, with join condition
being loan.loan_no = borrower.loan_no. The attributes of the result consist of the attributes of
the left-hand-side relation followed by the attributes of the right hand side of the relation.

Notes The attribute loan _no appears twice in the result. The first occurrence is from loan,
and the second is from borrower.
Result of loan innter join borrower on loan.loan_no = borrower.loan_no

We rename the result relation of a join and the attributes of the result relation using as clause, as
shown below:
loan inner join borrower on l oan.loan_no = borrower.loan_no
As lnbr (branch, loan_no, amount, cust, cust_loan_no)
The second occurrence of loan_no has been renamed as cust_loan_no. the ordering of the attributes
in the result of the join is important for renaming.

LOVELY PROFESSIONAL UNIVERSITY 67