MySQL查询. Join和SubQuery有什么区别? [英] Mysql query. What is the difference between Join and a 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.

但是看来这不是怎么回事?表格成倍增加而不是变小.而且显然我在上面描述的更像是子查询? (请注意，我说的很像.我不知道子查询的工作原理)

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

这篇关于MySQL查询. Join和SubQuery有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！