THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

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

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement