Which of these approaches has better performance for large tables?

Which of these approaches has better performance for large tables?

后知后觉 发布于 2021-11-30 字数 495 浏览 886 回复 4 原文

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?

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

一枫情书 2022-06-07 4 楼

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

可爱暴击 2022-06-07 3 楼

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

君勿笑 2022-06-07 2 楼

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.

清君侧 2022-06-07 1 楼

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.