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

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 2

Use the first one, as it is:

• More explicit
• Is the Standard way

As for performance - there should be no difference.

Answer 3

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 4

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.

Answer 5

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 6

Filtering joins solely using WHERE can be extremely inefficient in some common scenarios. For example:

SELECT * FROM people p, companies c WHERE p.companyID = c.id AND p.firstName = 'Daniel'

Most databases will execute this query quite literally, first taking the Cartesian product of the people and companies tables and then filtering by those which have matching companyID and id fields. While the fully-unconstrained product does not exist anywhere but in memory and then only for a moment, its calculation does take some time.

A better approach is to group the constraints with the JOINs where relevant. This is not only subjectively easier to read but also far more efficient. Thusly:

SELECT * FROM people p JOIN companies c ON p.companyID = c.id
    WHERE p.firstName = 'Daniel'

It's a little longer, but the database is able to look at the ON clause and use it to compute the fully-constrained JOIN directly, rather than starting with everything and then limiting down. This is faster to compute (especially with large data sets and/or many-table joins) and requires less memory.

I change every query I see which uses the "comma JOIN" syntax. In my opinion, the only purpose for its existence is conciseness. Considering the performance impact, I don't think this is a compelling reason.