Re: Reuse of Subselects

On Tue, Feb 17, 2004 at 13:02:43 +0100,
  Holger Marzen  wrote:
> Hi all,
> 
> if I have something like this:
> 
> SELECT column1,
>        (... complicated subselect ...),
>        column1 - (... same subselect as above ...)
> FROM table1;
> 
> do I really have to rewrite the subselect a 2nd time if I need that
> result in another column's expression?

You should be able to use a join. The syntax won't be simpler, but you
might get nearly a 2 fold speed up.

Assuming that the subselect depends on the current row, you want to do
something like:

SELECT (
  SELECT column1, complicated, column1 - complicated FROM
    (complicated select AS complicated) AS comptable)
  ) FROM table1;