Thread: subselect return in where clause

Group,

Is it possible to use a subselect result in a where statement ? If so,
how ?

Fx.
select some_stuff, more_stuff, 
(select other_stuff from other_table where other_table.stuff ilike
stuff.match) as other_stuff from stuff where other_stuff = 'hello'

thanks.
-- 
Best Regards,Michael Landin Hostbaek FreeBSDCluster.org - an International Community
*/ PGP-key available upon request /*

Michael Hostbaek <mich@the-lab.org> writes:
> select some_stuff, more_stuff, 
> (select other_stuff from other_table where other_table.stuff ilike
> stuff.match) as other_stuff from stuff where other_stuff = 'hello'

The above is nonsensical: WHERE cannot refer to values computed in
the SELECT list, because WHERE logically occurs before the SELECT
list is executed (indeed, the SELECT list will not be executed at
all, if WHERE returns false).

You could repeat yourself:

select some_stuff, more_stuff, 
(select other_stuff from other_table where other_table.stuff ilikestuff.match) as other_stuff from stuff
where
(select other_stuff from other_table where other_table.stuff ilikestuff.match) = 'hello'

or if it really bugs you to write the expression twice, perhaps
use a sub-select:

select * from
(select some_stuff, more_stuff, (select other_stuff from other_table where other_table.stuff ilike stuff.match) as
other_stufffrom stuff) as subselect
 
where other_stuff = 'hello'

although you shouldn't have any illusions about this being more
efficient than writing the expression twice.
        regards, tom lane