Join operation in relational algebra and SQL solved exercises
Question:
Consider two relations R and S with
instances as follows;
|
Relation R
|
Relation S
|
|
a b c
------
1
2 3
4
5 6
7
8 9
|
c d e
------
3
4 5
7
8 9
9
0 1
5
5 5
|
How many rows would the following
operations yield for the given data?
(a) cartesian product of R and S.
(b) natural join of R and S
(c) left outer join of R and S
(d) right outer join of R and S
(e) full outer join of R and S
Solution:
|
Operation
|
No. of rows
|
How?
|
Result
|
|
Cartesian product
|
12
|
No.
of records in R multiply No. of records in S
3 * 4
= 12
|
a b c c d e
--------------
1
2 3 3 4 5
1
2 3 7 8 9
1
2 3 9 0 1
1
2 3 5 5 5
4
5 6 3 4 5
4
5 6 7 8 9
4
5 6 9 0 1
4
5 6 5 5 5
7
8 9 3 4 5
7
8 9 7 8 9
7
8 9 9 0 1
7
8 9 5 5 5
|
|
Natural join
|
2
|
Compares
the value of the common attribute between relations R and S. In our example,
attribute C is the common attribute.
If
C’s value is common for pair of tuples from both relations, then that pair
will form a new tuple in the result.
Between R and S we have only 2 tuples with
common C values.
|
a b c d e
-------------
1
2 3 4 5
7
8 9 0 1
|
|
Left outer join
|
3
|
It
is the natural join that includes all the records from left side relation (in
our case R) but not all records from right side relation (in our case S).
If
there is no tuple in right side relation that are related to some records of left
side relation, then the right side values will be NULL.
Number of records = Number of records in
right relation R
|
a b c d e
--------------
1
2 3 4 5
4
5 6 n n
7
8 9 0 1
|
|
Right outer join
|
4
|
Same
as above with inclusion of all records from right side relation (S) with NULL
values of missing left side relation (R)
Number of records = Number of records in
left relation S
|
a b c d e
-------------
1
2 3 4 5
n n 7 8 9
7
8 9 0 1
n n
5 5 5
|
|
Full outer join
|
5
|
Same
as above. Now both sides the missing records will be replaced with NULL.
2 with common C values in R and S + 1 record
in R with S values as null + 2 records in S with R values as null = 5 records
|
a b c d e
-------------
1
2 3 4 5
4
5 6 n n
n n 7 8 9
7
8 9 0 1
n n 5 5 5
|
**************
Go to Natural join in DBMS page (SQL)
Go to Equi-join in DBMS page (SQL)
result of join operation in sql
result of natural join in relational algebra
outer joins in sql and relational algebra
left and right outer joins examples
join solved exercises in dbms
how join operation is performed in sql
how join operation is performed in relational algebra
join operation exercises
No comments:
Post a Comment