Date:         Mon, 25 Aug 2008 14:35:57 -0700
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      Re: Join Order question
In-Reply-To:  A<62432006F5965C42BAEC4EA29286EE0507AB2D2D93@EX-CMS01.westat.com>
Content-Type: text/plain; charset="us-ascii"

Thanks Sig & Jack -

I was simplifying some SQL code I use with natural join syntax and was
surprised to see the output variables reversed - I expected the usual
ordering of inner joins - in my use they always loaded the PDV like the
data step - first in are first out, unless specified.  I can see why the
key would wind up first in a natural join, but I was surprised that the
variables from the left dataset come out after those from the right
dataset.

Thought it was worth a mention here.

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sigurd Hermansen
Sent: Monday, August 25, 2008 10:07 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Join Order question

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