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?

[diafol]

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;

[Hearth]

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.

[diafol]

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.