MySQL 嵌入式 SELECT 与 JOIN
之间有明显的区别吗？
SELECT userid, username, userdept,
(SELECT deptname FROM depts WHERE deptid=userdept) AS deptname
FROM users
和
SELECT userid, username FROM users
INNER JOIN depts ON depts.deptid=users.userdept
哪个更好
Is there a noticeable difference between:
SELECT userid, username, userdept,
(SELECT deptname FROM depts WHERE deptid=userdept) AS deptname
FROM users
and
SELECT userid, username FROM users
INNER JOIN depts ON depts.deptid=users.userdept
Which one is better?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
加入更好，
请参阅此链接：
http:// /www.selikoff.net/2008/12/10/memo-avoid-nested-queries-in-mysql-at-all-costs/
join is better ,
see this link :
http://www.selikoff.net/2008/12/10/memo-avoid-nested-queries-in-mysql-at-all-costs/
您还可以看到关于此主题的 SO 中的许多讨论。
you can see the many discussion in SO on this topic as well.
这两个查询不是同义词。
如果您将
INNER JOIN替换为LEFT JOIN，它们将是同义词，但如果deptid则子查询可能会失败。不是唯一的，而 LEFT JOIN 总是会成功。如果
depts.deptid上有一个UNIQUE索引（这很可能是，因为该字段很可能是PRIMARY KEY），那么性能差异可以忽略不计。These two queries are not synonyms.
They would be the synonyms if you replaced the
INNER JOINwith theLEFT JOIN, with the exception that the subquery is a subject to failure ifdeptidis not unique, while aLEFT JOINwill always succeed.If there is a
UNIQUEindex ondepts.deptid(which most probably is, since this field is most probably aPRIMARY KEY), then the performance difference would be negligible.您的第二个查询具有更好的性能。
您可以看到这个示例： http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect
Your second query has better performance.
You can see this example: http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect