I have two tables, one with active records and one with inactive
records, and the columns in each table have the same name. They look
like this:
ACTIVE INACTIVE
idnum fname idnum fname
1 John 3 Mary
2 Diego 6 Zhou
5 George 4 Jennifer
I want to get a list of all idnums, active and inactive, like this:
idnum
------
1
2
3
4
5
6
I kept trying to use a FULL OUTER JOIN like this:
SELECT idnum FROM active FULL OUTER JOIN inactive ON idnum
I kept getting just idnums from the first table (active in this case),
and none from the second. I tried many different variations with
different ON clauses, but I could not get results form the second table.
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?
Thanks,
Jason
--
Jason Kramer
University Archives and Records Management
002 Pearson Hall
(302) 831 - 3127 (voice)
(302) 831 - 6903 (fax)
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
