When you are joining N tables, it is true that unless you have N-1 joins, you're going to have a Cartesian product. Today I saw a case where there were N-2 joins and no evidence of a Cartesian product in the result set. No additional rows in the result set - that's a fact. But what about the truth, that a Cartesian product occurs when you have less than N-1 joins?
In the code sample below where there are N-2 joins, a Cartesian product does occur, but the where clause prevents you from seeing it. The where clause filters out the superfluous row leaving you with a single row of output.
Just because you don't see a Cartesian product doesn't mean there isn't one. With different data, you would see the Cartesian product. When you are maintaining code, look for N-1 joins if you really want to be both thorough and safe.
create
table #table1 (
a int
,b int
);
create
table #table2 (
a int
,b int
);
create
table #table3 (
a int
,b int
);
insert
into #table1 (a,b) values (11,12);
insert into #table2 (a,b) values (11,22);
insert into #table3 (a,b) values (11,32);
insert into #table3 (a,b) values (311,321);
select
*
from #table1 t1
inner join #table2 t2
on t1.a = t2.a;
select
*
from #table1 t1
inner join #table2 t2
on t1.a = t2.a
, #table3 t3
where t3.b = 32; --comment this out to see the Cartesian product
select
*
from #table1 t1
inner join #table2 t2
on t1.a = t2.a
inner join #table3 t3
on t2.a = t3.a;