Postgres Pro
Downloads
Home   >   mailing lists

Thread: reuse a subquery

reuse a subquery

From
Charles Holleran
Date:
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;
 
 
 

Re: reuse a subquery

From
Thom Brown
Date:
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

Re: reuse a subquery

From
Charles Holleran
Date:

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

Re: reuse a subquery

From
Thom Brown
Date:
On 9 August 2010 15:34, Charles Holleran <scorpdaddy@hotmail.com> wrote:
>
>
>> 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.
>

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

Re: reuse a subquery

From
Thomas Kellerer
Date:
Charles Holleran wrote on 09.08.2010 16:04:
> 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;
>

What about:

WITH temp_a (col1, col2, col3) AS
(
    SELECT col1, col2, col3
    FROM table_a
    WHERE c = 3
)
SELECT *
FROM temp_a t1
   JOIN temp_a t2 ON (t2.d = t1.d + 5)
WHERE t2.d IS NULL
ORDER BY t1.d;

Regards
Thomas

Re: reuse a subquery

From
"Oliveiros d'Azevedo Cristina"
Date:
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.