SQL 什么是更好的？子查询还是内部连接十个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4921227/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is better? Subqueries or inner joining ten tables?
提问by Gustavo Cardoso
An old system have arrived on our office for some changes and fix, but it is also suffering from performance issues. We don't know exactly what is the source of this slowness.
旧系统已到达我们的办公室进行一些更改和修复，但它也存在性能问题。我们不知道这种缓慢的确切原因是什么。
While we were refactoring the old code we found several sql queries with the follow pattern (the queries are simplified for example purpose):
在重构旧代码时，我们发现了几个具有以下模式的 sql 查询（出于示例目的简化了查询）：
SELECT
(
SELECT X
FROM A
WHERE A.id = TABLE.id
) AS COLUMN1,
(
SELECT Y
FROM B
WHERE B.id = TABLE.id
) AS COLUMN1,
(
SELECT Z
FROM C
WHERE C.id = TABLE.id
) AS COLUMN1,
...
FROM
TABLE
WHERE
TABLE.id = @param;
These queries do several internal sub queries from everycolumn they return.
这些查询从它们返回的每一列执行几个内部子查询。
We are planning to rewrite these queries on the follow pattern:
我们计划按照以下模式重写这些查询：
SELECT
A.X, B.Y, C.Z
FROM
TABLE
INNER JOIN A on A.ID = TABLE.ID
INNER JOIN B on B.ID = TABLE.ID
INNER JOIN C on C.ID = TABLE.ID
WHERE
TABLE.id = @param;
With inner joins they are easier to read and understand, but is it really any faster? Is it the better way to write them? Unfortunately the first one we rewrote didn't improve the query time, it made the query a bit slower.
使用内连接，它们更容易阅读和理解，但它真的更快吗？写它们是更好的方法吗？不幸的是，我们重写的第一个没有改善查询时间，它使查询变慢了一点。
Here is my question: should we rewriting all these queries? Are these sub-queries a good way to do this job? Are they faster the the inner-join way?
这是我的问题：我们应该重写所有这些查询吗？这些子查询是完成这项工作的好方法吗？他们更快的内连接方式吗？
采纳答案by Rob van Wijk
If I understand your question correctly, you are starting an operation to rewrite some of your SQL statements because you THINK there might be an issue with them.
如果我正确理解了您的问题，则您正在开始重写某些 SQL 语句的操作，因为您认为它们可能存在问题。
My advice is to stop and first start to determine where your time is currently being spent. Only after you have found that it's in the queries with those scalar subselects AND it's because of those scalar subselects, you should be rewriting them. Until then: start tracing and examining.
我的建议是停止并首先开始确定您的时间目前都花在了哪里。只有在您发现它在带有这些标量子选择的查询中并且是因为这些标量子选择之后，您才应该重写它们。在那之前：开始跟踪和检查。
Here are two threads from OTN that are used to guide people with performance problems:
以下是来自 OTN 的两个线程，用于指导有性能问题的人：
http://forums.oracle.com/forums/thread.jspa?messageID=1812597http://forums.oracle.com/forums/thread.jspa?threadID=863295
http://forums.oracle.com/forums/thread.jspa?messageID=1812597 http://forums.oracle.com/forums/thread.jspa?threadID=863295
Regards,
Rob.
问候，
罗布。
And: because of scalar subquery caching, your original query might be a lot faster than a rewritten query using joins.
并且：由于标量子查询缓存，您的原始查询可能比使用连接重写的查询快得多。
回答by Sachin Shanbhag
subquery actually runs once for every row whereas the join happens on indexes.
子查询实际上对每一行运行一次，而连接发生在索引上。
Use joins for better readability and maintainability as you have already mentioned in your questions.
正如您在问题中已经提到的，使用连接以获得更好的可读性和可维护性。
回答by dogbane
Joins will give you better performance, but I recommend taking a look at the execution plan whenever "optimising" queries.
联接将为您提供更好的性能，但我建议在“优化”查询时查看执行计划。
回答by Adam Matan
As this answer argues, it shouldnot affect the performance. However, some query optimizers might perform better on JOINs, so you should make some experiments on your system.
正如这个答案所争论的那样，它不应该影响性能。但是，某些查询优化器可能在 JOIN 上表现更好，因此您应该在您的系统上进行一些实验。
And now for something completely different: JOINing each table to the next one might be more aesthetic than JOINing all with TABLE, and prevents errors whenever the id appears more than once in one of the tables:
现在来做一些完全不同的事情：将JOIN每张表放到下一张表可能比使用JOIN所有表更美观TABLE，并且可以在 id 在其中一张表中多次出现时防止错误：
SELECT
A.X, B.Y, C.Z
FROM
TABLE
INNER JOIN A on A.ID = TABLE.ID
INNER JOIN B on A.ID = B.ID
INNER JOIN C on B.ID = C.ID
WHERE
TABLE.id = @param;
回答by Dexter
Here inner joining is better. Below are the reasons:
这里内部连接更好。以下是原因：
1- In your main query, you are referring/using the values from the table used in sub query. Join is meant for this. Your ask is - "Get me some values by joining different tables as these can not be obtained from one table".
1- 在您的主查询中，您正在引用/使用子查询中使用的表中的值。加入就是为了这个。您的要求是-“通过加入不同的表来获取一些值，因为这些值无法从一张表中获得”。
Sub query should be used when columns from sub query is not referred in the main query. Like:
当主查询中未引用子查询中的列时，应使用子查询。喜欢：
select * from emp where deptno in ( select deptno from dept );
Here you are asking- "Get me all employees who works in department number deptno". You are not much concerned about this deptnoin dept.
在这里，您要问-“让我知道所有在部门编号为deptno 的员工”。你不是很关心这个deptnoin dept。
2- Another reason is readability, that you already mentioned.
2- 另一个原因是可读性，您已经提到过。
3- Performance-wise you need not worry as optimizer knows what to do.
3- 性能方面您不必担心，因为优化器知道该做什么。
For more details, please check here.
有关更多详细信息，请查看此处。
