【SQL 學習】INTERSECT,MINUS ,

SQL> select product_id,product_type_id ,name
  2  from products
  3  intersect               ---返回兩查詢結果共有的資料行
  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                                   

SQL> select product_id,product_type_id ,name
  2  from products
  3  minus     ----返回第一個查詢的結果減去第二個查詢的結果後剩餘的行。
  4  select prd_id,prd_type_id ,name
  5  from more_products;

PRODUCT_ID PRODUCT_TYPE_ID NAME                
---------- --------------- ---------------------
         3               2 Supernova
         4               2 Tank War               
         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              

已選擇10行。

SQL> select product_id,product_type_id ,nam
  2  from product_changes;

PRODUCT_ID PRODUCT_TYPE_ID NAME    
---------- --------------- -----------------
         1               1 Modern Science
         2               1 New Chemistry
         3               1 Supernova                        
        13               2 Lunar Landing                              
        14               2 Submarine                                  
        15               2 Airplane                        
已選擇6行。

------組合使用集合運算子，由於優先順序一樣，要使用括號來指示正確的順序。

SQL> (
  2  select product_id,product_type_id ,name
  3  from products
  4  union
  5  select prd_id,prd_type_id ,name
  6  from more_products)
  7  intersect
  8  select product_id,product_type_id ,name
  9  from product_changes;

PRODUCT_ID PRODUCT_TYPE_ID NAME  
---------- --------------- ----------------
         1               1 Modern Science

SQL> select product_id,product_type_id ,name
  2  from products
  3  union
  4  (
  5  select prd_id,prd_type_id ,name
  6  from more_products
  7  intersect
  8  select product_id,product_type_id ,name
  9  from product_changes);

PRODUCT_ID PRODUCT_TYPE_ID NAME                      
---------- --------------- ----------------------
         1               1 Modern Science
         2               1 Chemistry
         3               2 Supernova
         4               2 Tank War
         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

已選擇12行。