在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相同，我在代码中找到了所有这些查询，并问自己是否应该重写它们.他们身上有些臭吗?还是很好?

---

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

我的结论:我不会更改以前的查询，但会更正我的写作风格并在以后使用关键字.

感谢您的回答！

解决方案

在某些常见情况下，仅使用WHERE过滤联接可能会非常低效.例如:

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

大多数数据库将完全按字面意义执行此查询，首先使用笛卡尔积 >和companies表以及 then 过滤器，这些表具有匹配的companyID和id字段.尽管完全不受约束的产品并不存在，而是存在于内存中，然后只存在片刻，但它的计算确实需要一些时间.

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

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

更长一点，但是数据库能够查看ON子句，并使用它直接计算完全受约束的JOIN，而不是从一切开始，然后进行限制向下.计算速度更快(尤其是在具有大数据集和/或多表联接的情况下)，并且所需的内存更少.

我更改了我看到的每个使用逗号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!

解决方案

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