Thread: where clauses and multiple tables
Hello, Is it possible to join tables in the where clause of a statement? I ask because I have a situation where I only have access to the where clause of a select statement on a single table, yet I want to perform a join on multiple tables. eg: Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses still refer to a single table and the join referencing the second table occurs in the where clause. For example, something like this: SELECT foo.foo_id, foo.name FROM foo WHERE (SELECT * FROM foo, bar WHERE ...) foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the "where exists" clause, but that's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Thanks in advance! Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Scott Frankel-3 wrote: > > Is it possible to join tables in the where clause of a statement > > I've explored the "where exists" clause, but that's not supported by > the application toolkit I'm using. AFAIK, I've only got access to > where ... > > Thanks in advance! > Scott > Not entirely sure of the objective, but perhaps an inner join is the topic for which you are looking? Else, I'd suggest one of the following formats (which may, or may not, be available to you). SELECT foo.* FROM (SELECT * FROM bar WHERE bar."bar_id"='value') AS foo WHERE .... SELECT foo.* FROM foo WHERE foo."bar_id" IN (SELECT bar."bar_id" FROM bar WHERE ...); SELECT foo.* FROM foo WHERE foo."bar_id" IN (SELECT bar."bar_id" FROM(SELECT ....) AS bar WHERE ...); -- View this message in context: http://www.nabble.com/where-clauses-and-multiple-tables-tp25355350p25355681.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL] where clauses and multiple tables: >Is it possible to join tables in the where clause of a statement? [snip] >Given a statement as follows: > > SELECT foo.foo_id, foo.name > FROM foo, bar > WHERE foo.bar_id = bar.bar_id > AND bar.name = 'martini'; Just use an IN predicate: SELECT foo_id, name FROM foo WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini'); This is frequently called a semi-join. -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================
On Sep 8, 2009, at 4:02 PM, David W Noon wrote: > On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL] > where clauses and multiple tables: > >> Is it possible to join tables in the where clause of a statement? > [snip] >> Given a statement as follows: >> >> SELECT foo.foo_id, foo.name >> FROM foo, bar >> WHERE foo.bar_id = bar.bar_id >> AND bar.name = 'martini'; > > Just use an IN predicate: > > SELECT foo_id, name FROM foo > WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini'); > > This is frequently called a semi-join. This looks very promising. Thanks for the info! Scott > -- > Regards, > > Dave [RLU #314465] > = > ====================================================================== > david.w.noon@ntlworld.com (David W Noon) > = > ====================================================================== > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Scott Frankel President Circle-S Studios www.circlesfx.com 510-339-7477 (o) 510-332-2990 (c) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Sep 8, 2009, at 4:02 PM, David W Noon wrote: > On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL] > where clauses and multiple tables: > >> Is it possible to join tables in the where clause of a statement? > [snip] >> Given a statement as follows: >> >> SELECT foo.foo_id, foo.name >> FROM foo, bar >> WHERE foo.bar_id = bar.bar_id >> AND bar.name = 'martini'; > > Just use an IN predicate: > > SELECT foo_id, name FROM foo > WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini'); > > This is frequently called a semi-join. This looks very promising. Thanks for the info! Scott > -- > Regards, > > Dave [RLU #314465] > = > ====================================================================== > david.w.noon@ntlworld.com (David W Noon) > = > ====================================================================== > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Scott Frankel President Circle-S Studios www.circlesfx.com 510-339-7477 (o) 510-332-2990 (c) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 09/09/2009, at 9:02 AM, David W Noon wrote: > On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL] > where clauses and multiple tables: > >> Is it possible to join tables in the where clause of a statement? > [snip] >> Given a statement as follows: >> >> SELECT foo.foo_id, foo.name >> FROM foo, bar >> WHERE foo.bar_id = bar.bar_id >> AND bar.name = 'martini'; > > Just use an IN predicate: > > SELECT foo_id, name FROM foo > WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini'); > > This is frequently called a semi-join. By the way, folks, do you think there may be performance gain or loss from rewriting this with an explicit JOIN? E.g.: SELECT DISTINCT foo.foo_id, foo.name FROM foo JOIN bar ON foo.bar_id = bar.bar_id WHERE bar.name='martini'; Thanks! Yar
Yaroslav Tykhiy wrote:
> By the way, folks, do you think there may be performance gain or loss
> from rewriting this with an explicit JOIN? E.g.:
>
> SELECT DISTINCT foo.foo_id, foo.name FROM foo JOIN bar ON foo.bar_id =
> bar.bar_id WHERE bar.name='martini';
I would expect that to be more efficient as its the 'proper' SQL way of
doing things, and the optimizer will do a better job on it, especially
if foo.bar_id is a FK to bar.bar_id's primary key.
btw, can't this be written...
SELECT DISTINCT foo.foo_id, foo.name
FROM foo JOIN bar ON bar_id
WHERE bar.name='martini';
?
On Sep 8, 2009, at 4:02 PM, David W Noon wrote: > On Tue, 8 Sep 2009 14:25:20 -0700, Scott Frankel wrote about [GENERAL] > where clauses and multiple tables: > >> Is it possible to join tables in the where clause of a statement? > [snip] >> Given a statement as follows: >> >> SELECT foo.foo_id, foo.name >> FROM foo, bar >> WHERE foo.bar_id = bar.bar_id >> AND bar.name = 'martini'; > > Just use an IN predicate: > > SELECT foo_id, name FROM foo > WHERE bar_id IN (SELECT bar_id FROM bar WHERE name = 'martini'); > > This is frequently called a semi-join. This looks very promising. Thanks for the info! Scott > -- > Regards, > > Dave [RLU #314465] > = > ====================================================================== > david.w.noon@ntlworld.com (David W Noon) > = > ====================================================================== > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Scott Frankel President Circle-S Studios www.circlesfx.com 510-339-7477 (o) 510-332-2990 (c) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Tue, 08 Sep 2009 18:50:49 -0700, John R Pierce wrote about Re: [GENERAL] where clauses and multiple tables: >Yaroslav Tykhiy wrote: >> By the way, folks, do you think there may be performance gain or >> loss from rewriting this with an explicit JOIN? E.g.: >> >> SELECT DISTINCT foo.foo_id, foo.name FROM foo JOIN bar ON foo.bar_id >> = bar.bar_id WHERE bar.name='martini'; > >I would expect that to be more efficient as its the 'proper' SQL way >of doing things, Actually, since the "bar" table does not supply any of the result columns, the IN predicate is a more idiomatic (or "proper") way of coding the query. >and the optimizer will do a better job on it, >especially if foo.bar_id is a FK to bar.bar_id's primary key. The optimizer *should* produce the same plan, either way. >btw, can't this be written... > > SELECT DISTINCT foo.foo_id, foo.name > FROM foo JOIN bar ON bar_id > WHERE bar.name='martini'; The DISTINCT qualifier potentially changes the semantics, so the immediate answer is "No". -- Regards, Dave [RLU #314465] ======================================================================= david.w.noon@ntlworld.com (David W Noon) =======================================================================