内部查询是否可以? [英] Whether Inner Queries Are Okay?

问题描述

我经常看到类似...

SELECT events.id, events.begin_on, events.name
  FROM events
 WHERE events.user_id IN ( SELECT contacts.user_id 
                             FROM contacts 
                            WHERE contacts.contact_id = '1')
   OR events.user_id IN ( SELECT contacts.contact_id 
                            FROM contacts 
                           WHERE contacts.user_id = '1')

查询中有查询可以吗?是内部查询"吗?子查询"?它算作三个查询吗(我的例子)?如果这样做不好……我该如何重写我的示例?

Is it okay to have query in query? Is it "inner query"? "Sub-query"? Does it counts as three queries (my example)? If its bad to do so... how can I rewrite my example?

推荐答案

你的例子还不错.最大的问题通常来自存在所谓的相关子查询"的情况.那是子查询依赖于外部查询中的列的时候.这些问题尤其糟糕，因为实际上需要为潜在结果中的每一行重新运行子查询.

Your example isn't too bad. The biggest problems usually come from cases where there is what's called a "correlated subquery". That's when the subquery is dependent on a column from the outer query. These are particularly bad because the subquery effectively needs to be rerun for every row in the potential results.

您可以使用连接和 GROUP BY 重写您的子查询，但由于您拥有它，性能可能会有所不同，尤其是取决于您的 RDBMS.

You can rewrite your subqueries using joins and GROUP BY, but as you have it performance can vary, especially depending on your RDBMS.

这篇关于内部查询是否可以?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！