Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Exclamation 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. #2
    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. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    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. #4
    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. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: relation joins with itself

    Originally posted by Adrian Jones
    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. #6
    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. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697

    Re: relation joins with itself

    Originally posted by Adrian Jones
    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. #8
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up 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.
    SATHISH .

  9. #9
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    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
  •