Title: SQL Probable Cartesian join
Tags: sql;database
Question: 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)
(t1.col1 = t3.col2
and t2.col2 = t1.col1)
Comment: The implicit syntax should never be used in any query let alone a complex one. The fact that you have to ask this question is one reson why. If you are doing a code analyzer it should report any implicit join as incorrect.


Here is another answer: 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)
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
Comment for this answer: @onedaywhen, *shrug* - when I see t2.*, I think that duplication introduced by joining is undesire-able and I revert to filtering.  See my latest edit for a more traditional JOIN (which is in fact easier to write/read).
Comment for this answer: @Nehu, I was answering in my own voice... I don't know what a code analyzer should do except possibly warn on the cartesian syntax.
Comment for this answer: Your rewrite demonstrates nicely why 'old style' joins remain in the SQL Standard (and probably always will) i.e. because the same joins are much more complex (to the human coder's eye) to express using SQL-92 infix notation.
Comment for this answer: Thanks David for the quick reply. So, what do you say, should the code analyzer flag the query as "Probably containing a cartesian Join"?
Comment for this answer: When a query returns duplicate rows it cannot be decomposed simply into a UNION

Here is another answer: No, that is no a cartesian join. It may be a bad one but not a cartesian.
Comment for this answer: Thanks Octavia for the quick reply.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 2.5', 'CreationDate': '2010:00:11/12/10', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><database>', 'Title': 'SQL Probable Cartesian join', 'ViewCount': 149, 'id': 4165477, 'user_AboutMe': '', 'user_AccountId': 215602.0, 'user_DisplayName': 'Nehal Damania', 'user_Reputation': 8311.0, 'user_Views': 196.0, 'user_id': 470347.0}}