2,340 views
1 votes
1 votes

How the following two expressions are equal?

The LHS will remove duplicates but RHS will not.Please explain

3 Answers

Best answer
5 votes
5 votes

Both the expressions are equal.

The LHS is not natural join but conditional join, which is cross product of r and s and then projection of those tuples in the cross product that satisfy the given condition.

Consider r(A, B, C) and S(C, D)

r :

A B C
1 2 3
2 1 2

and s :

C D
3 4
1 4

and let the condition c be r.B < s.C 

So, r $\bowtie$c s will be :

r.A r.B r.C s.C s.D
1 2 3 3 4
2 1 2 3 4

An important point to note here is that unlike in natural join, conditional join will contain all the columns of cross product.

And r X s will be :

r.A r.B r.C s.A s.B
1 2 3 3 4
1 2 3 1 4
2 1 2 3 4
2 1 2 1 4

And after applying the condition r.B < s.C, we get

r.A r.B r.C s.C s.D
1 2 3 3 4
2 1 2 3 4
selected by
0 votes
0 votes

The LHS is a theta Join not a natural join, so it won't remove duplicates and the RHS is a condition enforced on Cartesian Product. 

In Conditional Join  AKA Theta Join, while performing cartesian product only the condition is evaluated.
In the RHS, first the Cartesian Product is evaluated and then the condition is enforced.

Both result in same output.

Related questions

0 votes
0 votes
0 answers
2
gatecrack asked Dec 10, 2018
481 views
1 votes
1 votes
2 answers
3
aditi19 asked May 8, 2019
1,223 views
Suppliers(sid, sname, address)Parts(pid, pname, color)Catalog(sid, pid, cost)Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham