何时使用SQL子查询与标准联接? [英] When to use SQL sub-queries versus a standard join?

问题描述

我正在重写一些写得不好的SQL查询，它们过度利用了子查询.我正在寻找有关子查询使用的最佳实践.

I am working on rewriting some poorly written SQL queries and they are over-utilizing sub-queries. I am looking for best-practices regarding the use of sub-queries.

任何帮助将不胜感激.

推荐答案

除非是依赖子查询(也称为

Subqueries are usually fine unless they are dependent subqueries (also known as correlated subqueries). If you are only using independent subqueries and they are using appropriate indexes then they should run quickly. If you have a dependent subquery you might run into performance problems because a dependent subquery typically needs to be run once for each row in the outer query. So if your outer query has 1000 rows, the subquery will be run 1000 times. On the other hand an independent subquery typically only needs to be evaluated once.

如果不确定子查询依赖或独立是什么意思，这是一条经验法则-如果您可以使用子查询，将其从上下文中删除，运行它并获得结果集，则它是independent subquery.

If you're not sure what is meant by a subquery being dependent or independent here's a rule of thumb - if you can take the subquery, remove it from its context, run it, and get a result set then it's an independent subquery.

如果由于语法错误而引用了子查询之外的某些表，则会出现语法错误dependent subquery.

If you get a syntax error because it refers to some tables outside of the subquery then its a dependent subquery.

当然，一般规则也有一些例外.例如:

The general rule of course has a few exceptions. For example:

• 许多优化器可以采用一个依赖子查询，并找到一种方法来作为JOIN有效地运行它.例如，NOT EXISTS查询可能会产生一个ANTI JOIN查询计划，因此它不一定比用JOIN编写查询要慢.
• MySQL具有 bug ，其中IN表达式内的独立子查询不正确标识为从属子查询，因此使用了次优的查询计划.这显然已在最新版本的MySQL中修复.

• Many optimizers can take a dependent subquery and find a way to run it efficiently as a JOIN. For example an NOT EXISTS query might result in an ANTI JOIN query plan, so it will not necessarily be any slower than writing the query with a JOIN.
• MySQL has a bug where an independent subquery inside an IN expression is incorrectly identified as a dependent subquery and so a suboptimal query plan is used. This is apparently fixed in the very newest versions of MySQL.

如果性能是一个问题，那么请衡量您的特定查询并查看最适合您的情况.

If performance is an issue then measure your specific queries and see what works best for you.

这篇关于何时使用SQL子查询与标准联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！