This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

Ad by

推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

bad answer for SQL server as well...

schen(睹往睹来)

It's a typical non-sargable query. This means that the WHERE clause (or at least part of it) of the query cannot take advantage of an index, instead performing a table/index scan, which may cause the query's performance to suffer.

If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

-Use EXISTS or NOT EXISTS
-Use IN
-Perform a LEFT OUTER JOIN and check for a NULL condition

You can find more in:

(#2482203@0)
Last Updated: 2005-9-1
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 专业技术讨论 / 关于SQL的小问题, 请DX指教, 看看那个STATEMENT最有效,有个PRODUCT TABLE有PRODNUM栏位, 有个DEPARTMENT表,放PRODUCT属于哪个DEPARTMENT, 现在要找出有那些PRODUCT没有属于DEPARTMENT,不知道哪个SQL STATMENT可以很快的找出来? -liulang(流浪); 2005-9-1 {4} (#2481498@0)
谢谢

select * from Product where poductnum not in (select product from Department) -open(-close); 2005-9-1 (#2481539@0)

This is a good answer for SQL-Server, but not for Oracle, etc. -wonderlife(Wonderlife); 2005-9-1 (#2482102@0)

bad answer for SQL server as well... -schen(睹往睹来); 2005-9-1 {623} (#2482203@0)
It's a typical non-sargable query. This means that the WHERE clause (or at least part of it) of the query cannot take advantage of an index, instead performing a table/index scan, which may cause the query's performance to suffer.

If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance:

-Use EXISTS or NOT EXISTS
-Use IN
-Perform a LEFT OUTER JOIN and check for a NULL condition

You can find more in:

Good point. 但楼主不必介意我和schen的讨论，尽管用open的答案即可。 -wonderlife(Wonderlife); 2005-9-1 (#2482328@0)
to be honest, i didnot think about perfermance issue at all, i assume the poster only need a answer, he may be a student, (this question is petty like school assignment). -open(-close); 2005-9-1 {36} (#2482398@0)
i just want to give some basic idea.
select * from product where not exists ( select * from departemnt where department.product=product.productnum) -findinghouse(不写错别字。1); 2005-9-1 (#2482417@0)

select DISTINCT * from product where not exists ( select * from departemnt where department.product=product.productnum) -open(-close); 2005-9-1 (#2482440@0)

@Calgary

bad answer for SQL server as well...

Replies, comments and Discussions:

More Topics