07.SQL Basic-to-set operations (Union and Union All)

SQL Basics--Set operations (Union and Union All)

The set operator can combine rows returned by two or more queries, that is, the collection belongs to the vertical join operation.

I. Common SET operators

UNION ALL returns all rows retrieved by each query and does not filter out duplicate records union returns all rows retrieved by each query that filter out duplicate records, that is, INTERSECT returns the common rows retrieved by two queries, that is, the intersection minus Returns the row that is subtracted from the row retrieved by the second query, minus the remaining rows after the first query, that is, the difference set

principle of Set operation

1. The number of expressions for all picklist must be the same 2. For each column in the result set, or for a subset of any column in an individual subquery, you must have the same data type, or you can implicitly convert to the same data type, or you will need to display conversion 3. The corresponding result set columns in each query must appear in the same order as 4. The first individual query in a

Iii. demonstration of the individual set operators

--Generates an EMP table with the same structure for the set operation generation environment, named EMP2

Idle> select * from EMP; EMPNO ename JOB MGR hiredate SAL commdeptno-----------------------------------------------------      --------------------------7369 SMITH clerk 7902 1980-12-17 7499 ALLEN salesman      7698 1981-02-20 1600300 7521 WARD salesman 7698 1981-02-22 1250500 7566 JONES       MANAGER 7839 1981-04-02 2975 7654 MARTIN salesman 7698 1981-09-28 1250 1400 30     7698 BLAKE Manager 7839 1981-05-01 2850 7782 CLARK manager 7839 1981-06-09 2450 Ten 7788 SCOTT ANALYST 7566 1987-04-19 7839 KING President 1981-11-17 500 0 7844 TURNER salesman 7698 1981-09-08 0 7876 ADAMS Clerk 7788 1987-05-2 3 1100 7900 JAMES clerk 7698 1981-12-03 950 7902 FORD ANALYST 751981-12-03 7934 MILLER Clerk 7782 1982-01-23 1300 1014 rows selected. 

idle>  CREATE table EMP2 tablespace TBS1 as SELECT * from EMP where empno in (7369,7654,7839,7876); Table created.

idle> INSERT INTO EMP2 (empno,ename,sal) Select 8001, ' ROBINSON ', 3500 from dual;1 row created.idle> insert INTO EMP2 (empno,ename,sal) Select 8002, ' HENRY ', 3700 from dual;1 row created.idle> insert into EMP2 (empno,ename,sal) Select 8004 , ' JOHNSON ', 4000 from dual;1 row created.

Idle>  SELECT * from EMP2;     EMPNO ename      JOB       MGR hiredate     SAL       commdeptno----------------------------------------------------- --------------------------      7369 SMITH      clerk      7902 1980-12-17      7654 MARTIN     Salesman      7698 1981-09-28    1250       1400    7839      KING       President    1981-11-17      7876 ADAMS      clerk      7788 1987-05-23 1100 8001      ROBINSON    3500      8002 HENRY    3700      8004 JOHNSON    40007 rows selected.

　　

--1.union filtering of duplicate records

Idle> Select Empno,ename,job,hiredate,sal from Empunionselect empno,ename,job,hiredate,sal from EMP2;  2    3       EMPNO ename      jobhiredate  SAL-------------------------------------------------      7369 SMITH      clerk1980-12-17      7499 ALLEN      salesman1981-02-20      7521 WARD       salesman1981-02-22 1250      7566 JONES      manager1981-04-02 2975      7654 MARTIN     salesman1981-09-28 1250 7698      BLAKE      manager1981-05-01 2850      7782 CLARK      manager1981-06-09 2450 7788      SCOTT      analyst1987-04-19      7839 KING       President 1981-11-17      7844 TURNER salesman1981-09-08 7876      ADAMS      clerk1987-05-23 1100      7900 JAMES      clerk1981-12-03  950      7902 FORD       analyst1981-12-03      7934 MILLER     clerk1982-01-23 1300      8001 ROBINSON 3500      8002 HENRY 3700      8004 JOHNSON 400017 Rows selected.

　　

--2.union all and do not repeat records

Idle> Select Empno,ename,job,hiredate,sal from Empunion allselect empno,ename,job,hiredate,sal from EMP2; 2 3 EMPNO ename jobhiredate SAL-------------------------------------------------7369 SMITH C LERK1980-12-17 7499 ALLEN salesman1981-02-20 7521 WARD salesman1981-02-22 1250 7566 JO      NES manager1981-04-02 2975 7654 MARTIN salesman1981-09-28 1250 7698 BLAKE manager1981-05-01 2850 7782 CLARK manager1981-06-09 2450 7788 SCOTT analyst1987-04-19 7839 KING President 1981 -11-17 7844 TURNER salesman1981-09-08 7876 ADAMS clerk1987-05-23 1100 7900 JAMES CL      ERK1981-12-03 950 7902 FORD analyst1981-12-03 7934 MILLER clerk1982-01-23 1300 7369 SMITH      CLERK1980-12-17 7654 MARTIN salesman1981-09-28 1250 7839 KING President 1981-11-17 5000 7876 ADAMS clerk1987-05-23 1100      8001 ROBINSON 3500 8002 HENRY 3700 8004 JOHNSON 400021 rows selected. 

　　

--3.intersect intersection, returns two result sets that share a portion

Idle> Select Empno,ename,job,hiredate,sal from Empintersectselect empno,ename,job,hiredate,sal from EMP2;  2    3       EMPNO ename      jobhiredate  SAL-------------------------------------------------      7369 SMITH      clerk1980-12-17      7654 MARTIN     salesman1981-09-28 1250      7839 KING       President 1981-11-17      7876 ADAMS      clerk1987-05-23 1100

　　

--4.minus complement, the result of the previous result set minus one result set

Idle> select Empno as "Employeeno", ename  "EmployeeName", Job  "job", HireDate as "HireDate", Sal "Sal" from EMPM Inusselect empno,ename,job,hiredate,sal from Emp2order by "Sal";  2    3    4  employeeno employeena jobhiredate  Sal-------------------------------------------------      7900 JAMES      clerk1981-12-03  950      7521 WARD       salesman1981-02-22 1250      7934 MILLER     clerk1982-01-23 1300      7844 TURNER     salesman1981-09-08      7499 ALLEN      salesman1981-02-20      7782 CLARK      manager1981-06-09 2450      7698 BLAKE      manager1981-05-01 2850      7566 JONES      manager1981-04-02 2975      7788 SCOTT      analyst1987-04-19 3000      7902 FORD       analyst1981-12-03 300010 rows selected.

　　

07.SQL Basic-to-set operations (Union and Union All)