INNER JOIN keywords | with and without using them

Question

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

SELECT * FROM TableA, TableB
where TableA.name = TableB.name

Which is the preferred way and why? Will there be any performance difference when keywords like JOIN is used?

Thanks

Answer 1

find out by using EXPLAIN SELECT …

it depends on the engine used, on the query optimizer, on the keys, on the table; on pretty much everything

Answer 2

Use the first one, as it is:

• More explicit
• Is the Standard way

As for performance - there should be no difference.

Answer 3

The second way is the classical way of doing it, from before the join keyword existed.

Normally the query processor generates the same database operations from the two queries, so there would be no difference in performance.

Using join better describes what you are doing in the query. If you have many joins, it's also better because the joined table and it's condition are beside each other, instead of putting all tables in one place and all conditions in another.

Another aspect is that it's easier to do an unbounded join by mistake using the second way, resulting in a cross join containing all combinations from the two tables.

Answer 4

Most current databases will optimize both of those queries into the exact same execution plan. However, use the first syntax, it is the current standard. By learning and using this join syntax, it will help when you do queries with LEFT OUTER JOIN and RIGHT OUTER JOIN. which become tricky and problematic using the older syntax with the joins in the WHERE clause.

Answer 5

In some SQL engines the second form (associative joins) is depreicated. Use the first form.

Second is less explicit, causes begginers to SQL to pause when writing code. Is much more difficult to manage in complex SQL due to the sequence of the join match requirement to match the WHERE clause sequence - they (squence in the code) must match or the results returned will change making the returned data set change which really goes against the thought that sequence should not change the results when elements at the same level are considered.

When joins containing multiple tables are created, it gets REALLY difficult to code, quite fast using the second form.

EDIT: Performance: I consider coding, debugging ease part of personal performance, thus ease of edit/debug/maintenance is better performant using the first form - it just takes me less time to do/understand stuff during the development and maintenance cycles.