How are these two SQL statements different?

Question

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?

You should research inner join, look at http://www.w3schools.com/sql/sql_join_inner.asp — Jess, Mar 30 '11 at 03:33
To translate both query statements: show all records from table_one as long as some record somewhere in table_two has an ID of $x. Did I get that right? — bob-the-destroyer, Mar 30 '11 at 03:58
@bob-the-destroyer: there will be all records from t1 **multiplied** to the set of t2.row = id. It is called cartesian product. — zerkms, Mar 30 '11 at 04:08
@zerkms: just speaking strictly on the expected results, especially when the join is on an arbitrary value rather than any fields with matching values. Also, just testing the queries shows these same results. — bob-the-destroyer, Mar 30 '11 at 04:12
@bob-the-destroyer: oh yes. Missed that fact. If `row` is a PK - then you're right, sorry. — zerkms, Mar 30 '11 at 04:46
@zerkms: actually, you're correct. Well, it's either a cartesian result or nothing depending on the existence of that arbitrary value in t2. I think I'll just slowly back away from this post now. — bob-the-destroyer, Mar 30 '11 at 04:56
@jon3laze I think your comment is out of date because I went to w3fools just now and it looks like they've rescinded their previous objections to w3schools (unless I'm missing something). — ryvantage, Sep 17 '18 at 18:44
@ryvantage You are correct. I have removed the comment as it is no longer relevant. Thank you for notifying me. — jon3laze, Dec 19 '18 at 07:42
@jon3laze no prob. I'm going to delete these comments in a couple of hours — ryvantage, Dec 19 '18 at 12:49

score 2 · Accepted Answer · answered Mar 30 '11 at 03:43

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.

score 0 · Answer 2 · answered Mar 30 '11 at 03:35

0

Yes, queries are semantically the same.

Though both produce cartesian product.

answered Mar 30 '11 at 03:35

zerkms

249,484
69
436
539

score 0 · Answer 3 · edited May 23 '17 at 09:58

0

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.

edited May 23 '17 at 09:58

Community

1
1

answered Mar 30 '11 at 03:41

Cᴏʀʏ

105,112
20
162
194

The ansi-92 syntax will allow left joins also, which is harder using the older syntax. It also makes the query easier to understand, so you don't mistakenly get the cartesian product that zerkms mentions. – xecaps12 Mar 30 '11 at 03:51

How are these two SQL statements different?

3 Answers3