How to code a subquery factoring clause

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