【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行。