Re: Algorithm for writing joins in SQL
Date: Wed, 2 Jul 2003 17:54:06 +0200
"sello" <sello_at_operamail.com> schrieb im Newsbeitrag
> 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
> 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!
- 50000 Records
- 1000 Records
- 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