Date:         Sun, 24 Aug 2008 18:02:21 -0700
Reply-To:     Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
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; format=flowed; delsp=yes

I don't know, other than to speculate that the column order is not
defined unless you specify it, just as row order is not defined unless
you specify it.  I don't see anything in the documentation that
specifies the order when * is coded.

In the example at <http://support.sas.com/onlinedoc/913/getDoc/en/proc.hlp/a002473691.htm
 >, a natural join of two tables, one with columns X Y Z, the other
with columns X B Z, yields a result with columns, X Z B Y, which I
would not have expected.


--
Jack Hamilton
jfh@alumni.stanford.org
videtis illam spirare libertatis auram




On Aug 24, 2008, at 5:46 pm, pchoate wrote:

> 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