求两张表查询的语句

[wwjlucky]

两张表abl、tbl，结构相同（A、B、C、D列），各有大约170万条数据，有何语句能查出这两张表的A、B两列不相同的条目？

[振乾]

select * from @ab1 t where not exists(select 1 from @ab2 where a=t.a and b=t.b)
union all
select * from @ab2 t where not exists(select 1 from @ab1 where a=t.a and b=t.b)

[zhengduan964532]

执行效率如何？

[wft001]

不相等表数为 sum
sum =(select count(*) from(select * from ab1 union select * from tb1) a)-170万

[zhaoyh0530]

求ab1中不在tb1的并上tb1中不在ab1中的：
select * 
from abl t 
where not exists(select 1 from tbl where a=t.a and b=t.b)
union all
select * 
from tbl t 
where not exists(select 1 from abl where a=t.a and b=t.b)

[水族杰纶]

select * from abl t where not exists(select 1 from tbl where a=t.a and b=t.b)
union all
select * from tbl t where not exists(select 1 from abl where a=t.a and b=t.b)

[SQL77]

SELECT * FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.A=B.A AND A.B=B.B)
UNION ALL
SELECT * FROM B WHERE NOT EXISTS(SELECT 1 FROM A WHERE A.A=B.A AND A.B=B.B)

??

[SQL77]

SELECT COUNT(*) FROM A WHERE NOT EXISTS(SELECT 1 FROM B WHERE A.A=B.A AND A.B=B.B)
UNION ALL
SELECT COUNT(*) FROM B WHERE NOT EXISTS(SELECT 1 FROM A WHERE A.A=B.A AND A.B=B.B)

[htl258_Tony]

select *
from (
	select * from abl 
	except 
	select * from tbl
) as a
union all
select *
from (
	select * from tbl 
	except 
	select * from abl
) as b

2005

[htl258_Tony]

[Quote=引用 4 楼 htl258 的回复:]
SQL codeselect*from (select*from ablexceptselect*from tbl
)as aunionallselect*from (select*from tblexceptselect*from abl
)as b2005
[/Quote]
不好意思,只是AB两列不是这样,用楼上的会简单点.

[soft_wsx]

--A表
select * from abl t where not exists(select 1 from tbl where a=t.a and b=t.b)
union all
--B表
select * from tbl t where not exists(select 1 from abl where a=t.a and b=t.b)

[zjb211434]

declare @ab1 table(A int, b int, c int, d int)
insert @ab1 select
1,2,3,4 union all select
2,3,4,5 union all select
3,4,5,6
declare @ab2 table(A int, b int, c int, d int)
insert @ab2 select
1,2,3,4 union all select
2,3,4,5 union all select
4,5,6,7

select a.A, a.B, a.C, a.D from @ab1 a full outer join
@ab2 b on a.A=b.A and a.B=b.B and a.C=b.C and a.D=b.D where b.A is null
union all
select b.A, b.B, b.C, b.D from @ab1 a full outer join
@ab2 b on a.A=b.A and a.B=b.B and a.C=b.C and a.D=b.D where a.A is null

--这个方法是能实现，不过不知道效率怎么样，关注大牛们有没有高效的方法

[zjb211434]

--将上面的方法两句合并成一句
declare @ab1 table(A int, b int, c int, d int)
insert @ab1 select
1,2,3,4 union all select
2,3,4,5 union all select
3,4,5,6
declare @ab2 table(A int, b int, c int, d int)
insert @ab2 select
1,2,3,4 union all select
2,3,4,5 union all select
4,5,6,7


select case when a.A is null then b.A else a.A end,
case when a.A is null then b.B else a.B end,
case when a.A is null then b.C else a.C end,
case when a.A is null then b.D else a.D end
from @ab1 a full outer join @ab2 b on a.A=b.A and a.B=b.B and a.C=b.C and a.D=b.D
where a.A is null or b.A is null

[zwzw911]

select a.* from a inner join b on a.a<>b.a and a.b<>b.b
union all
select b.* from b inner join a on a.a<>b.a and a.b<>b.b

不知道inner join和not exists，哪个效率比较高？

[zjybushiren88888]

select * from abl t where not exists(select 1 from tbl where a=t.a and b=t.b)
union all
select * from tbl t where not exists(select 1 from abl where a=t.a and b=t.b)

[wwjlucky]

谢谢各位。