Difference between UNION and UNION ALL

[rsrinivasan]

Hi,
What is the difference between UNION and UNION ALL.
I refered some document. But i did not get any clear idea on that.

Thanks,

[MMcCarthy]

Hi,
What is the difference between UNION and UNION ALL.
I refered some document. But i did not get any clear idea on that.

Thanks,

The main difference is that UNION will eliminate any duplicate rows returned by the various select statements being unioned whereas UNION ALL will not. It will return all records even duplicates.

[sandhya rani]

Dear Srinivas,
The union and union all are set operators. The main diffrence in btw Union and Union all is ,The Union will Display all rows selected my either queries but not duplicates where as union all will display all the rows including duplicates.
Its Better to Refer IVAN BAYROSS book u will find much information in that book.

regards
sandhya

[kaviarasu5]

Union eliminate duplicates, union all doesn’t eliminate dups..

Ex:

[code=oracle]

TABLE_1 :

SELECT * FROM DB2.*.Table1
WHERE NUM = '400005898' WITH UR;
---------+---------+---------+---------+---------+---------+---------+-
NUMBER APPLIED_DTSTMP EFF_DT EXP_DT
---------+---------+---------+---------+---------+---------+---------+-
191721709 2006-05-10-11.09.39.164189 05/10/2006 02/14/1997 191721709 2006-05-10-11.09.32.452921 05/10/2006 02/14/1997

NUMBER OF ROWS DISPLAYED IS 2

TABLE_2 :

SELECT * FROM DB2.*.Table2
WHERE NUM = '400005898'
WITH UR;
---------+---------+---------+---------+---------+---------+---------+-
NUMBER APPLIED_DTSTMP EFF_DT EXP_DT
---------+---------+---------+---------+---------+---------+---------+-
191721709 2006-05-10-11.09.39.164189 05/10/2006 02/14/1997
191721709 2006-05-10-11.09.32.452921 05/10/2006 02/14/1997
191721709 2002-03-14-09.46.20.512614 03/14/2002 02/14/1997
191721709 2002-03-14-09.46.17.356447 03/14/2002 02/14/1997
191721709 1997-10-10-23.05.28.750012 02/14/1997 02/14/1997
191721709 1997-10-10-23.05.28.750011 02/14/1997 ----------
DSNE610I NUMBER OF ROWS DISPLAYED IS 6

UNION ALL :

SELECT * FROM DB2.*.Table1
WHERE NUM = '400005898'
UNION ALL
SELECT * FROM DB2.*.Table2
WHERE NUM = '400005898'
WITH UR;
---------+---------+---------+---------+---------+---------+---------
NUMBER APPLIED_DTSTMP EFF_DT EXP_DT
---------+---------+---------+---------+---------+---------+---------
191721709 2006-05-10-11.09.39.164189 05/10/2006 02/14/1997
191721709 2006-05-10-11.09.32.452921 05/10/2006 02/14/1997
191721709 2006-05-10-11.09.39.164189 05/10/2006 02/14/1997
191721709 2006-05-10-11.09.32.452921 05/10/2006 02/14/1997
191721709 2002-03-14-09.46.20.512614 03/14/2002 02/14/1997
191721709 2002-03-14-09.46.17.356447 03/14/2002 02/14/1997
191721709 1997-10-10-23.05.28.750012 02/14/1997 02/14/1997
191721709 1997-10-10-23.05.28.750011 02/14/1997 ----------
DSNE610I NUMBER OF ROWS DISPLAYED IS 8


UNION :

SELECT * FROM DB2.*.Table1
WHERE NUM = '400005898'
UNION
SELECT * FROM DB2.*.Table2
WHERE NUM = '400005898'
WITH UR;
---------+---------+---------+---------+---------+---------+---------
NUMBER APPLIED_DTSTMP EFF_DT EXP_DT
---------+---------+---------+---------+---------+---------+---------
191721709 1997-10-10-23.05.28.750011 02/14/1997 ----------
191721709 1997-10-10-23.05.28.750012 02/14/1997 02/14/1997
191721709 2002-03-14-09.46.17.356447 03/14/2002 02/14/1997
191721709 2002-03-14-09.46.20.512614 03/14/2002 02/14/1997
191721709 2006-05-10-11.09.32.452921 05/10/2006 02/14/1997
191721709 2006-05-10-11.09.39.164189 05/10/2006 02/14/1997
DSNE610I NUMBER OF ROWS DISPLAYED IS 6

[/code]

[Rathinakumar]

The common answer is already available to you. One more thing you should definitely know is .. UNION ALL is faster as it doesnt involve sorting rows etc. Mere UNION is too costly as it has to sort all the results to remove redundant rows. HTH.