我是个MySQL新手，在涉及多表查询时，喜欢使用子查询，简单易懂。
SELECT * FROM *** WHERE id IN (SELECT ***);
但也发现很多人用JOIN同样能实现查询结果，我想知道JOIN相比子查询，在性能方面有什么优势吗？
我该如何理解JOIN语句呢？
PHP中文网2017-04-17 11:08:02
For mysql, subqueries and joins are never recommended. Because the efficiency of join itself is flawed, it is difficult to guarantee the efficiency once the amount of data increases. It is strongly recommended to fetch data from a single table based on the index, and then join and merge the data in the program.
怪我咯2017-04-17 11:08:02
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!
ringa_lee2017-04-17 11:08:02
I have not carefully understood the difference, but for situations where the data is relatively small (little pressure), both are available, and the performance is estimated to be not much different; for situations where the amount of data is large, both JOIN and subquery It is unacceptable and requires additional optimization (such as using NoSQL for caching).
黄舟2017-04-17 11:08:02
I think it doesn’t matter if the amount of data is small, but this method won’t be used if the amount of data is large.
巴扎黑2017-04-17 11:08:02
It doesn’t matter if the amount of data is small, but try to avoid it if the amount of data is large.
PHPz2017-04-17 11:08:02
http://chaous.com/MySQL/2012/03/27/my...
I wrote an article before about the MySQL IN operation, I hope it will be useful to LZ.
As for how to understand JOIN query, I recommend a book by LZ http://book.douban.com/subject/105696...
大家讲道理2017-04-17 11:08:02
I don’t know the specific performance. But I read that others said that join is used instead of subquery.
PHP中文网2017-04-17 11:08:02
Don’t use subqueries, the efficiency is too poor. If it is JOIN, it will be a nested query. Small tables drive large tables and are related through index fields. If the table records are relatively small, it is still OK. If it is large, the processing can be controlled in the business logic.
