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:

 
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
 
 

[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.