Comparison between select in and left join efficiency

The two table structures were

CREATE TABLE ' t1 ' (
  ' id ' int (one) not null auto_increment,
  ' rid ' int (one) not NULL,
  ' num ' int (one) not NULL,
  PRIMARY KEY (' id ')
) Engine=innodb auto_increment=24236 DEFAULT Charset=utf8;

CREATE TABLE ' T2 ' (
  ' id ' int (one) not null auto_increment,
  ' sid ' Int (one) not NULL,
  ' num ' int (one) not null,
  
   primary KEY (' id ')
) Engine=innodb auto_increment=24287 DEFAULT Charset=utf8;
  

Two table data is 10,000, query 10 times, shortest and longest time

Select T1.id,sum (t1.num) from T1 left JOIN T2 on T2.id=t1.id where t2.num=2 GROUP by t1.id;
Time: 0.007s 0.009s

Select T1.id,sum (T1.num) from (select ID to T2 where num =2) as T3 left JOIN T1 on t3.id=t1.id GROUP by T1.id;
Time: 0.007s 0.009s

Select T1.id,sum (T1.num) from (SELECT * to T2 where num =2) as T3 left JOIN T1 on t3.id=t1.id GROUP by T1.id;
Time: 0.005s 0.009s

Select Id,sum (num) from T1 where ID in (select ID from t2 where num =2) the group by ID;
Time: 0.013s 0.017s Two table data are 20,000, query 10 times, shortest and longest

Select T1.id,sum (t1.num) from T1 left JOIN T2 on T2.id=t1.id where t2.num=2 GROUP by t1.id;
Time: 0.014s 0.023s

Select T1.id,sum (T1.num) from (select ID to T2 where num =2) as T3 left JOIN T1 on t3.id=t1.id GROUP by T1.id;
Time: 0.012s 0.020s

Select T1.id,sum (T1.num) from (SELECT * to T2 where num =2) as T3 left JOIN T1 on t3.id=t1.id GROUP by T1.id;
Time: 0.016s 0.017s

Select Id,sum (num) from T1 where ID in (select ID from t2 where num =2) the group by ID;
Time: 0.015s 0.030s Two table data are 100,000, query 10 times, shortest and longest

Select T1.id,sum (t1.num) from T1 left JOIN T2 on T2.id=t1.id where t2.num=2 GROUP by t1.id;
Time: 0.045s 0.049s

Select T1.id,sum (T1.num) from (select ID to T2 where num =2) as T3 left JOIN T1 on t3.id=t1.id GROUP by T1.id;
Time: 0.042s 0.046s

Select T1.id,sum (T1.num) from (SELECT * to T2 where num =2) as T3 left JOIN T1 on t3.id=t1.id GROUP by T1.id;
Time: 0.044s 0.048s

Select Id,sum (num) from T1 where ID in (select ID from t2 where num =2) the group by ID;
Time: 0.045s 0.103s Summary

Without exception, the maximum time is the first query, and the following nine times are the same, and the select in is more efficient when the volume of data increases. Join query is less affected, more stable, more than 10,000 of data, preferably using join queries.

It is worth mentioning is not to query the way, join mode query efficiency is still very high, with the query efficiency of only 0.002s.