MySQL“开启”条件 - 它有什么作用?
首先让我解释一下,我已经搜索了至少一个小时来查找有关 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 技术交流群。
发布评论
评论(3)
仅供参考,两者都是相同的,除了 INNER JOIN ... ON
是 ANSI 92 语法
您可能需要 google ansi 92 语法与 ansi 86/89 语法
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
INNER JOIN
确实可以在没有 ON 的情况下编写,只需将所有 ON 子句移动到 where 子句即可。使用健全的优化器,它不会在性能方面产生差异(但它仍然会使查询更清晰!)。由于 null 处理,不能使用外连接(例如,
LEFT JOIN
)。如果b中不存在a.fk,则返回b.bar=NULL。
如果 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.will return b.bar=NULL if a.fk does not exist in b.
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