Born to Thrive

The WITH clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table. You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself. The query syntax is as follows:

The WITH clause has been incorporated starting from Oracle 9.2 as a tool for materializing subqueries to save Oracle from having to recompute them multiple times. The advantage of loading subquery data into a temporary table is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference.
The WITH clause is subject to the following restrictions:

• You can specify only one WITH clause in a single SQL statement. You cannot specify a query_name in its own subquery. However, any query_name defined in the WITH clause can be used in any subsequent named query block in the WITH clause.
• In a compound query with set operators, you cannot use the query_name for any of the component queries, but you can use the query_name in the FROM clause of any of the component queries.

Based on my experience as a software developer, I learned that not many software developers are aware of the WITH clause. But when to look at the obvious advantages of refactoring complex queries with WITH clause, it is hard to find a reason why software developers don't want to use it - maybe one would be that it is not part of original ANSI SQL, but even though, MS SQL Server has WITH clause as CTE and also IBM DB2 has WITH clause, so if the concern is compatibility of sql queries with different databases, then this might be a very good argument. After all, WITH clause usually introduces performance gain and definitely simplifies complex queries. I have tested WITH clause against a table that had 90k records and had no indexes. The table zip_to_geography has zip codes with additional 4 digit codes and the population value based on the zip code.
The query was to find the number of zip codes that had population for the given zip code (5 digit code only) above average of total population. So the regular query looked like this:
[Regular query]

There was nothing wrong with this query except maybe the total time of execution: 15 minutes, which is understandable due to the fact that the table was missing obvious indexes. The same query refactored using WITH clause looked like this:
[Refactored query using WITH clause]

First of all, this query is way easier to read and is pretty much self-explanatory, therefore it's easier to maintain the query, but the biggest benefit of refactoring was the performance gain as the query using WITH clause ran 1s, which is 900 times faster with 0.11% of the time the regular query required to run. Of course, not all queries when refactoring to use WITH clause will give that kind of performance gain, but every developer should always remember to check if WITH clause can speed up the query or at least simplify it so it's easier to read it and it makes other developers more confident with the changes or tweaks to the query.