Results 1 to 5 of 5

Thread: Natural Join.

  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Natural Join.

    Dear Guys,
    We know that natural join will generate only one common column combined with all rest columns. But how about if I join the same table by natural join with itself? i.e.

    A table
    x y z (column names)
    1 2 3
    1 3 2

    then A (natural join) A

    what's the result???

    thx,

    Neil

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Natural Join.

    A natural join joins on all columns that are common to both tables. In the case of "A natural join A" this is every column. The result will be all the rows of A where all columns are NOT NULL. Each column will be returned twice of course:

    select * from A natural join A
    x y z x y z (column names)
    1 2 3 1 2 3
    1 3 2 1 3 2

    In a "true RDBMS" the result should be identical to A.

  3. #3
    Join Date
    Feb 2004
    Posts
    13

    Re: Natural Join.

    Dear,
    Actually, I got my own answer already, just don't know it's right or not. My own answer is
    xyz
    123
    132
    123
    132
    (but not sure)
    I think the one you said is "cross-product," the column names should be
    x y z x1 y1 z1

    Thx,

    Neil

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Natural Join.

    No, your answer is wrong. That is the result of:

    select * from a
    UNION ALL
    select * from a;

    I don't have access to a DBMS that supports the NATURAL JOIN syntax at the moment, but it works like this:

    PHP Code:
    SQLselect from dept;

        
    DEPTNO DNAME          LOC
    ---------- -------------- -------------
            
    10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON

    SQL
    select from dept d1dept d2
      2  where d1
    .deptno d2.deptno
      3  
    and d1.dname d2.dname
      4  
    and d1.loc d2.loc;

        
    DEPTNO DNAME          LOC               DEPTNO DNAME          LOC
    ---------- -------------- ------------- ---------- -------------- -------------
            
    10 ACCOUNTING     NEW YORK              10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS                20 RESEARCH       DALLAS
            30 SALES          CHICAGO               30 SALES          CHICAGO
            40 OPERATIONS     BOSTON                40 OPERATIONS     BOSTON 
    Can you not try it yourself and see?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Natural Join.

    Actually, my earlier answer wasn't quite right. The common columns in a natural join only appear once in the result, so the result will be:

    select * from A natural join A
    x y z (column names)
    1 2 3
    1 3 2

    i.e. identical to A in this case. However if A contained:

    x y z
    1 2 3
    1 3 2
    2 3 -
    - 3 4

    (where "-" represents NULL) then the result would not be indentical to A, it would be:

    x y z
    1 2 3
    1 3 2

Posting Permissions

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