mysql为什么可以不用join,在MySQL查询中，为什么要使用join而不是在哪里？

最新推荐文章于 2024-07-07 04:01:35 发布

老魏一凡

最新推荐文章于 2024-07-07 04:01:35 发布

阅读量142

点赞数

文章标签： mysql为什么可以不用join

It seems like to combine two or more tables, we can either use join or where. What are the advantages of one over the other?

解决方案

Any query involving more than one table requires some form of association to link the results from table "A" to table "B". The traditional (ANSI-89) means of doing this is to:

List the tables involved in a comma separated list in the FROM clause

Write the association between the tables in the WHERE clause

SELECT *

FROM TABLE_A a,

TABLE_B b

WHERE a.id = b.id

Here's the query re-written using ANSI-92 JOIN syntax:

SELECT *

FROM TABLE_A a

JOIN TABLE_B b ON b.id = a.id

From a Performance Perspective:

Where supported (Oracle 9i+, PostgreSQL 7.2+, MySQL 3.23+, SQL Server 2000+), there is no performance benefit to using either syntax over the other. The optimizer sees them as the same query. But more complex queries can benefit from using ANSI-92 syntax:

Ability to control JOIN order - the order which tables are scanned

Ability to apply filter criteria on a table prior to joining

From a Maintenance Perspective:

There are numerous reasons to use ANSI-92 JOIN syntax over ANSI-89:

More readable, as the JOIN criteria is separate from the WHERE clause

Less likely to miss JOIN criteria

Consistent syntax support for JOIN types other than INNER, making queries easy to use on other databases

WHERE clause only serves as filtration of the cartesian product of the tables joined

From a Design Perspective:

ANSI-92 JOIN syntax is pattern, not anti-pattern:

The purpose of the query is more obvious; the columns used by the application is clear

It follows the modularity rule about using strict typing whenever possible. Explicit is almost universally better.

Conclusion

Short of familiarity and/or comfort, I don't see any benefit to continuing to use ANSI-89 JOIN syntax instead of ANSI-92 syntax. Some might complain that ANSI-92 syntax is more verbose, but that's what makes it explicit. The more explicit, the easier it is to understand and maintain.