在SQL或MySQL中不使用JOIN关键字的连接有问题吗？(Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL?)

当我开始编写数据库查询时，我不知道JOIN关键字，而且我自然地只是扩展了我已经知道的并且写了这样的查询：

SELECT a.someRow, b.someRow FROM tableA AS a, tableB AS b WHERE a.ID=b.ID AND b.ID= $someVar

现在我知道这与INNER JOIN是一样的，我在我的代码中找到所有这些查询，并问自己是否应该重写它们。 他们有什么臭味吗？

编辑：

我的回答总结 ：这个查询没有错，但是使用关键字最有可能使代码更易读/可维护。

我的结论 ：我不会改变我的旧查询，但我将会改正我的写作风格，并在将来使用关键字。

谢谢你的答案！

When I started writing database queries I didn't know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this:

SELECT a.someRow, b.someRow FROM tableA AS a, tableB AS b WHERE a.ID=b.ID AND b.ID= $someVar

Now that I know that this is the same as an INNER JOIN I find all these queries in my code and ask myself if I should rewrite them. Is there something smelly about them or are they just fine?

EDIT:

My answer summary: There is nothing wrong with this query BUT using the keywords will most probably make the code more readable/maintainable.

My conclusion: I will not change my old queries but I will correct my writing style and use the keywords in the future.

THANKS for your answers!

最满意答案

在某些常见情况下，仅使用WHERE进行过滤可能会非常低效。 例如：

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

大多数数据库将完全执行此查询，首先将people和companies表的笛卡尔乘积 ， 然后过滤具有匹配的companyID和id字段的那些。 虽然完全不受约束的产品不存在于内存中，但仅存在一段时间，但其计算确实需要一些时间。

一个更好的方法是将约束与JOIN相关联。 这不仅在主观上更容易阅读，而且效率也更高。 正是如此：

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

它还有一段时间，但是数据库可以查看ON子句，并使用它来直接计算完全受约束的JOIN ，而不是从一切开始，然后限制。 这是更快的计算（特别是对于大数据集和/或多表连接），并且需要更少的内存。

我更改每个使用“逗号JOIN ”语法查询的查询。 在我看来，其存在的唯一目的是简洁。 考虑到业绩影响，我不认为这是一个引人注目的理由。

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.

在SQL或MySQL中不使用JOIN关键字的连接有问题吗？(Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL?)

当我开始编写数据库查询时，我不知道JOIN关键字，而且我自然地只是扩展了我已经知道的并且写了这样的查询：

SELECT a.someRow, b.someRow FROM tableA AS a, tableB AS b WHERE a.ID=b.ID AND b.ID= $someVar

现在我知道这与INNER JOIN是一样的，我在我的代码中找到所有这些查询，并问自己是否应该重写它们。 他们有什么臭味吗？

编辑：

我的回答总结 ：这个查询没有错，但是使用关键字最有可能使代码更易读/可维护。

我的结论 ：我不会改变我的旧查询，但我将会改正我的写作风格，并在将来使用关键字。

谢谢你的答案！

When I started writing database queries I didn't know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this:

SELECT a.someRow, b.someRow FROM tableA AS a, tableB AS b WHERE a.ID=b.ID AND b.ID= $someVar

Now that I know that this is the same as an INNER JOIN I find all these queries in my code and ask myself if I should rewrite them. Is there something smelly about them or are they just fine?

EDIT:

My answer summary: There is nothing wrong with this query BUT using the keywords will most probably make the code more readable/maintainable.

My conclusion: I will not change my old queries but I will correct my writing style and use the keywords in the future.

THANKS for your answers!

最满意答案

在某些常见情况下，仅使用WHERE进行过滤可能会非常低效。 例如：

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

大多数数据库将完全执行此查询，首先将people和companies表的笛卡尔乘积 ， 然后过滤具有匹配的companyID和id字段的那些。 虽然完全不受约束的产品不存在于内存中，但仅存在一段时间，但其计算确实需要一些时间。

一个更好的方法是将约束与JOIN相关联。 这不仅在主观上更容易阅读，而且效率也更高。 正是如此：

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

它还有一段时间，但是数据库可以查看ON子句，并使用它来直接计算完全受约束的JOIN ，而不是从一切开始，然后限制。 这是更快的计算（特别是对于大数据集和/或多表连接），并且需要更少的内存。

我更改每个使用“逗号JOIN ”语法查询的查询。 在我看来，其存在的唯一目的是简洁。 考虑到业绩影响，我不认为这是一个引人注目的理由。

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.