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)
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Doing a natural self-join is like joining on all the columns, so like joining on the primary key (as the set of all columns is certainly a superset of the primary key.) Every row of the left copy of the table will be joined to exactly one row (its copy) of the right copy of the table.
Therefore, the size of the resulting set
R NATURAL JOIN R will be exactly the same as the original
R itself (actually the situation is even better, the resulting set is exactly the original
If on the other side, you self-join on (one or more) columns that are not a superset of the primary key, the size can be anything from 0 rows up to the square of