mysql查询.Join 和 SubQuery 有什么区别?
我总是虽然 join 得到某些结果，然后只使用这些结果进行连接.
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
据我了解，它将运行 where 语句并仅获取日期范围内的结果.然后它将遍历所有 t2 并且仅尝试匹配连接到 t1 的 id(这可能会使结果更小).然后使用这个较小的潜在结果，它将对 T3 执行相同的操作并输出最终结果.
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.
但它似乎不是这样工作的?并且表格成倍增加而不是变小.显然我上面描述的更像是子查询?(注意我说的是 LIKE.我不知道子查询是如何工作的)
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)
JOIN 是如何工作的，JOIN 和子查询有什么区别?
How does JOIN work and what is the difference between a JOIN and a subquery?
我同时使用 MySql 和 SQLite.我不知道那是否相关.
I use both MySql and SQLite. I dont know if thats relevant.
推荐答案
如果子查询中使用的表返回值两次，JOIN 也会返回匹配的行两次，而 IN 或 EXISTS 条件只会返回一次.
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.
JOIN 往往具有更好的性能，但在某些情况下可能并非如此，尤其是每个数据库(包括版本).
JOINs tend to have better performance, but there are situations where that might not be the case, particular to each database (including version).
参考:
- IN vs. JOIN vs. EXISTS
相关文章