Difference between these associations

Question

Is there a difference between

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

And request

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

Answer 1

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

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

It is easier to see this with the WHERE syntax.

I would rather enable a connection of type ANSI , because if you omit the ON clause somehow, an error is generated, whereas the old connection type, if you omit the condition in where , will not produce an error message and, thus, it will generate Cartesian products.

Answer 2

Two examples are the same. Both perform an INNER JOIN operation (even if it is SAVE in the second example), which basically returns all rows containing the corresponding results for the ON clause.

I assume that the JOIN and INNER JOIN operations are slightly faster as they are for this specific purpose, while the SELECT statements can be changed around to do much more.

Answer 3

The join version is about 20 years old and is preferred because it clearly identifies the predicates used to join, rather than those used to filter the results.
It also allows external joins if used with a left join (where you still get table_a if there is no matching row in table_b.)

The comma version does not allow external connections (you will not get the table_a row if there is no corresponding row in the table_b)