The WITH Clause

WITH g AS
(select x.deptno
, avg(x.msal) avg_sal
from employees x
group by x.deptno)
select e.ename, e.init, e.msal
from employees e
join g
using (deptno)
where e.msal > g.avg_sal;
ENAME INIT MSAL
-------- ----- --------
ALLEN JAM 1600
JONES JM 2975
BLAKE R 2850
SCOTT SCJ 3000
KING CC 5000

FORD MG 3000

As you can see, we have isolated the subquery definition, in lines 1 through 5, from the actual query in lines 6 through 10. This makes the structure of the main query clearer. Using the WITH clause syntax becomes even more attractive if you refer multiple times to the same subquery from the main query. You can define as many subqueries as you like in a single WITH clause, separated by commas.

WITH v1 AS (select ... from ...)
, v2 AS (select ... from ...)
, v3 AS ...
select ...
from ...