Re: Algorithm for writing joins in SQL

"sello" <sello_at_operamail.com> schrieb im Newsbeitrag news:c97b9fa.0306262043.44db5518_at_posting.google.com...
> sello_at_operamail.com (sello) wrote:
> > Hi, I have a reporting application that needs to dynamically generate
> > the sql query based on conditions & sorting. The underlying database
> > is a simple star structure with inner joins between tables only.
> >
> > I need to be able to create the nested inner join syntax as is done in
> > SQL server and MS Access query builders.
>
>
> Think I've figured it...
> Since the query is to return the intersect of all sets it doesn't
> matter in which order the tables are joined as long as they are all
> included.
> A n B n C = A n (B n C) = (B n A) n C with appropriate conditions
> applied to each intersection.

But it can make a huge difference in performance and memory usage!

Example

1. 50000 Records
2. 1000 Records
3. 8 Records

A & B: 1000 Records
B & C: 5 Records

So (B & C) & A would need less operations.

Most SQL Engines have strategies to find the best query plan.

Andreas Received on Wed Jul 02 2003 - 10:54:06 CDT