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

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.

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

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:

Can you not try it yourself and see?

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