Oracle Series: (15) Collection query

Query Employee information in department 20th or department 30th using the set operation

SELECT * from emp where deptno = 20unionselect * from emp where deptno = 30;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/86/FD/wKiom1fQHJqCkdMGAAA6IAD2YSM221.png "title=" 001. PNG "alt=" Wkiom1fqhjqckdmgaaa6iad2ysm221.png "/>

Attention:

Union: Two sets, if all have the same, take one

UNION ALL: Two sets, if all have the same, take a

Use set time/timing on to turn on the time switch

Set time On;set time off;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/86/FB/wKioL1fQHbKg1UxHAAAO-IqOO78132.png "title=" 002. PNG "alt=" Wkiol1fqhbkg1uxhaaao-iqoo78132.png "/>

Switch to turn off time using set time/timing off

Set timing On;set timint off;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/FD/wKiom1fQHgnzJP96AAAXTGalaG8288.png "title=" 003. PNG "alt=" Wkiom1fqhgnzjp96aaaxtgalag8288.png "/>

Use the intersection operation [intersect] to query employee information between 1000-2000 and 1500-2500 (mode one)

SELECT * from emp where Sal between + 2000intersectselect * from EMP where Sal between and 2500;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/86/FB/wKioL1fQHlSzdTaSAAAby0yS9dE587.png "title=" 004. PNG "alt=" Wkiol1fqhlszdtasaaaby0ys9de587.png "/>

Use the Where row filter to query employee information between 1000-2000 and 1500-2500 (mode two)

SELECT * from Empwhere (Sal between) and (Sal between and 2500);

Use difference set operation [minus] to query employee information (way one) with payroll at 1000-2000 but not between 1500-2500

SELECT * from emp where Sal between + 2000minusselect * from EMP where Sal between and 2500;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/86/FB/wKioL1fQHpvwgBI9AAAlb0Re2nY619.png "title=" 005. PNG "alt=" Wkiol1fqhpvwgbi9aaalb0re2ny619.png "/>

Use the Where row filter to query employee information (way two) with payroll at 1000-2000, but not between 1500-2500

SELECT * from emp where (Sal between and a) and (Sal not between and 2500);

details of the collection query :

1) Collection operations, 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;

2) Collection operation, you must ensure that the collection column type corresponds to the same

Select Empno,ename,sal,comm from emp where deptno = 20

Union

Select Empno,ename,sal,hiredate from emp where deptno = 30;

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 multiple collections operate, 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;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/FB/wKioL1fQHyeRyj2yAAAix3UZ__A216.png "title=" 006. PNG "alt=" Wkiol1fqhyeryj2yaaaix3uz__a216.png "/>

When multi-table query, subquery, collection query can complete the same task, according to the following optimization scheme selection: Query for queries with subqueries, such as multi-table query

Oracle Series: (15) Collection query