探讨一下这个sql的执行！

alan_yang

想不到无心插柳柳成荫啊！
     这个论题反应如此热烈，实在出乎意料！
    期望与各位大小虾共同进步！

jlandzpa

建议用外连接代替 not in,
据说速度可以提高30倍，
我也感觉确实快很多。

alan_yang

最初由 jlandzpa 发布
[B]建议用外连接代替 not in,
据说速度可以提高30倍，
我也感觉确实快很多。
[/B]

想知道具体就这个ｓｑｌ　如何写呢？

kezizi

The outer join, which I mentioned in my original"method 2", should be write like this
-------------------------------------
select * from a, b
where a.c = b.c +
and b.c is null
-------------------------------------
the above outer join returns same result as
select * from a where a.c not in (select c from b)
-------------------------------------
please let us know the performance. I guess it will not be much faster than the 3 queries

alan_yang

最初由 kezizi 发布
[B]The outer join, which I mentioned in my original"method 2", should be write like this
-------------------------------------
select * from a, b
where a.c = b.c +
and b.c is null
-------------------------------------
the above outer join returns same result as
select * from a where a.c not in (select c from b)
-------------------------------------
please let us know the performance. I guess it will not be much faster than the 3 queries [/B]

      I will test it !  Give me times !

smile_lan

最初由 kezizi 发布
[B]select * from a, b
where a.c = b.c +
and b.c is null
-------------------------------------
[/B]

in this sql , why add  'and b.c is null'
if b.c is alwayse not null, does it posible?

alan_yang

帮你提一提吧，看看谁能回答．

我是无能为力啦．我给你这么一问，也觉的怪怪的．

奇怪我就怎么没去想个为什么呢？惭愧！惭愧！

CYT

'b.c is null'  is the critical condition in this query, otherwise you'll get all the rows from a. Besides, why all of you use count(*) instead of counnt(1)? It will take tons of unnecessary info into the memory, and slow down the query.

alan_yang

Yes ! You are right!

Use count(col1) takes few times than count(*).

It's a mistake .

smile_lan

i 'll test it