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

Re: reuse a subquery

From: Thom Brown <thom(at)linux(dot)com>
To: Charles Holleran <scorpdaddy(at)hotmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: reuse a subquery
Date: 2010-08-09 14:12:51
Message-ID: AANLkTimgwNreCVg-Gm+Gj3OEGi80qJM0SjFj8G-ghH5o@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

Responses

pgsql-novice by date

Next:From: Charles HolleranDate: 2010-08-09 14:34:21
Subject: Re: reuse a subquery
Previous:From: Charles HolleranDate: 2010-08-09 14:04:33
Subject: reuse a subquery

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