Subquery factoring allows you to name a block of code that contains a SELECT statement.
- A subquery factoring clause can be thought of as a named subquery block. This name can then be used multiple times in the query.
- To define a subquery factoring block, your code the WITH keyword followed by the definition of the subquery.
- To code multiple subquery factoring clauses, separate them with commas. Then, each clause can refer to itself and any previously defined subquery factoring clauses in the same WITH clauses.
- You can use subquery factoring clauses with SELECT, INSERT, UPDATE, and DELETE statements. However, you're most likely to use them with SELECT statements.
The syntax of a subquery factoring clause
Two query names and a query that uses them
with summary as
(
select vendor_state, vendor_name, sum(invoice_total) as sum_of_invoices
from invoices i, vendors v
where i.vendor_id = v.vendor_id
group by vendor_state, vendor_name
),
top_in_state as
(
select vendor_state, max(sum_of_invoices) as sum_of_invoices
from summary
group by vendor_state
)
select s.vendor_state, s.vendor_name, t.sum_of_invoices
from summary s, top_in_state t
where s.vendor_state = t.vendor_state and s.sum_of_invoices = t.sum_of_invoices
order by s.vendor_state;
The result set