Joins vs. Where clause

[skiflyer]

Two syntaxes, same output... wondering if one is better in any way other than readability.

Simple case of two tables representing the weak entity and one of the entities of a many to many relationship. We'll pretend it's a list of keywords.

SELECT word 
FROM file_has_words JOIN words 
ON (file_has_words.word_id = words.word_id) 
WHERE file_has_words.file_id=<some variable>

or I can just do
SELECT word
FROM file_has_words, words
WHERE file_has_words.file_id=<some variable>
AND file_has_words.word_id = words.word_id

If I'm remembering my DB classes right, the two should be executed very similarly, both are basically going to do a full cross join, then elemented what doesn't match, right?

Eh, those classes is starting to get a little fuzzy, and of course it wasn't MySQL specific, so if you folks could help me out with this one I'd be appreciative.

-Rob

 

[sleipnir214]

Not a cross join. Something like:

SELECT word
FROM file_has_words, words

without a WHERE clause to related the tables will perform a cross join (or if you prefer, a Cartesian product).


The two examples you've given are both interpreted my MySQL as inner joins.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[skiflyer]

sorry, Cartesian product is what I meant when I said cross join... I need to brush up on my terms.

So, those two come out of the parser as equivalent? Would you consider them to be a good solution, or is this a place for something more clever?

-Rob

 

[sleipnir214]

I don't know of anything else for something that simple. It's probably just a stylistic thing.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

stylistic, definitely -- but with genuine advantages too

if you use JOIN syntax, then all join conditions are automatically removed from the WHERE clause and isolated to the specific two tables they join, which can be a substantial gain in clarity in a multi-table join statement

further, if you get in the habit of using JOIN syntax, you will have very little syntax to change when the time comes to change a given query from an inner to an outer join

rudy

http://rudy.ca/