求SQL语句2

[winnet]

以前那贴意思表达有点差误，现在开新的

有二个相同结构的表A和表B,结构如下：
p_id varchar(10),p_color varhcar(10),p_size varchar(10)

现在要找出a表不存在于b表的记录,即三字段中任何一个或多个字段不相同的都是

要的是速度比较快的SQL，因为二表中数据很多

我现在在用的SQL是：
select p_id,p_color,p_size from a
where (p_id + p_color + p_size) not in (select p_id + p_color + p_size from b)

有没更快的方法？

谢谢！

[pengxuan]

select p_id,p_color,p_size from a where not exists(select 1 from b where p_id<>a.p_id and p_color<>a.p_color and p_size<>a.p_size)

试试

[shanguotao20]

select * from b left join a on a.p_id = b.p_id and a.p_color = b.p_color and a.p_size = b.p_size where a.p_id is not null

大概意思就这个 没测 应该可以。

[shanguotao20]

from a left join b 写错了 手误。。

[我腫了]

select p_id,p_color,p_size from a  
where not exists (select p_id + p_color + p_size where (p_id + p_color + p_size)<>(a.p_id + a.p_color + a.p_size))

[我腫了]

select p_id,p_color,p_size from a  
where not exists (select p_id + p_color + p_size where (p_id + p_color + p_size)=(a.p_id + a.p_color + a.p_size))

[中国风]

select p_id,p_color,p_size from a  
where checksum(p_id ,p_color , p_size) not in (select checksum(p_id, p_color , p_size) from b)

binary_checksum 或用 checksum

[快溜]

这几种可以挨个试试

--1
select * from a left join b 
on a.p_id = b.p_id and a.p_color = b.p_color and a.p_size = b.p_size 
 where a.p_id is not null
--2
select * from a
 where not exists(select 1 from b 
                 where p_id=a.p_id and p_color=a.p_color and p_size=a.p_size)
--3
select * from a except select * from b
--4
select * from a checksum(p_id,p_color,p_size) not in(select p_id,p_color,p_size from b)

[中国风]

select * from a where exists(select 1 from b where p_id=a.p_id and (p_color<>a.p_color or p_size<>a.p_size))

[--小F--]

select
 * 
from
 a left join b 
on
 a.p_id = b.p_id and a.p_color = b.p_color and a.p_size = b.p_size 
where
 a.p_id is not null---连接字段加索引。

[NBDBA]

select * from a left join b on a.p_id = b.p_id and a.p_color = b.p_color and a.p_size = b.p_size where b.p_id is not null

语句not exists写法效率差不了多少

两表（p_id，p_color，p_size）加联合索引很关键

[winnet]

上面各位提到的速度都差不多，，算了，结贴,谢谢各位