Oracle Learning's set operation

First, set operation operator

UNION: (unions) returns two sets all records with a duplicate value removed

UNION All: (set) returns two sets all records with duplicate values removed

INTERSECT: (intersection) returns all records of two sets, repeating only once

minus: (difference set) returns all that belong to the first collection but do not belong to the second collection

Recording

each set in a set operation must have the same number of columns, and the type is consistent, and the node of the set Operation

Fruit will be The header of the first collection is used as the final header, and the order by must be placed in each

After collection

Second, set operation

Use the union operator to query employee information for 10-and 20-door parts of the Scott table

SQL code

2. Sql> SELECT * from EMP
4. 2 where deptno=10
6. 3 Union
8. 4 SELECT * FROM EMP
10. 5 where deptno=20;
14. EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
16. ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
18. 7369 SMITH Clerk 7902 1 July-December-80 880 20
20. 7566 JONES MANAGER 7839 February-April-81 2975 20
22. 7782 CLARK MANAGER 7839 September-June-81 2450 10
24. 7788 SCOTT ANALYST 7566 1 September-April-87 3000 20
26. 7839 KING President 1 July-November-81 5000 10
28. 7876 ADAMS Clerk 7788 2 March-May-87 1100 20
30. 7902 FORD ANALYST 7566 March-December-81 3000 20
32. 7934 MILLER Clerk 7782 2 March-January-82 1300 10
36. 8 rows have been selected.

Use the union operator to count the total wages of each department in the EMP table under the Scott user,

and various departments of the job bit of wages

SQL code

2. Sql> break on deptno Skip 2;
4. sql> Select Deptno,job,sum (SAL) from the emp GROUP by deptno,job
6. 2 Union
8. 3 Select Deptno,to_char (null),sum (SAL) from the emp GROUP by Deptno /c10>
10. 4 Union
12. 5 Select To_number (null), TO_CHAR (null),sum (sal) from EMP;
16. DEPTNO JOB SUM (SAL)
18. ---------- --------- ----------
20. Ten Clerk 1300
22. MANAGER 2450
24. President 5000
26. 8750
32. ANALYST 6000
34. Clerk 1980
36. MANAGER 2975
38. 10955
44. 950 Clerk
46. MANAGER 2850
48. Salesman 5600
50. 9400
56. 29105
64. 13 rows have been selected.

Use the UNION ALL operator to query the information in the Dept table under the Scott user

SQL code

2. Sql> SELECT * from dept
4. 2 Union All
6. 3 SELECT * FROM dept;
10. DEPTNO dname LOC
12. ---------- -------------- -------------
14. Ten ACCOUNTING NEW YORK
16. DALLAS
18. SALES CHICAGO
20. OPERATIONS BOSTON
22. Ten ACCOUNTING NEW YORK
24. DALLAS
26. SALES CHICAGO
28. OPERATIONS BOSTON
32. 8 rows have been selected.

use the Intersect operator to query the Dept table and the EMP table that are present in the Scott user.

Department number

SQL code

1. Sql> Select Deptno from emp
2. 2 intersect
3. 3 Select Deptno from dept;
5. DEPTNO
6. ----------
7. 10
8. 20
9. 30

use the minus operator to query the presence of the Scott user under the Dept table and in the EMP table

non-existent department number

SQL code

1. Sql> Select Deptno from dept
2. 2 minus
3. 3 Select Deptno from EMP;
5. DEPTNO
6. ----------
7. 40

use the minus operator to query the employee wages in the EMP table under the Scott user

(1200-2000) This file, not the employee of (1400-3000) this file

SQL code

2. sql> Select Empno,ename,job,sal
4. 2 from EMP
6. 3 where Sal between
8. 4 minus
10. 5 Select Empno,ename,job,sal
12. 6 from EMP
14. 7 where Sal between 1400 and 3000;
18. EMPNO ename JOB SAL
20. ---------- ---------- --------- ----------
22. 7521 WARD salesman 1250
24. 7654 MARTIN salesman 1250
26. 7934 MILLER Clerk 1300

Oracle Learning's set operation