求一SQL

[l9830216]

table1：
字段
c1,c2
数据：
a,b
a,c
b,c
table2：
字段
d1,d2
数据：
a,b
a,c
b,c
b,d
a,f
要求查出在table2中，且不在table1中的数据
如，红色为查询结果，效率高的sql

[ivws_19]

SELECT * from table2 except select * from table1

[l9830216]

其中table1的c1，c2与table2中的d1，d2是对应的

[l9830216]

[Quote=引用 1 楼 ivws_19 的回复:]
SELECT * from table2 except select * from table1
[/Quote]
table1与table2是不完全一样的，数据相同的只是这两个字段

[liuyileneal]

create table t1
(
a varchar(2),
b varchar(2)
)
create table t2
(
c varchar(2),
d varchar(2)
)
insert into t1 values('a','b');
insert into t1 values('b','b');
insert into t1 values('c','b');
insert into t1 values('d','b');

insert into t2 values('b','b');
insert into t2 values('c','b');
insert into t2 values('d','b');

select   *   from   t1   where   checksum(*)   not   in   
(select   checksum(*)   from   t2)   
a       b
-----------
a	b

[丰云]

SELECT d1,d2 from table2 except select c1,c2 from table1

[cqzw100]

select table2.* from table2 t2 left jion table1 t1 on t1.c1 = t2.d1 and t1.c2 = t2.d2
where isnull(t1.c1, '@@') = '@@'

[cqzw100]

select table2.*
from table2 t2 left jion table1 t1 on t1.c1 = t2.d1 and t1.c2 = t2.d2
where isnull(t1.c1, '@@') = '@@'

not in 就不用考虑了

[cqzw100]

select table2.*
from table2 t2 left jion table1 t1 on t1.c1 = t2.d1 and t1.c2 = t2.d2
where t1.c1 is null

not in 就不用考虑了

[l9830216]

[Quote=引用 4 楼 liuyileneal 的回复:]
SQL code

create table t1
(
a varchar(2),
b varchar(2)
)
create table t2
(
c varchar(2),
d varchar(2)
)
insert into t1 values('a','b');
insert into t1 values('b','b');
insert into t1 val……
[/Quote]
抬起大了，哈哈，，谢谢