Date: Mon, 25 Aug 2008 13:07:24 -0400
Reply-To: Sigurd Hermansen <SigurdHermansen@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <SigurdHermansen@WESTAT.COM>
Subject: Re: Join Order question
Content-Type: text/plain; charset="us-ascii"
Speaking for the original SAS SQLHead, I'd say that the SAS System's implementation of the natural join behaves the way it behaves. SQL does not specify ordering of attributes by default. A SELECT clause may specify a projection of attributes, but, if left undefined in the program, the SQL compiler can chose whatever order it finds convenient. The SQL compiler doesn't even have to be consistent in one situation versus another. I am sure that you know that, but I'll state it for the record.
As for why SAS appears to use one order consistently for INNER JOIN's and another for (INNER) NATURAL JOIN's, it seems natural to me that the key attributes would appear first in an unspecified projection. Putting the RHS non-key attributes first doesn't appear to have any purpose. Perhaps it's somehow related to the equally arbitrary behavior of a SAS Data step MERGE that overwrites LHS variables with same name as RHS variables.
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of pchoate
Sent: Sunday, August 24, 2008 8:46 PM
Subject: Join Order question
Here's one for the SQL heads - does anyone know why a natural join reverses the data set order that the variables are entered by into the PDV?
do i=1 to 10;
do j=1 to 10;
do j=1 to 10;
do k=1 to 10;
create TABLE innerjoin as
select * from a, b where a.j=b.j;
create TABLE naturaljoin as
select * from a natural join b;
Table innerjoin - I J K
Table naturaljoin - K J I
tia - Paul