oracle聚合union、union all、intersect、minus

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