子查询优化成join关联查询时要注意一对多关系

mysql> select * from t where t.id in (select t1.tid from t1);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select t.id from t join t1 on t.id=t1.tid;
+------+
| id   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> select distinct t.id from t join t1 on t.id=t1.tid;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

这个例子中，t表中只有一行数据1，t1表中有两行1

子查询可以查到一行数据，而不加distinct的join查询出来两条数据，为了避免这种情况，就需要注意使用distinct去重

转载于:https://www.cnblogs.com/walter371/p/4170953.html