Re: Diff. between UNION and UNION ALL

From: Martyn Cavett <cavett_at_globalnet.co.uk>
Date: 1997/03/06
Message-ID: <01bc25dd$22c70640$7c567ec2@cavett.globalnet.co.uk>#1/1

Naren Chintala <naren_at_mink.att.com> wrote in article <33173071.8F9_at_mink.att.com>...
> Hi,
>
> What's the difference between UNION and UNION ALL (in a SELECT
> statement). I don't find any info on this in the Oracle manuals. Would
> someone explain this with a simple example.
>
> TIA
>
> Naren

---

Naren -

A ordinary UNION implicity removes duplicate rows returned from the 2 (or more) select lists in a UNION statement.

A UNION ALL returns all rows from the statement, not removing duplicates.

FOR EXAMPLE: Table t1 (name VARCHAR2(30)) Table t2 (name VARCHAR2(30))                                 

t1 data: Charlie				t2 data:	Charlie
	Karen					William
	Bill					Sarah

SELECT name				SELECT name
FROM    t1				FROM    t1
UNION					UNION ALL
SELECT name				SELECT name
FROM    t2				FROM    t2
ORDER BY 1;				ORDER BY 1;

would return:				would return:

NAME					NAME
---------					----------
Bill					Bill
Charlie					Charlie
Karen					Charlie
Sarah					Karen
William					Sarah
					William

5 rows returned				(all) 6 rows returned 

(I hope the formatting of the above didn't get too scrambled in transmission)

Hope this answers your question satisfactorily.

Regards from

Martyn Cavett
home: cavett_at_globalnet.co.uk
work: Martyn.Cavett_at_gtplc.com

>
  Received on Thu Mar 06 1997 - 00:00:00 CST