MySQL Joins

[asif49]

The following two are methods of joining two tables together but what I want to know is whether the two methods actually have any differences. I tested the two methods on two tables and they didn't give different results but maybe my two tables were just an anomaly. Are these different at all?

Method 1

SELECT * FROM table1, table2;

Method 2

SELECT * FROM table1 NATURAL JOIN table2;

[whiteyoh]

Im not sure if this is any use, but when i collect data i use dot notation, and on joins its usually a LEFT JOIN which will return a value even if values are empty

Select table1.columnname as name1, table2.columnname as name 2 FROM table1 JOIN table1.id = table2.id

[asif49]

which will return a value even if values are

I'm not sure how that answers my question :/

I basically want to know whether there is a different between putting in the words "NATURAL JOIN" and just simply putting a comma next to two tables (the two methods above) in the way that they will return the data. Is there any difference between these two?

NATURAL JOIN is just shorthand for LEFT JOIN (AFAIK). I think it's the same as just listing tables. However using the 'LEFT JOIN' syntax you need to stipulate the linked fields with the ON clause. I have to be honest, although this is 'easy', I much prefer the 'full' method.

[JorgeM]

I beleive that there is a difference. The SQL code you have in Method #1 is the equivalent to a cross-join.

SELECT * FROM table1, table2;

SELECT * FROM table1 CROSS JOIN table2;

[|-|x]

I believe that Natural Joins are more akin to inner joins than left joins.

Have a read of the Natural Join section here and in particular note the dangers described as modifying table structure will result in differences in output.

Listing the tables in a from clause, as I understand it, will always join every row in table1 to every row in table2 regardless of the data.

Yes, that's right. It's a cross join (doh). It'll give you a cartesian product. I was testing on two tables, one with just a single record. That'll teach me. Adding a record gave me the 'obvious' cartesian product.