Re: reuse a subquery

Charles Holleran wrote on 09.08.2010 16:04:
> I have a query that uses the same subquery twice.  What is the correct
> syntax to reuse the subquery instead of running it twice? The query
> below 'works' but reruns the identical subquery. The point of the
> subquery is to limit the join work to the subset of table_a where c = 3
> instead of the entire table_a with c ranging from 0 to 65535. The
> planner helps expedite the rerun query, but there must be a better
> syntax for subquery reuse.
>
> E.g.
>
> SELECT *
>
> FROM
> (
> SELECT *
> FROM table_a
> WHERE c = 3
> ORDER BY d
> ) AS T1
>
> LEFT JOIN
>
> (
> SELECT *
> FROM table_a
> WHERE c = 3
> ORDER BY d
> ) AS T2
>
> ON T2.d = (T1.d + 5)
> WHERE T2.d IS NULL
> ORDER BY T1.d;
>

What about:

WITH temp_a (col1, col2, col3) AS
(
    SELECT col1, col2, col3
    FROM table_a
    WHERE c = 3
)
SELECT *
FROM temp_a t1
   JOIN temp_a t2 ON (t2.d = t1.d + 5)
WHERE t2.d IS NULL
ORDER BY t1.d;

Regards
Thomas