According to the Oracle docs, the WITH clause behaves much like a temporary table and is a great way to clean up lengthy SQL:
WITHclause (formally known as
subquery_factoring_clause) enables you to reuse the same query block in a
SELECTstatement when it occurs more than once within a complex query.
WITHis a part of the SQL-99 standard. This is particularly useful when a query has multiple references to the same query block and there are joins and aggregations. Using the
WITHclause, Oracle retrieves the results of a query block and stores them in the user's temporary tablespace. Note that Oracle Database does not support recursive use of the
This is exactly what I was looking for to solve a difficult task involving ROWNUM (ROWNUM post). What’s more, you are not limited to only a single “named query”. You can define as many as you want by separating them with a comma:
WITH query_name AS (subquery)
[, query_name AS (subquery) ]...