两个表table1,table2,table1.name=table2.name,table1.code=table2.code,现在想跟据这个条件查询出table2中table1.id<>table2.id的数据,这样写可以吗?select table1.id, table1.code, table2.id, table2.code
from table1 a
left join table2 b
on table1.code = table2.code
and table1.name = table2.name
and table1.id <> table2.id
order by b.code
from table1 a
left join table2 b
on table1.code = table2.code
and table1.name = table2.name
and table1.id <> table2.id
order by b.code
minus
select t3.* from table2 t3, table1 t1
where t1.name = t3.name and t1.code = t3.code and t1.id = t3.id;select * from table2 t2
where not exists
( select 1 from table1 t1 where t1.name = t2.name and t1.code = t2.code and t1.id = t2.id);
select table1.id, table1.code, table2.id, table2.code
from table1 a
left join table2 b
on a.code = b.code
and a.name = b.name
and a.id <> b.id
order by b.code
on table1.code = table2.code
and table1.id <> table2.id
这俩矛盾, 为什么不这么写呢,select table1.id, table1.code, table2.id, table2.code
from table1 a,
table2 b
where table1.code(+) = table2.code
and table1.name = table2.name
order by b.code取table2的全部呢
where not exists
( select 1 from table1 t1 where t1.name = t2.name and t1.code = t2.code and t1.id = t2.id);支持这个
minus
select t3.*,t1.id from table2 t3, table1 t1
where t1.name = t3.name and t1.code = t3.code and t1.id = t3.id;
---这个应该可以吧
select a.id, a.code, b.id, b.code
from table1 a
inner join table2 b
on a.code = b.code
and a.name = b.name
and a.id <> b.id
order by b.code
2、如果(table1.code,table1.name)和(table2.code,table2.name)可以分别唯一的确定
table1中和table2中的一条数据,你这么写是没有问题的,如果不是就不可以了,会产生笛卡尔积
3、最保险的方法还是用 1 楼的第二种方法。