Difference between these joins

Question

Is there any difference between

SELECT *
FROM TABLE_A a
JOIN TABLE_B b
  ON a.propertyA = b.propertyA

And the query

SELECT * from TABLE_A a, TABLE_B b where a.propertyA=b.propertyA.

The second is still considered a "join", but it's the old-school version — Bohemian, Sep 10 '12 at 05:54

score 5 · Accepted Answer · answered Sep 10 '12 at 05:52

INNER JOIN is ANSI (SQL-92) syntax which you use on the first one. It is generally considered more readable, especially when you join lots of tables.

The WHERE syntax (SQL-89) is more relational model oriented. A result of two tables JOIN'ed is a cartesian product of the tables to which a filter is applied which selects only those rows with joining columns matching.

It's easier to see this with the WHERE syntax.

I'd rather go on the ANSI type join because if you some how omit the ON clause, an error is generated whereas the old type of join if you omit the condition on where clause will not produce an error message and thus it will generate cartesian product.

+1 for nice explanation, would be great if you could add links to the documentation for ANSI(SQL-92) and (SQL-89) — Habib, Sep 10 '12 at 06:13

score 2 · Answer 2 · answered Sep 10 '12 at 05:53

The two examples are the same. Both perform an INNER JOIN operation (even if it's just JOIN in the 2nd example) which basically returns all rows that contain matching results in relation to the ON clause.

My guess is that the JOIN and INNER JOIN operations are just a bit faster since they're designed for that specific purpose while SELECT statements can be modified around to do much more.

score 0 · Answer 3 · answered Sep 10 '12 at 05:51

The "join" version has been around about 20 years and is preferred because it clearly identifies predicates used for the join as opposed to those used for filtering results.
It also allow outer joins if used with left join (where you still get table_a's row if there isn't a matching row in table_b).

The "comma" version doesn't allow outer joins (you won't get table_a's row if there isn't a matching row in table_b)

Difference between these joins

3 Answers3