Join relations

Join relations – The concept of a joined table (or joined relation) was incorporated to permit users to specify a table resulting from a join operation in the FORM clause of a query. In addition to providing the basic Cartisean product mechanism for joining tuples of relations provided by the earlier versions of SQL. SQL- 92 also provides various other mechanisms for joining relations, including condition joins, and natural joins, as well as various forms of outer joins. These additional operations are used as sub query expressions in the from clause. This construct may be easier to comprehend mixing together the entire select clause and join conditions in the where clause.

Inner join

This is the simplest of all join operations.
Loan inner join borrower on loan.loan-no = borrower.loan-no
The expression computes the theta join of the loan and 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. Note that the attribute loan_no appears twice in the result. The first occurrence is from loan, and the second is from borrower.

Br_name
Loan_no
Loan_ammount
K R CRCLE
KL1
6000
SARASWATHIPURAM
SL1
7000
K R CIRCLE
KL2
4000

 

Loan relation
Cust_name
Loan_no
Padma
GL1
Vijaya
SL1
Shreya
Kl2
Borrower relation
Br_name
Loan_no
Loan_amount
Cust_name
Loan_no
SARASWATHIPURAM
SL1
7000
Vijaya
SL1
K R CIRCLE
KL2
4000
Shreya
KL2
Result of loan inner 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 loan.loan_no= borrower.loan_no
As Inbr(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.

Natural join

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.

Br_name
Loan_no
Loan_amount
Cust_name
SARASWATHIPURAM
SL1
7000
Vijaya
K R CIRCLE
KL2
4000
Shreya

Left outer join

The left outer join is computed as follows. First the result of the inner join is computed as before. Then, for every tuple t in left-hand-side relation loan that did not match any tuple in the right-hand-side relation borrower in the inner join, a tuple r is added to the result of the join as follows. The attribute of tuple r that are derived from the left-hand-side relation are filled in with the values from tuple t, and the remaining attributes of r are filled in with the null values. The LEFT OUTER JOIN expression is written as follows.
Loan left outer join borrower on loan.loan_no= borrower.loan_no

Br_name
Loan_no
Loan_amount
Cust_name
Loan_no
SARASWATHIPURAM
SL1
7000
Vijaya
SL1
K R CIRCLE
KL2
4000
Shreya
KL2
K R CIRCKE
KL1
6000
Null
Null

Join types and conditions

Usually outer join expressions are used in from clause, they can be used anywhere that a relation can be used. Each of the variants of the join operations in SQL-92 consists of a join type and a join condition. The join condition defines which tuple sin the two relations match on what attributes are present in the result of the join. The join type defines how tuples in each relation that what do not match and what attribute are present in the result of the join. The join type defines how tuples in each relation that do not match any tuple in the other relation. The first join type is the inner join, and the other three types of outer joins. The three join conditions are the natural join and the on condition and the using condition.

The use of join condition is mandatory for outer joins, but is optional for inner joins (if it is omitted, a cartisean product results) syntactically the key word natural appears before the join type, whereas the on and using conditions appear at the end of the join expression. The Keyword inner and outer is optional.

Join types

Inner join
Left outer join
Right outer join
Full outer join

Natural join ( On<predicate>)Using(A1, A2,….An)

The meaning of the join condition natural, in terms of which tuples from the two relations match, is straightforward. The ordering of the attributes in the result of natural jin is as follows. The join attributes (that is, the attributes comn on the both relations) appear first, in the order in which they appear in the left-hand-side relation. Next come all no join attributes of the left-hand-side relation.
The right outer join type is symmetric to the left outer join. Tuples from the right-hand-side relation that do not match any tuple in the left-hand-side relation are padded with null and are added to the result of the right outer join.
The following expression is an example of combining the natural join condition with the right outer join type.
Loan natural right outer join borrower.
The result of the expression is as follows.
Br_name
Loan_no
Loan_amount
Cust_name
SARASWATHIPURAM
SL1
7000
Vijay
K R CIRCLE
KL2
4000
Shreya
Null
GL1
Null
Padma
The attributes of the result are defined by the join type is a natural join; loan_no appears only once. The first two tuples in the result are from the inner natural join of loan and borrower. The tuple (padma, GL1) from the right-hand-side relation does not match any tuple from the left-hand-side relation loan in the natural join.
The join condition using (A1, A2,…. An) is similar to the natural join condition, except that the join attributes   A1, A2,…. An  rather than all attributes that are common to both relations. The attributes   A1, A2,…. An  must consist of only attributes that are common to both relations, and they appear only once in the result of the join.

Full outer join

The full outer join type is a combination of the left and right outer-join type. After the result of the inner join is computed, tuples from 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 from the left-hand-side relation are also extended with nulls, and are added to the result.
Loan full outer join borrower using (loan_no)
The result of the expression is as follows:

Br_name
Loan_no
Loan_amount
Cust_name
SARASWATHIPURAM
SL1
7000
Vijay
K R CIRCLE
KL2
4000
Shreya
Null
GL1
Null
Padma
K R CIRCLE
KL1
6000
Null
Find all customers who have either an account or a loan (but not both) at the bank
Select cust_name
From ( depositor natural full outer join borrower)
Where account_no is null or loan_no is null

Leave a Reply

Your email address will not be published.