Re: Algorithm for writing joins in SQL

From: Andreas Schmidt <a_j_schmidt_at_rocketmail.com>
Date: Wed, 2 Jul 2003 17:54:06 +0200
Message-ID: <bduv8a$10o7i0$1_at_ID-13084.news.dfncis.de>


"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 - 17:54:06 CEST

Original text of this message