Re: Multiple selects versus join

On Tue, 2002-08-20 at 04:00, Jean-Christian Imbeault wrote:
> Is it more efficient to do multiple selects on multiple tables to get
> data or do a join of those tables and extract the data from the
> resulting temp table?
>
> For example:
>
> select name from a where id=1;
> select pub_date from b where id=1;
> pub_id = select publisher_id from c where id=1;
> select pub_name from d where id = pub_id;
>
> or (I don't know the syntax for join so this is just pseudo-sql);
>
> select name, pub_date, pub_name from join((join(A, B, C) on id), D) on
> pub_id where id = 1;
>
> All my tables are related to each other so I am wondering which is more
> efficient, do multile selects or joining the tables. (Joining the table
> would return just one row).

Yo!

If you *know* that you will get just one row, you know more than the
database system can know. Generally: a join of tables with n and m rows
can return at most n*m rows, so joins are not very efficient compared to
multiple selects. A join is basically a select on both tables with
additional processing done. If you do multiple selects and can do the
join 'by hand' because you know much about the data you expect, this
will almost always be faster.

Of course, for very small databases, the overhead of executing multiple
statements instead of only one statement may be significant.

NOTE: I'm not a db pro or anything.

cheers
-- vbi

--
secure email with gpg                         http://fortytwo.ch/gpg