# Thread: relation joins with itself

1. Registered User
Join Date
Oct 2003
Posts
1

## relation joins with itself

it seems simple but...
when you join relation r with itself what is the output - relation R or something different.
any help will be greatly appreciated!
Borislava

2. Registered User
Join Date
Oct 2003
Location
Sofia
Posts
16
Depends ...
If relation between table A and A is "PK - PK" then you will see just extanded table A - table AA.
Otherwise - no one knows, data depended.
Last edited by Krastio; 10-30-03 at 05:44.

3. Registered User
Join Date
Oct 2002
Location
Posts
697
According to the relational algebra, when you join two relations that have identical headers, you're performing a set intersection. Since the intersection of a set with itself returns itself, it follows that a relation joined with itself returns itself.

In SQL you have to rename identical columns which makes a mess. I think that's what Kristio was driving at...

But, SQL is <b>not</b> the relational algebra and it doesn't even deal with proper relations. In SQL "A NATURAL JOIN A" is not a valid expression. You have to phrase it as a SELECT statement so there's really no such thing as joining a SQL table with itself.

If you write a statement of the form SELECT A1.* FROM A A1 INNER JOIN A A2 ON A1.PK = A2.PK, you'll get the same thing as A |><| A in relational algebra, namely, A.

4. Registered User
Join Date
Nov 2003
Location
Currently New York; Usually UK
Posts
4

## Re: relation joins with itself

PK-PK should produce a set of one record.

PK-FK, where the relationship is recursive (e.g. RecA parents RecB, C and D) will return a set of the immediate children.

'FK-FK' should produce a set of all records that have that attribute in common, e.g. they all belong to the same parent; they all happened on the same date; they all total 3, or whatever.

That parent table may be just implied, rather than represented by an actual table. An example of this is the set of Dates, which often has a number of related sets -- same month, same quarter, same year, etc -- where to express in an actual table all the possible combinations would be tedious, to say the least. Nonetheless, the implied data sets still exist.

5. Registered User
Join Date
Oct 2002
Location
Posts
697

## Re: relation joins with itself

PK-PK should produce a set of one record.
So, supposing relation Foo is:

Code:
```PK  A           B
1    alpha      antelope
2    beta       buzzard
3    gamma   centipede```
Can you show me the expression that will return a relation of one tuple?

6. Registered User
Join Date
Nov 2003
Location
Currently New York; Usually UK
Posts
4

## Re: relation joins with itself

Sco08y,

I'm sorry -- you are correct.

I'm thinking one record per record, so to speak, which is the same as saying the entire dataset.

7. Registered User
Join Date
Oct 2002
Location
Posts
697

## Re: relation joins with itself

Sco08y,

I'm sorry -- you are correct.

I'm thinking one record per record, so to speak, which is the same as saying the entire dataset.
Okay, that makes sense. The question was ambiguous as to exactly what kind of join was being performed, so I thought maybe you were talking about some kind of strange SQL operation.

8. Registered User
Join Date
Nov 2003
Location
Bangalore, INDIA
Posts
333

## Re: relation joins with itself

Hi,

If U join a relation R with itself , it will create a cross product. For example if the relation R has N tuples, then the Output will be 2^N Tuples.

9. Registered User
Join Date
Oct 2002
Location
Posts
697

## Re: relation joins with itself

Originally posted by satish_ct
Hi,

If U join a relation R with itself , it will create a cross product. For example if the relation R has N tuples, then the Output will be 2^N Tuples.
If you renamed all of R's attributes and joined the result against R, you would indeed get a cross product, but it would have N^2 tuples.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•