我是个MySQL新手,在涉及多表查询时,喜欢使用子查询,简单易懂。
SELECT * FROM *** WHERE id IN (SELECT ***);
但也发现很多人用JOIN
同样能实现查询结果,我想知道JOIN
相比子查询,在性能方面有什么优势吗?
我该如何理解JOIN语句呢?
as described on stackoverflow.http://stackoverflow.com/questions/38...
7 down vote accepted
I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by "OR" (WHERE x=Y OR x=Z OR...).
As with ALL THINGS SQL though, your mileage may vary. The speed will depend a lot on indexes (do you have indexes on both ID columns? That will help a lot...) among other things.
The only REAL way to tell with 100% certainty which is faster is to turn on performance tracking (IO Statistics is especially useful) and run them both. Make sure to clear your cache between runs!