MySQL“开启”条件 - 它有什么作用?

发布于 10-12 23:48 字数 877 浏览 3 评论 0原文

首先让我解释一下,我已经搜索了至少一个小时来查找有关 MySQL 中这个经常使用的关键字的任何内容。问题是,我用来查找相关信息的任何搜索引擎都会在最琐碎且不相关的结果中匹配单词“ON”。我也没能浏览 MySQL 的文档。

我看到 on 与 INNER JOIN 一起使用作为条件,但我不知道它的作用。一个用法的例子是

SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

我知道“WHERE”和“HAVING”之间的区别分别是过滤行和过滤组之间的区别。

然而我无法想象“ON”过滤了什么。

我认为使用 INNER JOIN 时可能需要进行过滤,但我之前在 INNER JOIN 情况下使用过 WHERE,如下所示:

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name, 
    COUNT(i.id) as image_count

FROM gallery_groups g INNER JOIN
    users c INNER JOIN
    users u INNER JOIN
    gallery_images i

WHERE g.created_by=c.id AND g.updated_by=u.id AND i.group=g.id
GROUP BY g.name
ORDER BY g.date_updated DESC, g.name

任何信息和/或示例将不胜感激!

First of all let me explain that I have searched for at least an hour for anything regarding this MUCH used keyword in MySQL. The problem is that any search engine I use to find information on it matches the word "ON" in the most trivial and unrelated results. I also have had no luck in browsing MySQL's documentation.

I see on used with INNER JOIN as a condition, but I have no idea what it does. An example of usage is

SELECT t1.name, t2.salary
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

I know the differences between "WHERE" and "HAVING" is the difference between filtering rows and filtering groups respectively.

I can't imagine what "ON" filters however.

I figured it might be required for filtering when INNER JOIN was used, but I have used WHERE in INNER JOIN cases before, like here:

SELECT g.id, g.name, g.date_created, g.date_updated, g.created_by, 
    c.fullname AS creator_name, g.updated_by, u.fullname AS updater_name, 
    COUNT(i.id) as image_count

FROM gallery_groups g INNER JOIN
    users c INNER JOIN
    users u INNER JOIN
    gallery_images i

WHERE g.created_by=c.id AND g.updated_by=u.id AND i.group=g.id
GROUP BY g.name
ORDER BY g.date_updated DESC, g.name

Any information and/or examples would be appreciated!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

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

评论(3

杀手六號2024-10-19 23:48:57

INNER JOIN 确实可以在没有 ON 的情况下编写,只需将所有 ON 子句移动到 where 子句即可。使用健全的优化器,它不会在性能方面产生差异(但它仍然会使查询更清晰!)。

由于 null 处理,不能使用外连接(例如,LEFT JOIN)。

SELECT a.foo, b.bar FROM a LEFT JOIN b ON (a.fk = b.pk)

如果b中不存在a.fk,则返回b.bar=NULL。

SELECT a.foo, b.bar FROM a, b WHERE a.fk.b.pk

如果 b 中不存在 a.fk,则不会返回行。

仅供参考:连接语法为 http://dev.mysql.com/doc/ refman/5.0/en/join.html

INNER JOIN can indeed be written without ON just by moving all the ON clauses to the where clause. With a sane optimizer, it won't make a difference performance-wise (but it'll still make the query clearer!).

Outer joins (e.g., LEFT JOIN) can not be, because of null handling.

SELECT a.foo, b.bar FROM a LEFT JOIN b ON (a.fk = b.pk)

will return b.bar=NULL if a.fk does not exist in b.

SELECT a.foo, b.bar FROM a, b WHERE a.fk.b.pk

will not return a row if a.fk does not exist in b.

FYI: Join syntax is http://dev.mysql.com/doc/refman/5.0/en/join.html

眼眸印温柔2024-10-19 23:48:57

您可以将 JOIN 闭包中的表之间的依赖关系放在 ON 关键字之后,而不是编写长的 WHERE 语句。

它加快了数据库中的搜索过程(它涉及 kartezjan 矩阵),使您的代码更干净,并且更容易放置真正的 WHERE 条件

Instead writing long WHERE statement you put dependency between tables in JOIN closure after ON key word

It speed up searching process in database ( it realted to kartezjan matrix ), make your code cleaner, and easier to put real WHERE conditions

把昨日还给我2024-10-19 23:48:57

仅供参考,两者都是相同的,除了 INNER JOIN ... ON 是 ANSI 92 语法

您可能需要 google ansi 92 语法与 ansi 86/89 语法

FYI, both are identical except INNER JOIN ... ON is ANSI 92 syntax

You probably need to google ansi 92 syntax vs ansi 86/89 syntax

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文