On Mon, 21 Apr 2003, Tom Lane wrote:
> I don't see a lot of interest in the non-recursive case; it seems just
> a bizarre alternate syntax for subselect-in-FROM. The recursive case
> is interesting and useful though.
The nonrecursive case does not give you anything that you can't get
without. It's just about giving names to subexpression.
I think people just don't know about the nice syntax that they could have
had. For those who don't know about the WITH-syntax, let me show you an
example from one of my apps. First the old syntax:
SELECT p.pid, name, score FROM (((SELECT pid FROM result WHERE mid = 112) UNION (SELECT pid FROM
rphWHERE mid = 112) ) NATURAL LEFT OUTER JOIN (SELECT pid, score FROM calculated_result
WHERE mid = 112) ) AS x, person p WHERE x.pid = p.pid
ORDER BY name
and with SQL99 syntax:
WITH result_pids AS SELECT pid FROM result WHERE mid = 112 rph_pids AS SELECT
pid FROM rph WHERE mid = 112 scores AS SELECT pid, score FROM
calculated_result WHERE mid = 112 SELECT p.pid, name, score FROM (result_pids UNION rph_pids)
NATURALLEFT OUTER JOIN scores AS x, person p WHERE x.pid = p.pid
ORDER BY name;
The only "real" gain comes if you use a subexpression several times (not
in the example above) where you have to cut'n'paste the expression if you
don't have WITH.
--
/Dennis
