CODEWITHSUNDEEP
sqldatabase
Nehal Damania
Nehal Damania

Reputation: 9018

SQL Probable Cartesian join

I am creating a SQL Static Code analyzer. Should the following query be considered as containing a cartesian join: select t2.*
from test1 t1,
test2 t2,
test3 t3
where (t1.col1 = t2.col2
and t2.col3 = t3.col4)
or
(t1.col1 = t3.col2
and t2.col2 = t1.col1)

Upvotes: 0

Views: 161

Answers (2)

Amy B
Amy B

Reputation: 110191

Should the following query be considered as containing a cartesian join

I would consider it to be so without analysis, and I'd demand it be rewriten without the commas. Then I'd demand it be written without the OR.

And here it is:

select t2.*
from test2 t2
where t2.col2 in (SELECT t1.col1 FROM test1 t1 WHERE t1.col1 is not null)
  and t2.col3 in (SELECT t3.col4 FROM test3 t3 WHERE t3.col4 is not null)
UNION
select t2.*
from test2 t2
where t2.col2 in (SELECT t1.col1 FROM test1 t1 WHERE t1.col1 is not null)
  and t2.col2 in (SELECT t3.col2 FROM test3 t3 WHERE t3.col2 is not null)

Or more traditionally:

select t2.* 
from test2 t2 
  JOIN test1 t1 ON t1.col1 = t2.col2
  JOIN test3 t3 ON t2.col3 = t3.col4
UNION 
select t2.* 
from test2 t2 
  JOIN test1 t1 ON t1.col1 = t2.col2
  JOIN test3 t3 ON t2.col2 = t3.col2

Upvotes: 1

Otávio Décio
Otávio Décio

Reputation: 74290

No, that is no a cartesian join. It may be a bad one but not a cartesian.

Upvotes: 3

Related Questions