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

问题描述

当我开始编写数据库查询时，我还不知道 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

现在我知道这与 INNER JOIN 相同，我在我的代码中找到所有这些查询，并问自己是否应该重写它们.它们是不是有什么异味，或者它们很好?

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?

我的回答总结:此查询没有任何问题，但使用关键字很可能会使代码更具可读性/可维护性.

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.

推荐答案

仅使用 WHERE 过滤联接在某些常见情况下效率极低.例如:

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'

大多数数据库都会按字面意思执行这个查询，首先采用笛卡尔积>people 和 companys 表和 then 过滤那些具有匹配的 companyID 和 id 字段.虽然完全无约束的乘积不存在于任何地方，只存在于内存中，并且只存在片刻，但它的计算确实需要一些时间.

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.

更好的方法是将约束与相关的 JOIN 分组.这不仅在主观上更容易阅读，而且效率也更高.因此:

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'

有点长，但是数据库能够查看ON子句并使用它直接计算完全约束的JOIN，而不是从<开始em>一切，然后限制下来.这计算速度更快(特别是对于大型数据集和/或多表连接)并且需要更少的内存.

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.

我更改了我看到的每个使用逗号 JOIN"语法的查询.在我看来，它存在的唯一目的就是简洁.考虑到性能影响，我认为这不是一个令人信服的理由.

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 关键字的连接有什么问题吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！