[RBASE-L] - Re: FULL OUTER JOIN question

[Jason Kramer]

Thanks for all of your help.  That clears things up.                                   Jason Jason Kramer University Archives and Records Management 002 Pearson Hall (302) 831 - 3127 (voice) (302) 831 - 6903 (fax) Dennis McGrath wrote: Larry is right, Union is the way to go. This would have given you all the ids, except in two columns. Sorry for the misdirection. SELECT T1.idnum, T2.idnum FROM active T1 FULL OUTER JOIN inactive T2 ON T1.idnum = T2.Idnum You might also try this to see if it is faster than the plain UNION: SELECT idnum, 'A' FROM active UNION ALL SELECT idnum, 'I' FROM inactive This will give you a complete list with an indicator of which table they came from. Dennis McGrath ________________________________________ From: rbase-l@rbase.com [mailto:rbase-l@rbase.com] On Behalf Of Lawrence Lustig Sent: Wednesday, September 24, 2008 9:12 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: FULL OUTER JOIN question << Next, I used a UNION: SELECT idnum FROM active UNION SELECT idnum FROM inactive That gave me the results I was looking for. I thought that the UNION statement above was equal to the FULL OUTER JOIN. Have I misunderstood how the FULL OUTER JOIN works? Yes and no. The SELECT UNION solution is correct. I think you may be getting confused by two different uses of the word UNION in R:Base. The old UNION _statement_ would produce a new table by JOINing to other tables together. The UNION statement and the SELECT OUTER JOIN are very similar (UNION would produce a permanent table while SELECT OUTER JOIN produces a transient dataset) I think this is what you're thinking of. Since the introduction of VIEWs into R:Base, this UNION statement has been pretty much unecessary. The SELECT UNION syntax is actually quite different from the old UNION statement and from a SELECT JOIN statement. SELECT JOIN (and the old UNION statement) both produce output that is _wider_ (joined together horizontally, if you want to think of it that way). But the SELECT UNION statement produces output that is _taller_ (joined together vertically). -- Larry