【SQL】UNION ALL 與UNION 的區別

SQL> select product_id,product_type_id ,name
  2  from products
  3  union all
  4  select prd_id,prd_type_id ,name
  5  from more_products order by 1;

PRODUCT_ID PRODUCT_TYPE_ID NAME                                         
---------- --------------- ------------------------------                                                         
         1               1 Modern Science                                         
         1               1 Modern Science                     
         2               1 Chemistry                
         2               1 Chemistry                            
         3               2 Supernova               
         3                 Supernova               
         4               2 Tank War              
         4               2 Lunar Landing              
         5               2 Submarine               
         5               2 Z Files               
         6               2 2412: The Return     
         7               3 Space Force 9                
         8               3 From Another Planet                            
         9               4 Classical Music              
        10               4 Pop 3        
        11               4 Creative Yell               
        12                 My Front Line

已選擇17行。

union all 返回查詢資料集的所有行，
SQL> select product_id,product_type_id ,name
  2  from products
  3  union
  4  select prd_id,prd_type_id ,name
  5  from more_products;

PRODUCT_ID PRODUCT_TYPE_ID NAME                                                                                                  
---------- --------------- ------------------------------                    
         1               1 Modern Science                   
         2               1 Chemistry              
         3               2 Supernova                              
         3                 Supernova                        
         4               2 Lunar Landing               
         4               2 Tank War                 
         5               2 Submarine                    
         5               2 Z Files               
         6               2 2412: The Return               
         7               3 Space Force 9                          
         8               3 From Another Planet                                                                    
         9               4 Classical Music
        10               4 Pop 3                                                         
        11               4 Creative Yell                                                  
        12                 My Front Line                               

已選擇15行。

UNION 返回資料集中所有非重複的行。