CODEWITHSUNDEEP
sqlperformance
Peter Bratton
Peter Bratton

Reputation: 6408

Which of these approaches has better performance for large tables?

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?

Upvotes: 1

Views: 172

Answers (4)

Quassnoi
Quassnoi

Reputation: 425341

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:

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.

Upvotes: 5

Ryan Guill
Ryan Guill

Reputation: 13896

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.

Upvotes: 1

tpdi
tpdi

Reputation: 35141

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

Upvotes: 0

Eric
Eric

Reputation: 95123

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

Upvotes: 0

Related Questions