SQL中JOIN与子查询的优劣

Question

我是个MySQL新手，在涉及多表查询时，喜欢使用子查询，简单易懂。

SELECT * FROM *** WHERE id IN (SELECT ***);

但也发现很多人用JOIN同样能实现查询结果，我想知道JOIN相比子查询，在性能方面有什么优势吗？
我该如何理解JOIN语句呢？

Answer 1

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!