in嵌套循环会自动过滤掉重复的ID

以下查询结果会不同。是因为in嵌套循环会自动过滤掉重复的ID

mysql> select count(*) from goods
    -> where goodsid
    -> in(SELECT goodsid  FROM goodsatt WHERE cid=200);
+----------+
| count(*) |
+----------+
|   169039 |
+----------+
1 row in set (7.00 sec)

mysql> select count(*) from goods a,goodsatt b
    -> where (a.goodsid=b.goodsid and b.cid=200);
+----------+
| count(*) |
+----------+
|   172083 |
+----------+
1 row in set (2.63 sec)

in嵌套循环会自动过滤掉重复的ID，举例：

以下查询会得到4条符合条件的记录，由于menu_id有重复值。

mysql> SELECT *
    -> FROM lm_menu_att
    -> WHERE att_id =200
    -> LIMIT 0 , 30
    -> ;
+---------+--------+
| menu_id | att_id |
+---------+--------+
|     363 |    200 |
|     359 |    200 |
|     364 |    200 |
|     364 |    200 |
+---------+--------+
4 rows in set (0.02 sec)

所以要是用这2种不同语句统计的话数据是会不同的。

mysql> select count(*) from lm_menu
    -> where menu_id
    -> in(SELECT menu_id  FROM lm_menu_att WHERE att_id=200
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from lm_menu a,lm_menu_att b
    -> where (a.menu_id=b.menu_id and b.att_id=200);
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)