> From: thom@linux.com > Date: Mon, 9 Aug 2010 15:12:51 +0100 > Subject: Re: [NOVICE] reuse a subquery > To: scorpdaddy@hotmail.com > CC: pgsql-novice@postgresql.org > > On 9 August 2010 15:04, Charles Holleran <scorpdaddy@hotmail.com> wrote: > > 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; > > > > Can't you just do: > > SELECT * > FROM table_a > WHERE d+5 NOT IN (SELECT t1.d FROM table_a AS t1) > ORDER BY d; > > -- > Thom Brown > Registered Linux user: #516935 >
The intent was to not run 2 selects from table_a. If the subquery is reused, then only the one subquery selects from table_a, limiting the work thereafter to the limited subset of table_a where c = 3. The second syntax suggested 'works' but also runs the select from table_a twice.