Home » SQL & PL/SQL » SQL & PL/SQL » Union Vs Union All
Union Vs Union All [message #200523] Tue, 31 October 2006 04:29 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
I have a query , in which there are multiple duplicate records. when it is fired , the results shows ok

but when this query is embeded with UNION/Union all clause, then the duplicate records just filtered out.

But I want to get all the duplicate rows also with use of any one of these.

Is there way to extarct everything

Re: Union Vs Union All [message #200524 is a reply to message #200523] Tue, 31 October 2006 04:32 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Can you cross check that once again with your results?

UNION will be displaying only distinct records that too in sorted order.

UNION ALL will be displaying all the records.

By
Vamsi.
Re: Union Vs Union All [message #200527 is a reply to message #200524] Tue, 31 October 2006 04:44 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
yes , i have verified, the query return correct when they run alone.

Re: Union Vs Union All [message #200529 is a reply to message #200527] Tue, 31 October 2006 04:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The previous poster is correct - a UNION ALL will return the entire results set, and will not discard any rows.
Re: Union Vs Union All [message #200530 is a reply to message #200527] Tue, 31 October 2006 04:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Sanjit,
I really understand your concern.
Quote:

but when this query is embeded with UNION/Union all clause, then the duplicate records just filtered out.


Quote:

yes , i have verified, the query return correct when they run alone.



Please elaborate.

By
Vamsi.
Re: Union Vs Union All [message #200533 is a reply to message #200530] Tue, 31 October 2006 05:02 Go to previous messageGo to next message
sanjit
Messages: 65
Registered: November 2001
Member
I guess you are correct.
Toad is returing all rows with Union All. There is something wrong in SQL*plus report which does nor printing duplicate rows...
Re: Union Vs Union All [message #200634 is a reply to message #200533] Tue, 31 October 2006 10:46 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sanjit,

Are you telling us that TOAD correctly processes an UNION ALL statement and that SQL*Plus does not?

I find that hard to believe. Couldn't it be that there is some additional code in the SQL*Plus statement, like a group by?

Regards,
Sabine
Previous Topic: SQL QUERY
Next Topic: Need SQL Help Please
Goto Forum:
  


Current Time: Sun Dec 04 22:56:35 CST 2016

Total time taken to generate the page: 0.08910 seconds