is NATURAL JOIN any better than SELECT FROM WHERE in terms of performance?

sql select product cartesian
Ashkan Kh. Nazary picture Ashkan Kh. Nazary · Jun 17, 2010 · Viewed 18.7k times · Source

Possible Duplicate:
Inner join vs Where

Today I got into a debate with my project manager about Cartesian products. He says a 'natural join' is somehow much better than using 'select from where' because the later cause the db engine to internally perform a Cartesian product but the former uses another approach that prevents this. As far as I know, the natural join syntax is not any different in anyway than 'select from where' in terms of performance or meaning, I mean you can use either based on your taste.

SELECT * FROM table1,table2 WHERE table1.id=table2.id
SELECT * FROM table1 NATURAL JOIN table2

please elaborate about the first query causing a Cartesian product but the second one being somehow more smart

Answer

gbn picture gbn · Jun 17, 2010

The correct way should be explicit with filters and joins separated

SELECT * FROM table1 JOIN table2 ON table1.id = table2.id

NATURAL JOINS may be easy and "clean" but more likely to be utterly unpredictable...

Edit, May 2012.

The accepted answer for the duplicate doesn't actually answer NATURAL JOIN.
These links discuss in further detail.

tl;dr

Performance isn't the issue: but your queries should be reliable and predictable which NATURAL JOIN certainly isn't.

"JOIN in the WHERE" aka implied JOIN aka what you call "Cartesian" is also bad as per these links (the same applies to Oracle as well as SQL Server)

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
SQL SELECT WHERE field contains words

I need a select which would return results like this: SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3' And I need all results, i.e. this includes strings with 'word2 word3 word1' or 'word1 word3 word2' …

Read More
SQL update from one Table to another based on a ID match

I have a database with account numbers and card numbers. I match these to a file to update any card numbers to the account number, so that I am only working with account numbers. I created a view linking the …

Read More