Re: reuse a subquery

On 9 August 2010 15:34, Charles Holleran <scorpdaddy(at)hotmail(dot)com> wrote:
>
>
>> From: thom(at)linux(dot)com
>> Date: Mon, 9 Aug 2010 15:12:51 +0100
>> Subject: Re: [NOVICE] reuse a subquery
>> To: scorpdaddy(at)hotmail(dot)com
>> CC: pgsql-novice(at)postgresql(dot)org
>>
>> On 9 August 2010 15:04, Charles Holleran <scorpdaddy(at)hotmail(dot)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.
>

The problem is that you're treating table_a as a separate materialized
table by transposing all the d values by 5, so I'm not quite sure how
you can avoid using the table twice.

--
Thom Brown
Registered Linux user: #516935