Which of these approaches has better performance for large tables?

Question

Let A and B be two tables in a database schema. A and B are related by a many-to-one relationship. There exists many B's for each A, and B has a foreign key column a_id. Both tables have a primary key column id.

Which of the following two queries performs better for large data sets in A and B?

SELECT A.* FROM A,B WHERE A.id = B.a_id

or

SELECT A.* FROM A INNER JOIN B ON A.id = B.a_id

Or are they equivalent?

Answer 1

From my understanding, they are both the same. In theory, I suppose the INNER JOIN could be optimized by the engine, as the default behavior for A,B would be a Cartesian join. That being said, I'm dealing with a decent size table now, and both gave the same query times in SQL Server 2005, so my guess is that the engine is smart enough to pick this up.

Cheers,
Eric

Answer 2

They are equivalent for all 4 major database systems: Oracle, SQL Server, MySQL, PostgreSQL.

Using JOIN syntax (to be more exact, using STRAIGHT_JOIN instead of JOIN) will help to enforce the join order you need in MySQL.

See this answer for details:

• Does the order of tables matter?

It's also generally considered more clean and readable to use the JOIN syntax.

Though I'm grown on Oracle code samples which generally use the WHERE syntax.

Answer 3

They're equivalent. But the second form is the preferred syntax.

Answer 4

I would agree with the other answers that performance wise the two statements are equivalent on the major systems but I would also throw out there that the second statement is preferable from a code readability standpoint. I would also say that specifying the column list instead of using an asterisk would increase performance as well as readability.