Does it cause a Cartesian join if you include a table nothing is selected from?

Question

Would this be a Cartesian join:

select
dog_t.type
dog_t.breed
date_t.date
from
dog_t,
cat_t,
date_t
where
dog_t.date_key=date_t.date_key

Would including the cat_t table cause a Cartesian join... because it isn't joined to anything?

See this question for more info: http://stackoverflow.com/q/14780307/11683 — GSerg, Feb 13 '13 at 01:32

Helio Santos · Accepted Answer · 2013-02-13T01:33:16.273

3

Yes. You need to add cat_t.date_key=date_t.date_key to avoid the cartesian join.

But you still have a cross-join problem with tables cat_t and dog_t. I would say you should union a separated select from each table.

edited Feb 13 '13 at 01:33

answered Feb 13 '13 at 01:26

Helio Santos

This is a purely hypothetical example. I guess I just don't understand why the cartesian happens... does the sql just buffer fields it doesn't need? – tylercomp Feb 13 '13 at 01:35
@tylercomp I see your point. But don't you really need it? Imagine if for some dark reason you need the information of `cat_t` implicit on the results (adding redundant occurrences of cat_t rows) – Helio Santos Feb 13 '13 at 01:43
Yes it is definitively bad practice, it is just curious to me that it actually causes a Cartesian join. Helps me understand how sql works under the hood a little bit. Thanks I appreciate the answer! – tylercomp Feb 13 '13 at 01:54
Two tables in a query will cause a Cartesian join that is why you have to add a condition. I am trying to say that Cartesian joins is the way sql works. – Helio Santos Feb 13 '13 at 02:05

1 Answers1