Home » SQL & PL/SQL » SQL & PL/SQL » Simple union in oracle 10g?
Simple union in oracle 10g? [message #382088] Tue, 20 January 2009 23:02 Go to next message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
Hi I have not worked on oracle for years now.

we were using sybase before.

Recently got oracle 10g.

I heard that union is replaced by full outer join in 10g.

In my old scripts i was using Union operator to get distinct id_numbers from different tables without all the hassle of joining the tables. Is there any similar alternative in Oracle 10g.

Here is my old script code. TIA

select distinct id_number
from committee
where committee_code = '00001'

union

select distinct mailing_list.id_number
from mailing_list
mailing_list.mail_list_status_code = 'O'

union

select distinct gift.gift_donor_id
from gift
where gift.gift_account like 'Y7%'
Re: Simple union in oracle 10g? [message #382089 is a reply to message #382088] Tue, 20 January 2009 23:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I heard that union is replaced by full outer join in 10g.

Don't trust what you hear, refer to the documentation: SQL Reference

DISTINCT is useless with UNION as UNION makes a DISTINCT.

Regards
Michel
Re: Simple union in oracle 10g? [message #382093 is a reply to message #382088] Tue, 20 January 2009 23:26 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
auto wrote on Wed, 21 January 2009 06:02
I heard that union is replaced by full outer join in 10g.

In my old scripts i was using Union operator to get distinct id_numbers from different tables without all the hassle of joining the tables. Is there any similar alternative in Oracle 10g.

Seems a little contradictory - you would like to (full outer) join without using join.

But as Michel already stated, this is nonsense. UNION was used to "emulate" FULL OUTER JOIN when using Oracle outer join operator. As Oracle supports ANSI join syntax from version 9i, it is no longer necessary.

The query you provided is not that case anyway. If it gives expected results, what is the need to change it (instead of removing the unnecessary DISTINCT words)? I do not see any need for this.
Re: Simple union in oracle 10g? [message #382335 is a reply to message #382093] Thu, 22 January 2009 00:03 Go to previous message
auto
Messages: 8
Registered: January 2009
Location: aus
Junior Member
Tahnks for the helps. i tried taking off the trailing spaces around union and it works now.
Previous Topic: Prasing a text field (merged)
Next Topic: Total of a column at each page end and Grand total at the end of report.
Goto Forum:
  


Current Time: Sat Dec 03 03:43:34 CST 2016

Total time taken to generate the page: 0.08214 seconds