oracle Union Minus Intersect Union All

Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

先创建表：

CREATE TABLE TB_USER
(
    ID INTEGER PRIMARY KEY,
    USER_NAME VARCHAR2(20) NOT NULL,
    USER_AGE INTEGER NOT NULL
);

CREATE SEQUENCE SEQ_USER
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW
BEGIN
    SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

然后插入一些数据：

DECLARE
V_AGE TB_USER.USER_AGE%TYPE;
V_NAME TB_USER.USER_NAME%TYPE;
BEGIN
  FOR I IN 1..100 LOOP
    SELECT DBMS_RANDOM.value(1, 100) INTO V_AGE FROM DUAL;
    V_NAME := 'FOR_' || V_AGE;
    INSERT INTO TB_USER(USER_NAME, USER_AGE) VALUES (V_NAME, V_AGE);
  END LOOP;
  COMMIT;
END;

然后创建另外两张表：

CREATE TABLE TB_USER1 AS SELECT ID, USER_NAME, USER_AGE FROM (SELECT ROWNUM RN, U.* FROM TB_USER U) T WHERE T.RN BETWEEN 30 AND 60;

CREATE TABLE TB_USER2 AS SELECT ID, USER_NAME, USER_AGE FROM (SELECT ROWNUM RN, U.* FROM TB_USER U) T WHERE T.RN BETWEEN 40 AND 70;

TB_USER1数据：

ID	USER_NAME	USER_AGE
550	FOR_95	95
551	FOR_16	16
552	FOR_65	65
553	FOR_20	20
554	FOR_10	10
555	FOR_89	89
556	FOR_14	14
557	FOR_74	74
558	FOR_22	22
559	FOR_15	15
560	FOR_91	91
561	FOR_45	45
562	FOR_35	35
563	FOR_3	3
564	FOR_28	28
565	FOR_8	8
566	FOR_95	95
567	FOR_57	57
568	FOR_36	36
569	FOR_41	41
570	FOR_8	8
571	FOR_1	1
572	FOR_28	28
573	FOR_34	34
574	FOR_94	94
575	FOR_86	86
576	FOR_67	67
577	FOR_59	59
578	FOR_79	79
579	FOR_69	69
580	FOR_35	35

TB_USER2数据：

ID	USER_NAME	USER_AGE
560	FOR_91	91
561	FOR_45	45
562	FOR_35	35
563	FOR_3	3
564	FOR_28	28
565	FOR_8	8
566	FOR_95	95
567	FOR_57	57
568	FOR_36	36
569	FOR_41	41
570	FOR_8	8
571	FOR_1	1
572	FOR_28	28
573	FOR_34	34
574	FOR_94	94
575	FOR_86	86
576	FOR_67	67
577	FOR_59	59
578	FOR_79	79
579	FOR_69	69
580	FOR_35	35
581	FOR_82	82
582	FOR_59	59
583	FOR_4	4
584	FOR_69	69
585	FOR_36	36
586	FOR_86	86
587	FOR_74	74
588	FOR_45	45
589	FOR_24	24
590	FOR_5	5

两张表中有些数据是重复的，ID从560到580之间。

Minus：

执行：

SELECT * FROM TB_USER2 MINUS SELECT * FROM TB_USER1;

输出：

ID	USER_NAME	USER_AGE
581	FOR_82	82
582	FOR_59	59
583	FOR_4	4
584	FOR_69	69
585	FOR_36	36
586	FOR_86	86
587	FOR_74	74
588	FOR_45	45
589	FOR_24	24
590	FOR_5	5

执行：

SELECT * FROM TB_USER1 MINUS SELECT * FROM TB_USER2;

输出：

ID	USER_NAME	USER_AGE
550	FOR_95	95
551	FOR_16	16
552	FOR_65	65
553	FOR_20	20
554	FOR_10	10
555	FOR_89	89
556	FOR_14	14
557	FOR_74	74
558	FOR_22	22
559	FOR_15	15

Intersect：

执行：

SELECT * FROM TB_USER1 INTERSECT SELECT * FROM TB_USER2;

输出：

ID	USER_NAME	USER_AGE
560	FOR_91	91
561	FOR_45	45
562	FOR_35	35
563	FOR_3	3
564	FOR_28	28
565	FOR_8	8
566	FOR_95	95
567	FOR_57	57
568	FOR_36	36
569	FOR_41	41
570	FOR_8	8
571	FOR_1	1
572	FOR_28	28
573	FOR_34	34
574	FOR_94	94
575	FOR_86	86
576	FOR_67	67
577	FOR_59	59
578	FOR_79	79
579	FOR_69	69
580	FOR_35	35

UNION：

执行：

SELECT * FROM TB_USER1 UNION SELECT * FROM TB_USER2;

输出：

ID	USER_NAME	USER_AGE
550	FOR_95	95
551	FOR_16	16
552	FOR_65	65
553	FOR_20	20
554	FOR_10	10
555	FOR_89	89
556	FOR_14	14
557	FOR_74	74
558	FOR_22	22
559	FOR_15	15
560	FOR_91	91
561	FOR_45	45
562	FOR_35	35
563	FOR_3	3
564	FOR_28	28
565	FOR_8	8
566	FOR_95	95
567	FOR_57	57
568	FOR_36	36
569	FOR_41	41
570	FOR_8	8
571	FOR_1	1
572	FOR_28	28
573	FOR_34	34
574	FOR_94	94
575	FOR_86	86
576	FOR_67	67
577	FOR_59	59
578	FOR_79	79
579	FOR_69	69
580	FOR_35	35
581	FOR_82	82
582	FOR_59	59
583	FOR_4	4
584	FOR_69	69
585	FOR_36	36
586	FOR_86	86
587	FOR_74	74
588	FOR_45	45
589	FOR_24	24
590	FOR_5	5

UNION ALL：

执行：

SELECT * FROM TB_USER1 UNION ALL SELECT * FROM TB_USER2;

输出：

ID	USER_NAME	USER_AGE
550	FOR_95	95
551	FOR_16	16
552	FOR_65	65
553	FOR_20	20
554	FOR_10	10
555	FOR_89	89
556	FOR_14	14
557	FOR_74	74
558	FOR_22	22
559	FOR_15	15
560	FOR_91	91
561	FOR_45	45
562	FOR_35	35
563	FOR_3	3
564	FOR_28	28
565	FOR_8	8
566	FOR_95	95
567	FOR_57	57
568	FOR_36	36
569	FOR_41	41
570	FOR_8	8
571	FOR_1	1
572	FOR_28	28
573	FOR_34	34
574	FOR_94	94
575	FOR_86	86
576	FOR_67	67
577	FOR_59	59
578	FOR_79	79
579	FOR_69	69
580	FOR_35	35
560	FOR_91	91
561	FOR_45	45
562	FOR_35	35
563	FOR_3	3
564	FOR_28	28
565	FOR_8	8
566	FOR_95	95
567	FOR_57	57
568	FOR_36	36
569	FOR_41	41
570	FOR_8	8
571	FOR_1	1
572	FOR_28	28
573	FOR_34	34
574	FOR_94	94
575	FOR_86	86
576	FOR_67	67
577	FOR_59	59
578	FOR_79	79
579	FOR_69	69
580	FOR_35	35
581	FOR_82	82
582	FOR_59	59
583	FOR_4	4
584	FOR_69	69
585	FOR_36	36
586	FOR_86	86
587	FOR_74	74
588	FOR_45	45
589	FOR_24	24
590	FOR_5	5