坏(相关)或好(不相关)子查询?

问题描述：

我想知道，下面更新语句中的子查询是好(不相关)还是坏(子查询)?

I wonder, if the subquery in the following update statement is a good (not correlated) or bad (subquery)?

换句话说，我的问题是，这是一个低效的查询吗?

In other words, my question is, is it an inefficient query?

UPDATE tableA
SET field1=0
FROM tableA
WHERE field2 IN (SELECT field2 
                 FROM tableA
                 WHERE someField IS NOT NULL 
                 AND someOtherField = 'ABC')

答

您的查询没有关联，它只是一个子查询..

Your query is not correlated ,its just a subquery..

下面是一个相关的子查询..

below is a correlated subquery..

UPDATE a
SET field1=0
FROM tableA a
WHERE exists  (SELECT 1
                 FROM tableB b
                 WHERE a.somecol=b.somecol)

相关子查询的另一个例子

one more example of correlated subquery

select orderid,
(select custname from customers c where c.custid=o.custid) from orders o

上面的查询可以写成join

above query can be written as join

select orderid,custname
 from orders o
 join
 customers c
 on c.custid=o.custid

执行两个查询往往使用相同的执行计划，并且两者的成本也相同..所以我们不能假设，相关的子查询不会表现得更好

executing both queries tend to use same execution plan and both have same cost as well..so we can't assume,Correlated subqueries won't perform better

select orderid,
(select count(orderid) from orders o2 where o2.custid=o.custid ) 
from orders o

对于上述相关子查询，SQL 不能只访问一次订单表并进行所有计算，它需要访问表两次..这只是我在相关子查询中看到的问题

for the above correlated subquery,SQL can't access orders table only once and do all the calculations,it will need to access table twice..this is only gotcha i could see with correlated subqueries

坏(相关)或好(不相关)子查询?

相关推荐