On Tue, 08 Sep 2009 18:50:49 -0700, John R Pierce wrote about Re: 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@xxxxxxxxxxxx (David W Noon) ======================================================================= -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
