469,267 Members | 1,643 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

Difference between UNION and UNION ALL

rsrinivasan
221 100+
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,
May 24 '07 #1
4 76133
MMcCarthy
14,534 Expert Mod 8TB
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.
May 24 '07 #2
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
May 25 '07 #3
Union eliminate duplicates, union all doesn’t eliminate dups..

Ex:

Expand|Select|Wrap|Line Numbers
  1.  
  2. TABLE_1 :
  3.  
  4. SELECT * FROM DB2.*.Table1
  5. WHERE NUM = '400005898'                                   WITH UR;                                                                
  6. ---------+---------+---------+---------+---------+---------+---------+-        
  7. NUMBER   APPLIED_DTSTMP              EFF_DT      EXP_DT      
  8. ---------+---------+---------+---------+---------+---------+---------+-        
  9. 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  
  10.  
  11. NUMBER OF ROWS DISPLAYED IS 2                                          
  12.  
  13. TABLE_2 :
  14.  
  15. SELECT * FROM DB2.*.Table2 
  16. WHERE NUM = '400005898'                                  
  17. WITH UR;                                                               
  18. ---------+---------+---------+---------+---------+---------+---------+-
  19. NUMBER  APPLIED_DTSTMP              EFF_DT      EXP_DT      
  20. ---------+---------+---------+---------+---------+---------+---------+-
  21.         191721709  2006-05-10-11.09.39.164189  05/10/2006  02/14/1997  
  22.         191721709  2006-05-10-11.09.32.452921  05/10/2006  02/14/1997  
  23.         191721709  2002-03-14-09.46.20.512614  03/14/2002  02/14/1997  
  24.         191721709  2002-03-14-09.46.17.356447  03/14/2002  02/14/1997  
  25.         191721709  1997-10-10-23.05.28.750012  02/14/1997  02/14/1997  
  26.         191721709  1997-10-10-23.05.28.750011  02/14/1997  ----------  
  27. DSNE610I NUMBER OF ROWS DISPLAYED IS 6                                          
  28.  
  29. UNION ALL :
  30.  
  31. SELECT * FROM DB2.*.Table1
  32. WHERE NUM = '400005898'                                
  33. UNION ALL                                                            
  34. SELECT * FROM DB2.*.Table2
  35. WHERE NUM = '400005898'                                
  36. WITH UR;                                                             
  37. ---------+---------+---------+---------+---------+---------+---------
  38. NUMBER  APPLIED_DTSTMP              EFF_DT      EXP_DT    
  39. ---------+---------+---------+---------+---------+---------+---------
  40.         191721709  2006-05-10-11.09.39.164189  05/10/2006  02/14/1997
  41.         191721709  2006-05-10-11.09.32.452921  05/10/2006  02/14/1997
  42.         191721709  2006-05-10-11.09.39.164189  05/10/2006  02/14/1997
  43.         191721709  2006-05-10-11.09.32.452921  05/10/2006  02/14/1997
  44.         191721709  2002-03-14-09.46.20.512614  03/14/2002  02/14/1997
  45.         191721709  2002-03-14-09.46.17.356447  03/14/2002  02/14/1997
  46.         191721709  1997-10-10-23.05.28.750012  02/14/1997  02/14/1997
  47.         191721709  1997-10-10-23.05.28.750011  02/14/1997  ----------
  48. DSNE610I NUMBER OF ROWS DISPLAYED IS 8                               
  49.  
  50.  
  51. UNION :
  52.  
  53. SELECT * FROM DB2.*.Table1
  54. WHERE NUM = '400005898'                                
  55. UNION                                                                
  56. SELECT * FROM DB2.*.Table2
  57. WHERE NUM = '400005898'                                
  58. WITH UR;                                                             
  59. ---------+---------+---------+---------+---------+---------+---------
  60. NUMBER  APPLIED_DTSTMP              EFF_DT      EXP_DT    
  61. ---------+---------+---------+---------+---------+---------+---------
  62.         191721709  1997-10-10-23.05.28.750011  02/14/1997  ----------
  63.         191721709  1997-10-10-23.05.28.750012  02/14/1997  02/14/1997
  64.         191721709  2002-03-14-09.46.17.356447  03/14/2002  02/14/1997
  65.         191721709  2002-03-14-09.46.20.512614  03/14/2002  02/14/1997
  66.         191721709  2006-05-10-11.09.32.452921  05/10/2006  02/14/1997
  67.         191721709  2006-05-10-11.09.39.164189  05/10/2006  02/14/1997
  68. DSNE610I NUMBER OF ROWS DISPLAYED IS 6
  69.  
  70.  
Feb 29 '08 #4
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.
Oct 11 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by JasBascom | last post: by
6 posts views Thread by Ioannis Vranos | last post: by
5 posts views Thread by NAJH | last post: by
3 posts views Thread by Dalan | last post: by
2 posts views Thread by sumit.sharma | last post: by
reply views Thread by jimmyshaw83 | last post: by
1 post views Thread by RoSsIaCrIiLoIA | last post: by
73 posts views Thread by Sean Dolan | last post: by
50 posts views Thread by Mikhail Teterin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.