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;
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 |