Difference between WITH clause and subquery?

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_tablewhere curr >= 0union allselect -1 * curr from (  select curr from tableone t1  left join tabletwo t2 on (t1.empid = t2.empid)) temp_tablewhere 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_tablewhere curr >= 0union allselect -1 * curr from temp_tablewhere 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.

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).

Additionally, if the subquery contains analytical functions (LEAD/LAG/etc) and if you want to filter the result of the analytical function - with the SUBQUERY approach, you'd have to insert the results into a temp table and perform the filtering etc on the temp table whereas using a WITH clause, you can use the result for filtering/grouping/etc in the same query

;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    , DateChangedFROM    tempWHERE    temp.NextStatusID IS NULL