CODEWITHSUNDEEP
mysqlsql
John Smith
John Smith

Reputation: 8811

How are these two SQL statements different?

1:

$query = mysql_query("
SELECT table_one.row 
FROM table_one 
INNER JOIN table_two 
ON table_two.row = $id");

2:

$query = mysql_query("
SELECT table_one.row 
FROM table_one, table_two 
WHERE table_two.row = $id");

Are these just two ways of writing the same thing?

Upvotes: 1

Views: 90

Answers (3)

Chris
Chris

Reputation: 58142

They both achieve the same results but with different approaches. Though you are misusing the ON clause.

I would suggest something like this:

$query = mysql_query("
SELECT table_one.row 
FROM table_one 
INNER JOIN table_two 
ON table_one.id = table_two.id
WHERE table_two.row = $id");

Quote from mysql site:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

Upvotes: 2

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

The first query is using the newer ANSI-92 syntax, while your second in using the older ANSI-89 syntax. Both should produce identical results.

Moreover, read this post: Why isn't SQL ANSI-92 standard better adopted over ANSI-89?

Personally, and I hope most would agree, I prefer ANSI-92 which uses the "JOIN" syntax. As was mentioned in the referenced post, it lets you separate your JOIN constraints from your WHERE or filter constraints which improves readability.

Upvotes: 0

zerkms
zerkms

Reputation: 254916

Yes, queries are semantically the same.

Though both produce cartesian product.

Upvotes: 0

Related Questions