reuse a subquery

From: Charles Holleran <scorpdaddy(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: reuse a subquery
Date: 2010-08-09 14:04:33
Message-ID: BAY126-W18B41FAF70D0D6A8FB8B9BCE940@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


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;


Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thom Brown 2010-08-09 14:12:51 Re: reuse a subquery
Previous Message William Furnass 2010-08-08 22:52:41 Re: Recursive queries for network traversal