Example Analysis of Collection query in Oracle
This article mainly introduces the example analysis of set query in Oracle, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.
Use the union operation to query the employee information of department 20 or department 30
Select * from emp where deptno = 20unionselect * from emp where deptno = 30
Note:
Union: if both sets have the same one, choose one.
Union all: if both collections have the same one, take both
Using set time/timing on, turn on the time switch
Set time on;set time off
Using set time/timing off, turn off the time switch
Set timing on;set timint off
Use the intersection operation [intersect] to query employee information with salaries between 1000-2000 and 1500-2500 (method 1)
Select * from emp where sal between 1000 and 2000intersectselect * from emp where sal between 1500 and 2500
Use where row filtering to query employee information with salaries between 1000-2000 and 1500-2500 (method 2)
Select * from empwhere (sal between 1000 and 2000) and (sal between 1500 and 2500)
Use the subtraction operation [minus] to query employee information whose salary is between 1000 and 2000 but not between 1500 and 2500 (method 1)
Select * from emp where sal between 1000 and 2000minusselect * from emp where sal between 1500 and 2500
Use where row filtering to query employee information with salaries between 1000 and 2000 but not between 1500 and 2500 (method 2)
Select * from emp where (sal between 1000 and 2000) and (sal not between 1500 and 2500)
Details of the collection query:
1) when operating a collection, you must ensure that the number of collection columns is equal.
Select empno,ename,sal,comm from emp where deptno = 20
Union
Select empno,ename,sal from emp where deptno = 30; wrong
2) when performing collection operations, you must ensure that the collection column types correspond to the same.
Select empno,ename,sal,comm from emp where deptno = 20
Union
Select empno,ename,sal,hiredate from emp where deptno = 30; wrong
3) A union B union C = C union B union A
Select * from emp where deptno = 10
Union
Select * from emp where deptno = 20
Union
Select * from emp where deptno = 30
4) when there are multiple set operations, the column name of the result is determined by the first collection column name
Select empno "number", ename "name", sal "salary" from emp where deptno = 20unionselect empno,ename,sal from emp where deptno = 10
When multiple table queries, subqueries, and collection queries can all accomplish the same task, choose the following optimization scheme:
Multi-table query-> subquery-> Collection query
Thank you for reading this article carefully. I hope the article "sample Analysis of set queries in Oracle" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!