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

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.

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.

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.

So, supposing relation Foo is:

Can you show me the expression that will return a relation of one tuple?

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.

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.