oracle WITH子句和子查询的区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30078464/
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
Difference between WITH clause and subquery?
提问by Hari Rao
What is the difference between WITHclause and subquery?
WITH子句和子查询有什么区别？
1. WITH table_name as ( ... )
2. select *
from ( select curr from tableone t1
left join tabletwo t2
on (t1.empid = t2.empid)
) as temp_table
回答by Alex Poole
The WITHclause is for subquery factoring, also known as common table expressions or CTEs:
该WITH子句用于子查询分解，也称为公用表表达式或 CTE：
The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.
WITH query_name 子句允许您为子查询块分配名称。然后，您可以通过指定 query_name 在查询中的多个位置引用子查询块。Oracle 数据库通过将查询名称视为内联视图或临时表来优化查询。
In your second example, what you've called temp_tableis an inline view, not a temporary table.
在您的第二个示例中，您调用的temp_table是内联视图，而不是临时表。
In many cases the choice of which to use comes down to your preferred style, and CTEs can make code more readable particularly with multiple levels of subqueries (opinions vary of course). If you only refer to the CTE/inline view once you probably won't see any difference in performance, and the optimiser may end up with the same plan.
在许多情况下，选择使用哪种风格取决于您喜欢的风格，而 CTE 可以使代码更具可读性，尤其是在具有多级子查询的情况下（当然意见各不相同）。如果您只参考一次 CTE/inline 视图，您可能看不到任何性能差异，并且优化器最终可能会采用相同的计划。
They are particularly useful though when you need to use the same subquery in more than one place, such as in a union. You can pull an inline view out into a CTE so the code isn't repeated, and it allows the optimiser to materialize it if it thinks that would be beneficial.
但是当您需要在多个地方使用相同的子查询时，它们特别有用，例如在联合中。您可以将内联视图拉出到 CTE 中，这样代码就不会重复，并且如果优化器认为这会有益，它允许优化器实现它。
For example, this contrived example:
例如，这个人为的例子：
select curr from (
select curr from tableone t1
left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr >= 0
union all
select -1 * curr from (
select curr from tableone t1
left join tabletwo t2 on (t1.empid = t2.empid)
) temp_table
where curr < 0
could be refactored to:
可以重构为：
with temp_table as (
select curr from tableone t1
left join tabletwo t2 on (t1.empid = t2.empid)
)
select curr from temp_table
where curr >= 0
union all
select -1 * curr from temp_table
where curr < 0
The subquery no longer has to be repeated. The more complicated the repeated code is, the more beneficial it is from a maintenance point of view to use a CTE. And the more expensive the subquery is the more performance benefit you couldsee from using a CTE, though the optimiser is usually pretty good at figuring out what you're doing anyway.
子查询不再需要重复。重复代码越复杂，从维护的角度来看，使用 CTE 就越有利。子查询成本越高，您可以从使用 CTE 中看到更多的性能优势，尽管优化器通常非常擅长弄清楚您在做什么。
回答by ibre5041
Possibly none. Oracle is capable of many algebraic transformations before actually optimizing the query. Most probably both queries will be evaluated the same way (they will have the same execution plan).
可能没有。在实际优化查询之前，Oracle 能够进行许多代数转换。很可能两个查询将以相同的方式进行评估（它们将具有相同的执行计划）。
回答by N Shah
Additionally, if the subquery contains analytical functions (LEAD/LAG/etc) and if you want to filter the result of the analytical function - with the SUBQUERYapproach, you'd have to insert the results into a temp table and perform the filtering etc on the temp table whereas using a WITHclause, you can use the result for filtering/grouping/etc in the same query
此外，如果子查询包含分析函数（LEAD/LAG/etc），并且如果您想过滤分析函数的结果 - 使用该SUBQUERY方法，您必须将结果插入临时表并执行过滤等临时表，而使用WITH子句，您可以在同一查询中使用结果进行过滤/分组/等
;WITH temp AS
(
SELECT
ID
, StatusID
, DateChanged
, LEAD(StatusID,1) OVER (PARTITION BY ID ORDER BY ID, DateChanged, StatusID) NextStatusID
FROM
myTable
WHERE
ID in (57,58)
)
SELECT
ID
, StatusID
, DateChanged
FROM
temp
WHERE
temp.NextStatusID IS NULL
