查询问题

[jqka007]

如何在两个结构完全相同的表a和b中，查询出，在a中存在，在b中不存在的记录

[wangxia241]

up

[yujohny]

select a.* from a left join b on b.id=a.id where b.id is null

[txlicenhe]

1:如果有关键字
Select * from a where keyField not in (select keyField from b)

[yujohny]

我上面的语句id就是你们a、b表中的关系字段

[yujohny]

是关键字段

[txlicenhe]

2:如果没有关键字，或关键字为几个字段
Select a.* from a left join b on a.F1 = b.F1 and a.F2 = b.F2 and ...
where b.F1 is null or b.F2 is null or ...

[sdhdy]

如果只有一个主键的话

select * from a where 主键 not in (select 主键 from b)

[伍子V5]

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

[dafu71]

a-b

select * from a where not exists(select 1 from b where a.列1=b.列1 and a.列2=b.列2 and a.列3=b.列3 and ... and a.列n=b.列n)

[Wally_wu]

select * from a where not exists(select * from b)

[hongyan80]

如果表有主键,主键相同，则记录也相同
select * from a where 主键 not in (select 主键 from b)

一般情况
select * from a where 字段1+字段2+...... not in(select 字段1+字段2+...... from b)

[hongyan80]

如果有主键，主键相同，记录不一定相同，所以用主键来判断是不正确的。

[ysycrazy]

如果有个主键ID;
select * from a where id not in (select id from b)

[qdubit]

1:如果有关键字
Select * from a where keyField not in (select keyField from b)

2:如果没有关键字，或关键字为几个字段
Select a.* from a left join b on a.F1 = b.F1 and a.F2 = b.F2 and ...
where b.F1 is null or b.F2 is null or ...

[lijinqiang]

SELECT * FROM A WHERE B NOT IN (SELECT * FROM B )

[pengdali]

并运算
select c1,c2 from t1
union all
select c1,c2 from t2

差：
c1-c2:

select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)

c2-c1:

select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)

交:
select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)

[gmlxf]

myflok(阿棋) 的啊