Difference between WITH clause and subquery?

Question

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

Answer 1

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

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.

In your second example, what you've called temp_table is an inline view, not a temporary 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.

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

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