0

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;
5
Contributors
6
Replies
7
Views
5 Years
Discussion Span
Last Post by diafol
0

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

0

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?

0

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.

0

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;
0

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.

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.