Re: reuse a subquery - Mailing list pgsql-novice

From Oliveiros d'Azevedo Cristina
Subject Re: reuse a subquery
Date
Msg-id 62AE7FA3507C4CB980D562A00FA4483A@marktestcr.marktest.pt
Whole thread Raw
In response to reuse a subquery  (Charles Holleran <scorpdaddy@hotmail.com>)
List pgsql-novice
You mean a   query with just one SELECT clause?
 
SELECT *
FROM table_a primeiro
LEFT JOIN table_a segundo
ON primeiro.d + 5 = segundo.d
WHERE primeiro.c= 3
AND segundo.c = 3
AND segundo.d IS NULL   
ORDER BY d
 
Best,
Oliveiros
----- Original Message -----
Sent: Monday, August 09, 2010 3:34 PM
Subject: Re: [NOVICE] reuse a subquery


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

pgsql-novice by date:

Previous
From: Thomas Kellerer
Date:
Subject: Re: reuse a subquery
Next
From: Rikard Bosnjakovic
Date:
Subject: Trying to understand why a null "fails" a select