where clauses and multiple tables

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)
=======================================================================

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 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)

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 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)
=======================================================================