mySQL JOIN usage

Question

Possible Duplicate:
INNER JOIN ON vs WHERE clause

I started as web developer about 1 year ago. Since then I've gone through PHP, mySQL, Javascript, jQuery, etc. I think I've learned some things in this time. But when it comes to SQL I'm a real noob. I just do a couple of SELECT, INSERT, UPDATE, and using some functions like SUM or UNIX_TIMESTAMP, etc.

I've come across the JOIN functions, and it seems to be pretty useful, but I don't see the difference of using JOIN or some WHERE clauses to "join" the data betwwen tables.

I read this article (spanish) about join. and I can't really see the usefulness of it. For example:

Assuming this dataset:

id nombre       id  nombre
-- ----         --  ----
1  Pirata       1   Rutabaga
2  Mico         2   Pirata
3  Ninja        3   Darth Vader
4  Spaghetti    4   Ninja

Wouldn't this query: SELECT * FROM TablaA INNER JOIN TablaB ON TablaA.name = TablaB.name produce the same results as SELECT * FROM TablaA, TablaB WHERE TablaA.name = TablaB.name ?

You just discovered the syntactical shortcut for an inner join. There are other types of joins. — Brian Roach, Aug 15 '12 at 23:01
http://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line — dugas, Aug 15 '12 at 23:04
Thanks! From now on I will start using JOIN, and I agree, I have some large unreadable queries with that old WHERE clauses — davidaam, Aug 15 '12 at 23:14

score 4 · Accepted Answer · answered Aug 15 '12 at 23:06

4

Yes, they will produce the same result, however, SELECT * FROM TablaA, TablaB WHERE TablaA.name = TablaB.name is an older method of performing a join that does not meet the SQL-92 standard. It is also a lot more difficult to read once your queries start to become very complex. It is better to stick with the explicitly declared INNER JOIN format.

answered Aug 15 '12 at 23:06

JamieSee

12,696
2
31
47

Thanks! I'm going to study the SQL JOIN to get a better understanding of it – davidaam Aug 15 '12 at 23:16

score 2 · Answer 2 · answered Aug 15 '12 at 23:09

Your example happens to yield the same results, but realize there are other types of joins - LEFT JOIN being the only other one I usually use - and the concept of joining is separate from the concept of filtering the results with the WHERE clause. Sometimes WHERE clauses can get complicated enough without all the join conditions mixed in there as well. Keep your SQL easy to maintain and join in the JOIN clause, not in the WHERE clause.

mySQL JOIN usage

2 Answers2