WITH子句和子查询的区别? [英] Difference between WITH clause and subquery?

问题描述

WITH子句和子查询有什么区别?

What is the difference between WITH clause and subquery?

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

推荐答案

WITH 子句适用于 子查询分解，也称为公用表表达式或CTE:

The WITH clause is for subquery factoring, also known as common table expressions or CTEs:

WITH query_name 子句允许您为子查询块分配名称.然后，您可以通过指定 query_name 在查询中的多个位置引用子查询块.Oracle 数据库通过将查询名称视为内联视图或临时表来优化查询.

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.

在您的第二个示例中，您所称的 temp_table 是内联视图，而不是临时表.

In your second example, what you've called temp_table is an inline view, not a temporary table.

在许多情况下，选择使用哪一个取决于您喜欢的样式，而 CTE 可以使代码更具可读性，尤其是在具有多级子查询的情况下(当然意见会有所不同).如果您只参考 CTE/inline 视图一次，您可能不会看到任何性能差异，并且优化器最终可能会采用相同的计划.

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 中，这样代码就不会重复，并且如果优化器认为这将是有益的，它可以将其具体化.

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.

例如，这个人为的例子:

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

可以重构为:

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

不再需要重复子查询.重复代码越复杂，从维护的角度来看，使用 CTE 就越有利.子查询成本越高，可以从使用 CTE 中看到更多的性能优势，尽管优化器通常很擅长弄清楚你在做什么.

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 could see from using a CTE, though the optimiser is usually pretty good at figuring out what you're doing anyway.

这篇关于WITH子句和子查询的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！