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



  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值