Menu
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 
600
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.
You can't use a WITH clause in a subquery and reference its table in the FROM clause of the main query or another subquery. This query pattern results in an error message of the form relation table_name doesn't exist for the WITH clause table. You can't specify another WITH clause inside a WITH clause subquery.
A Common Table Expression (aka CTE, aka WITH statement) is a temporary data set to be used as part of a query. It only exists during the execution of that query; it cannot be used in other queries even within the same session (from Wikipedia). A subquery is a nested query; it's a query within a query (more Wikipedia).
Advantage of Using CTE Instead of having to declare the same subquery in every place you need to use it, you can use CTE to define a temporary table once, then refer to it whenever you need it. CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries.
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.
113
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 , DateChanged FROM temp WHERE temp.NextStatusID IS NULL If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
