Prev: Top 2 from count
Next: CREATE BLANK ROWS BETWEEN DATA
From: Plamen Ratchev on 12 Aug 2010 11:51
If you have the same join predicates then the results of the multiple
inner joins (no matter how you group or rearrange them) will be always
the same. Try it with example and see.

The order of tables in JOIN matters only when you deal with outer
joins (because of the added outer rows).

--
Plamen Ratchev
http://www.SQLStudio.com
From: J.B. Moreno on 12 Aug 2010 23:25
In article <eMsN3FjOLHA.5700(a)TK2MSFTNGP04.phx.gbl>, tshad
<tfs(a)dslextreme.com> wrote:

> Right
>
> but would:
> A JOIN B
> B JOIN C
> C JOIN D
>
> be the same thing as
> A JOIN B
> C JOIN D
> B JOIN C
>
> If so, the 3rd join IS NOT joining against the previous result set.

The above syntax isn't close to valid (no join conditions, no commas to
indicate cross joins), but any way you look at it, the results are the
same (although performance could vary)


select *
from A
inner join B on a.x = b.x
inner join C on b.y = c.y
inner join D on c.z = d.z

Previous results are used at each step.

or

select *
from A
inner join B on a.x = b.x
inner join (select * C inner join D on c.z = d.z) CJ on b.y = CJ.y

Result of A/B combined with results C/D on condition b.y=c.y Because
of the b.y=c.y join condition it doesn't matter how many c/d records
there are, all except the ones that match with b are discarded, because
the c/d condition is the same either way, you get the same number of
records in the end.

The only way it would make a difference is if you meant

select *
from A
cross join B
cross join C
cross join D
cross join B
cross join C


Where you have cartesian joins, and include B and C twice. If you
don't include tables more than once, then cross join or inner join or a
mixture of the two, as long as the same conditions are applied you'll
end up with the same result set.

--
J.B. Moreno
First  |  Prev  | 
Pages: 1 2 3
Prev: Top 2 from count
Next: CREATE BLANK ROWS BETWEEN DATA