subselect return in where clause

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

subselect return in where clause
2+ messages / 2 participants
[nested] [flat]

* subselect return in where clause
@ 2003-01-28 11:21  Michael Hostbaek <mich@the-lab.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Michael Hostbaek @ 2003-01-28 11:21 UTC (permalink / raw)
  To: pgsql-sql

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 /*

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: subselect return in where clause
@ 2003-01-28 15:56  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michael Hostbaek <mich@the-lab.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2003-01-28 15:56 UTC (permalink / raw)
  To: Michael Hostbaek <mich@the-lab.org>; +Cc: pgsql-sql

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 ilike
 stuff.match) as other_stuff from stuff
where
(select other_stuff from other_table where other_table.stuff ilike
 stuff.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_stuff from 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-01-28 15:56 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-28 11:21 subselect return in where clause Michael Hostbaek <mich@the-lab.org>
2003-01-28 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox