reuse a subquery - Mailing list pgsql-novice

From Charles Holleran
Subject reuse a subquery
Date
Msg-id BAY126-W18B41FAF70D0D6A8FB8B9BCE940@phx.gbl
Whole thread Raw
Responses Re: reuse a subquery  (Thom Brown <thom@linux.com>)
Re: reuse a subquery  (Thomas Kellerer <spam_eater@gmx.net>)
List 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;
 
 
 

pgsql-novice by date:

Previous
From: William Furnass
Date:
Subject: Re: Recursive queries for network traversal
Next
From: Thom Brown
Date:
Subject: Re: reuse a subquery