更好的是：将连接或多个子选择语句作为一个查询的一部分
性能方面，什么更好？
如果我的查询中有 3 或 4 个 join 语句，或者使用嵌入式 select 语句从数据库中提取相同的信息作为一个查询的一部分？
Performance wise, what is better?
If I have 3 or 4 join statements in my query or use embedded select statements to pull the same information from my database as part of one query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我想说联接更好，因为：
select只能模拟左/右外连接。有时您无法使用连接执行操作，在这种情况下（并且只有在那时）您将不得不依靠内部选择。
I would say joins are better because:
select's can only simulateleft/right outer join.Sometimes you cannot do stuff using joins, in that case (and only then) you'll have to fall back on an inner select.
这取决于您的数据库：特别是表的大小，而且还取决于内存参数，有时甚至还取决于表的索引方式。
在低于当前版本的 MySQL 上，使用子选择的查询很可能比返回使用联接构造的相同结果的查询慢得多。 （在 MySQL 4.1 时代，我发现差异大于一个数量级。）因此，我更喜欢使用联接构建查询。
也就是说，有些类型的查询非常难以使用联接构建，而子选择是真正做到这一点的唯一方法。
It rather depends on your database: sizes of tables particularly, but also the memory parameters and sometimes even how the tables are indexed.
On less than current versions of MySQL, there was a real possibility of a query with a sub-select being considerably slower than a query that would return the same results structured with a join. (In the MySQL 4.1 days, I have seen the difference to be greater than an order of magnitude.) As a result, I prefer to build queries with joins.
That said, there are some types of queries that are extremely difficult to build with a join and a sub-select is the only way to really do it.
假设数据库引擎绝对不进行优化，我想说这取决于您需要数据的一致性。如果您在繁忙的数据库上执行多个 SELECT 语句，并且您正在查看的数据可能会快速变化，则可能会遇到查询之间的数据不匹配的问题。
假设您的数据不包含相互依赖关系，那么多个查询就可以正常工作。但是，如果您的数据需要一致性，请使用单个查询。
这种观点归结为保证数据的交易安全。考虑这样一种情况，您必须提取所有应收账款的总计，该总计与货币交易金额保存在单独的表中。如果有人在您的两个查询之间添加另一笔交易，则应收账款总额将与交易金额的总和不匹配。
Assuming the database engine does absolutely no optimization, I would say it depends on how consistent you need your data to be. If you're doing multiple
SELECTstatements on a busy database, where the data you are looking at may change rapidly, you may run into issues where your data does not match up, between queries.Assuming your data contains no inter-dependencies, then multiple queries will work fine. However, if your data requires consistency, use a single query.
This viewpoint boils down to keeping your data transactionally safe. Consider the situation where you have to pull a total of all accounts receivable, which is kept in a separate table from the monetary transaction amounts. If someone were to add another transaction in between your two queries, the accounts receivable total would not match the sum of the transaction amounts.
大多数数据库会将下面的两个查询优化为同一个计划，因此无论您是否这样做：
或
它最终都是相同的。然而，在大多数情况下，对于重要的查询，您最好尽可能坚持使用联接，特别是因为某些类型的联接（例如内部联接）无法使用子选择来实现。
Most databases will optimize both queries below into the same plan, so whether you do:
or
It ends up being the same. However, in most cases for non-trivial queries you'd better stick with joins whenever you can, especially since some types of joins (such as an inner join) are not possible to achieve using sub-selects.