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