oracle集合union、union all、intersect、minus

oracle中集合的效率比普通的比较要高

测试案例

--测试表数据

  

   

    

     

    
    

     

      SQL> select stuid from zr_stu;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      -----------
     
    
   

    

     

    
    

     

       1
     
    
   

    

     

    
    

     

       2
     
    
   

    

     

    
    

     

       3
     
    
   

    

     

    
    

     

       4
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     

       6
     
    
   

    

     

    
    

     

       7
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      7 rows selected
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.14 seconds
     
    
  
 

--表数据

  

   

    

     

    
    

     

      SQL> select stuid from zr_stu where stuid > 3;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      -----------
     
    
   

    

     

    
    

     

       4
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     

       6
     
    
   

    

     

    
    

     

       7
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.047 seconds
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      SQL> select stuid from zr_stu where stuid < 6;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      -----------
     
    
   

    

     

    
    

     

       1
     
    
   

    

     

    
    

     

       2
     
    
   

    

     

    
    

     

       3
     
    
   

    

     

    
    

     

       4
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.109 seconds
     
    
  
 

--union

  

   

    

     

    
    

     

      SQL> select stuid from zr_stu where stuid > 4 union select stuid from zr_stu where stuid < 6;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      ----------
     
    
   

    

     

    
    

     

       1
     
    
   

    

     

    
    

     

       2
     
    
   

    

     

    
    

     

       3
     
    
   

    

     

    
    

     

       4
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     

       6
     
    
   

    

     

    
    

     

       7
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      7 rows selected
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.031 seconds
     
    
  
 

--union all

  

   

    

     

    
    

     

      SQL> select stuid from zr_stu where stuid > 4 
     
    
   

    

     

    
    

     
  2  union all 
     
    
   

    

     

    
    

     
  3  select stuid from zr_stu where stuid < 6;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      ----------
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     

       6
     
    
   

    

     

    
    

     

       7
     
    
   

    

     

    
    

     

       1
     
    
   

    

     

    
    

     

       2
     
    
   

    

     

    
    

     

       3
     
    
   

    

     

    
    

     

       4
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      8 rows selected
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.156 seconds
     
    
  
 

--intersect

  

   

    

     

    
    

     

      SQL> select stuid from zr_stu where stuid > 4 
     
    
   

    

     

    
    

     
  2  intersect 
     
    
   

    

     

    
    

     
  3  select stuid from zr_stu where stuid < 6;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      ----------
     
    
   

    

     

    
    

     

       5
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.078 seconds
     
    
  
 

--minus

  

   

    

     

    
    

     

      SQL> select stuid from zr_stu where stuid > 4
     
    
   

    

     

    
    

     
  2  minus 
     
    
   

    

     

    
    

     
  3  select stuid from zr_stu where stuid < 6;
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

       STUID
     
    
   

    

     

    
    

     

      ----------
     
    
   

    

     

    
    

     

       6
     
    
   

    

     

    
    

     

       7
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

      Executed in 0.062 seconds
     
    
  
 

文章来源: bugstack.blog.csdn.net，作者：Yao__Shun__Yu，版权归原作者所有，如需转载，请联系作者。

原文链接：bugstack.blog.csdn.net/article/details/7906663