Date:         Thu, 20 Sep 2001 10:45:25 -0400
Reply-To:     Sigurd Hermansen <hermans1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <hermans1@WESTAT.COM>
Subject:      Re: Proc Sql question
Comments: To: drgonzo3@HOTMAIL.COM

A simple example proves by exception that the two do not produce the same
results:

data testab;
  a=1;b=1;output;
  a=2;b=3;output;
  a=1;b=1;output;
run;
data testbc;
  b=1;c=2;output;
  b=3;c=1;output;
  b=2;c=4;output;
run;
proc sql;
  select * from
     (select * from testab)
      outer union corr
  (select * from testbc)
  ;
quit;
proc sql;
  select * from
     (select * from testab)
      union all
  (select * from testbc)
  ;
quit;

The OUTER UNION CORR keeps all rows from the testab and all rows from
testbc. It stacks the columns that have the same name and type (and
thus works basically the same as a SET testab testbc in a data step).

The UNION ALL also keeps all of the rows from both datasets, but it lines up
columns of the same data type and stacks columns under whatever column name
appears next in left to right order. I normally avoid using a plain UNION or
a UNION ALL unless the datasets involved have the same column structure.

You'll find a reasonably complete discussion of the various forms of SQL
joins at http://sasdocs.ats.ucla.edu/proc/zljoined.htm .

Sig

On Thu, 20 Sep 2001 00:49:20 -0700, Magnus Jansson <drgonzo3@HOTMAIL.COM>
wrote:

>Hi
>Any SQL guru out there?
>My question is:
>What is the difference between
>
>outer union corr
>
>and
>
>union all
>
>?
>
>Both seem to produce the same results for me.
>Thanx
>
>Magnus Jansson
>Sweden

One does not have to be a SQL guru to show by exception that