Oracle Learning (7): Set Operations

Oracle Learning (7): Set Operations
Set operation
Small Example

SQL> -- Query employees with Department numbers 10 and 20 SQL> -- 1. select * from emp where deptno = 10 or deptno = 20; SQL> -- 2. select * from emp where deptno in (10, 20); SQL> -- 3. SQL> select * from emp where deptno = 102 union3 select * from emp where deptno = 20; empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- ------------ ---------- 7369 smith clerk 7902-17-12 months-80 800 jones manager 207566 02-04 months-81 7839 clark manager 2975 09-6 months -81 2450 107788 scott analyst 7566 13-7 month-87 3000 207839 king president 17-11 month-81 5000 107876 adams clerk 7788 13-7 month-87 1100 207902 ford analyst 7566 03-12 month-81 3000 MILLER CLERK 7782 23-1 month-82 1300 10

Eight rows have been selected.

If it is directly merged, a syntax error occurs:

SQL> select deptno,job,sum(sal) from emp group by deptno,job2 union3 select deptno,sum(sal) from emp group by deptno4 union5 select sum(sal) from emp;select deptno,sum(sal) from emp group by deptno*

Row 3 has an error:
ORA-01789: the query block has incorrect result Columns



Set operation



SQL> /*
SQL> set operation:
SQL> 1. Each set involved in the operation must have the same number of columns and have the same type
SQL> 2. The final result uses the header of the first set as the header.
SQL> 3. order by must use the same order by after each set

SQL> */

To add an empty character column, use to_char (null)

To add an empty column for a number, use to_number (null)

 

 

Union
The UNION operator returns the UNIONALL records of the Two sets after removing the repeated elements.

SQL> select deptno,job,sum(sal) from emp group by deptno,job2 union3 select deptno,to_char(null),sum(sal) from emp group by deptno4 union5 select to_number(null),to_char(null),sum(sal) from emp;DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 875020 ANALYST 600020 CLERK 190020 MANAGER 297520 1087530 CLERK 95030 MANAGER 285030 SALESMAN 5600DEPTNO JOB SUM(SAL)---------- --------- ----------30 940029025

13 rows have been selected.

Format:

SQL> break on deptno skip 2SQL> /DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 1300MANAGER 2450PRESIDENT 5000875020 ANALYST 6000CLERK 1900MANAGER 297510875DEPTNO JOB SUM(SAL)---------- --------- ----------30 CLERK 950MANAGER 2850SALESMAN 5600940029025

13 rows have been selected.

SQL> break on nullSQL> /DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 875020 ANALYST 600020 CLERK 190020 MANAGER 297520 1087530 CLERK 95030 MANAGER 285030 SALESMAN 5600DEPTNO JOB SUM(SAL)---------- --------- ----------30 940029025

13 rows have been selected.


SQL> host cls

Start SQL Execution time SQL> -- start SQL Execution time

SQL> set timing onSQL> select deptno,job,sum(sal)2 from emp3 group by rollup(deptno,job);DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 875020 CLERK 190020 ANALYST 600020 MANAGER 297520 1087530 CLERK 95030 MANAGER 285030 SALESMAN 5600DEPTNO JOB SUM(SAL)---------- --------- ----------30 940029025

13 rows have been selected.


Used time: 00: 00: 00.00

SQL> select deptno,job,sum(sal) from emp group by deptno,job2 union3 select deptno,to_char(null),sum(sal) from emp group by deptno4 union5 select to_number(null),to_char(null),sum(sal) from emp;DEPTNO JOB SUM(SAL)---------- --------- ----------10 CLERK 130010 MANAGER 245010 PRESIDENT 500010 875020 ANALYST 600020 CLERK 190020 MANAGER 297520 1087530 CLERK 95030 MANAGER 285030 SALESMAN 5600DEPTNO JOB SUM(SAL)---------- --------- ----------30 940029025

13 rows have been selected.


Used time: 00: 00: 00.00

SQL>Set timing off

Because the data volume is too small, the time is too short to be tested. In normal times, this method can be used to detect which SQL statement has a shorter execution time, so that SQL statement is better.

 

INTERSECT statement

The INTERSECT operator returns records that belong to both sets.

Shows that the salary is at the same level 1 (700 ~ 1300) and level 2 (1201 ~ 1400) employee information:

SQL> select ename,sal from emp2 where sal between 700 and 13003 INTERSECT4 select ename,sal from emp5 where sal between 1201 and 1400;ENAME SAL---------- ----------MARTIN 1250MILLER 1300WARD 1250

Minus
MINUS returns a record that belongs to the first set but does not belong to the second set.

Shows that the salary is at the same level 1 (700 ~ 1300), but not level 2 (1201 ~ 1400) employee information:

SQL> select ename,sal from emp 2 where sal between 700 and 13003 minus4 select ename,sal from emp5 where sal between 1201 and 1400;ENAME SAL---------- ----------ADAMS 1100JAMES 950SMITH 800

Note for set operation: the parameter types and numbers in the lselect statement must be consistent. L you can use parentheses to change the sequence of execution of the set l if there is an order by clause, it must be placed after each query statement l the set operation uses the header of the first statement as the header