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子句用于

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屋！