Oracle_in_not-in_distinct_minsu的用法

 

create table a(
   id int,
   username varchar2(20)
);


create table b(
   id int,
   username varchar2(20)
);

insert into a  values(1,'小明');
insert into a  values(2,'小红');
insert into a  values(3,'小君');
commit

select * from a
select * from b
delete from a where id>=4

insert into b  values(2,'小红');
insert into b  values(4,'小星');
insert into b  values(5,'小刘');

 

去重 （查人数）
select * from a
union
select * from b

 

去重另一种写法
select distinct * from (
  select * from a
  union all
  select * from b
)

 

 

不去重（速度快）
select * from a
union all
select * from b

 

求红色区域

 

先查id

select id from a where a.id in (select b.id from b)

 

再查是谁 
select * from a where id in(
    select id from b where b.id in (select a.id from a)
)

 

另一种方法
select * from b where b.id in (select a.id from a)

 

还有一种方法
select b.* from a,b where a.id = b.id

 

 求红色区域

 

select * from a where id not in (select id from b)

 not in 在oracle大数据时，速度非常慢，不建议使用

 

select * from a where id in (

    select id from a
    minus
    select id from b

)