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