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
Comments: To: pchoate <paulchoate61@GMAIL.COM>
In-Reply-To:  <bf1f2a5d-2927-4e58-a1f3-cb49a9690c16@s1g2000pra.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

Paul:
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.
S


-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of pchoate
Sent: Sunday, August 24, 2008 8:46 PM
To: SAS-L@LISTSERV.UGA.EDU
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?

data a;
     do i=1 to 10;
         do j=1 to 10;
          output;
     end; end;
data b;
     do j=1 to 10;
         do k=1 to 10;
          output;
     end; end;
run;

proc sql;
     create TABLE innerjoin as
     select * from a, b where a.j=b.j;
     create TABLE naturaljoin as
     select * from a  natural join b;
quit;

Table innerjoin - I J K
Table naturaljoin - K J I

tia - Paul