两个表关联的条件不是一对一的关系是一对多或多对多的关系可以用exists和not exists 吗
会不会出现和表关联条件一样的笛卡尔积的情况
create table test1 (id number,name varchar2(20));
create table test2 (id number,name varchar2(20));
insert into test1 values(1,'河北');
insert into test1 values(1,'河北');
insert into test1 values(2,'河南');
insert into test1 values(2,'河南');
insert into test1 values(3,'河东');
insert into test1 values(3,'河东');
insert into test2 values(1,'河北');
insert into test2 values(1,'河北');
insert into test2 values(2,'河南');
insert into test2 values(2,'河南');
select * from test1 a where not exists (select 1 from test2 b where a.id=b.id)
select * from test1 a where exists (select 1 from test2 b where a.id=b.id)
not exists 和exists我都测试了一下 没有出现笛卡尔积的情况 谁能解释下
------解决思路----------------------
select a.* from test 1 a left join test2 b on a.id=b.id where b.id is null
select a.* from test 1 a join test2 b on a.id=b.id