THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in John Paul Cook (Entire Site) Search

# Don't let the facts interfere with the truth

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;

Published Wednesday, February 27, 2013 11:22 PM by John Paul Cook

#### Marc Shapiro said:

It's almost as if there were no distinction between a condition in the ON clause of an INNER JOIN and the WHERE clause following it; and no distinction between a CROSS JOIN and INNER JOIN except that the INNER JOIN syntactically requires an ON clause.

February 28, 2013 7:16 AM

#### Sunil Boga said:

Thank you John for the post :)

February 28, 2013 11:28 AM

#### Jānis said:

And having no "No join predicate" warning also does not mean there is no Cartesian join.

http://dba.stackexchange.com/questions/35082/what-exactly-does-no-join-predicate-mean-in-sql-server

March 5, 2013 3:43 AM