Skip site navigation (1) Skip section navigation (2)

Re: reuse a subquery

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Charles Holleran" <scorpdaddy(at)hotmail(dot)com>
Cc: "postgresql novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: reuse a subquery
Date: 2010-08-09 15:22:47
Message-ID: 62AE7FA3507C4CB980D562A00FA4483A@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: 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 ----- 
  From: Charles Holleran 
  To: pgsql-novice(at)postgresql(dot)org 
  Sent: Monday, August 09, 2010 3:34 PM
  Subject: Re: [NOVICE] reuse a subquery



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

In response to

pgsql-novice by date

Next:From: Rikard BosnjakovicDate: 2010-08-09 16:24:44
Subject: Trying to understand why a null "fails" a select
Previous:From: Thomas KellererDate: 2010-08-09 15:21:45
Subject: Re: reuse a subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group