Date: Fri, 24 Jan 2003 15:36:39 -0500
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: Outer Joins
Content-Type: text/plain; charset="iso-8859-1"
You have a choice of unrestricted (Cartesian product), inner, left, right,
and full (outer) joins. An unrestricted join yields every possible pair of
rows in TableA and TableB (or, in SAS SQL, datasetA and datasetB):
create table TableC as select t1.*,t2.* from TableA,TableB;
(SAS SQL selects first reference to any column variable that has the
same name in A and B.)
inner join:
create table TableC as select t1.*,t2.* from TableA as t1 inner join
TableB as t2
on t1.ID1=t2.ID1 and
t1.ID2=t2.ID2 and ....
where .....;
left join:
create table TableC as select t1.*,t2.* from TableA as t1 left join
TableB as t2
on t1.ID1=t2.ID1 and
t1.ID2=t2.ID2 and ....
where .....;
right join: (substitute 'right join' for 'left join')
full join:
create table TableC as select t1.*,t2.* from TableA as t1 full outer
join TableB as t2
on t1.ID1=t2.ID1 and
t1.ID2=t2.ID2 and ....
where .....;
The MS Powerpoint 'slides' from my presentation at the last SESUG,
Structured Query Language: Structure, Logic, and Syntax, show in set logic
terms the the differences among different forms of joins. If you have an
interest, send me an e-mail and I'll reply with a zipped copy of the slide
show. The slide show will not, of course, include the lame jokes and my
comic attempts to remain lucid. You had to be there ....
Sig
-----Original Message-----
From: Doug [mailto:dheuer@EJIVA.COM]
Sent: Friday, January 24, 2003 2:49 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Outer Joins
I have a SQL query with joins and outer joins, I want to convert to SAS SQL
and can not find good documentation on doing this. Does anyone have an
example that I can look at that has outer joins and where statement?
| 
