Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.