CODEWITHSUNDEEP
databasejoinrelational-algebra
newbieLinuxCpp
newbieLinuxCpp

Reputation: 486

Estimating a Size of Joining a Relation with itself

I'm studying size estimation of logical query plans in order to select a physical query plan.

I was wondering what is the size of joining (natural join) a relation to itself?

e.g R(a,b) JOIN R(a,b), say total number of tuples is 100 and attributes a and b both has a distinct values of 20.

Will the join size (number of tuples in result) equal to 100?

I'm so confused!

Upvotes: 2

Views: 757

Answers (1)

AntC
AntC

Reputation: 2806

To answer the question as asked: Natural join of a relation to itself is the identity operation; you'll get exactly the tuples you started with (yes, 100 tuples in this case).

The equivalent SQL for what you ask is: SELECT R1.a, R1.b FROM R AS R1, R As R2 WHERE R1.a = R2.a AND R1.b = R2.b

This is because RA's (Natural) Join always matches by attribute name.

What could be more sensible? What's to be confused about?

Upvotes: 1

Related Questions