Mysql查询。连接和子查询有什么区别？
我总是认为 join 会得到某些结果，然后仅使用这些结果进行连接。
SELECT * FROM tbl AS t1
JOIN tbl2 AS t2 ON t1.id = t2.foreignId
JOIN tbl3 AS t3 ON t2.id = t3.foreignId
WHERE t1.date > SOMEDATE
根据我的理解，它将运行 where 语句并仅获取日期范围内的结果。然后它会遍历所有 t2 并且只尝试匹配连接到 t1 的 id（这可能会使结果更小）。然后，对于这个较小的潜在结果，它将对 T3 执行相同的操作并输出最终结果。
但似乎不是这样的？而且表格会成倍增加而不是变小。显然我上面描述的更像是子查询？ （注意，我说的是 LIKE。我不知道子查询是如何工作的）
JOIN 是如何工作的以及 JOIN 和子查询之间有什么区别？
我同时使用 MySql 和 SQLite。我不知道这是否相关。
I always though join gets the results of something and then joins only using those results.
SELECT * FROM tbl AS t1
JOIN tbl2 AS t2 ON t1.id = t2.foreignId
JOIN tbl3 AS t3 ON t2.id = t3.foreignId
WHERE t1.date > SOMEDATE
From my understanding it will run the where statement and get only the results that fall within the date range. Then it will go through all of t2 and only try to match ids that connect to t1 (which may make the results smaller). Then with this smaller potential results it will do the same for T3 and output the final results.
But it appears that is NOT how this works? And the tables multiply instead of getting smaller. And apparently what i am describing above is more like subqueries? (NOTE that i said LIKE. I dont know how subquerys work)
How does JOIN work and what is the difference between a JOIN and a subquery?
I use both MySql and SQLite. I dont know if thats relevant.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果子查询中使用的表返回一个值两次，则 JOIN 也会返回匹配的行两次，而 IN 或 EXISTS 条件只会返回一次。
JOIN 往往具有更好的性能，但在某些情况下可能并非如此，特别是对于每个数据库（包括版本）。
参考：
If the table used in the subquery returns a value twice, a JOIN will also return the matching rows twice, while an IN or EXISTS condition will return them only once.
JOINs tend to have better performance, but there are situations where that might not be the case, particular to each database (including version).
Reference:
子查询示例
数据库引擎的执行方式实际上取决于其优化器。尝试在查询前面添加 EXPLAIN 来查看内容数据库引擎正在做。需要考虑许多变量，包括索引、表大小等。
sub query example
How the db engine performs is really up to its optimizer. Try putting an EXPLAIN in front of the query to see what the db engine is doing. Many variables come into consideration including index, table size, etc.