MySQL：主从查询中包含 WHERE 语句会产生不同的结果？
假设我有一个主表（表1）和一个详细表（表2）。每个主记录可以有多个详细记录。现在我想要一个对每个主记录的所有详细记录进行计数的查询：
SELECT t1.id, count(t2.*)
FROM table1 as t1
LEFT JOIN table2 AS t2 ON t2.id=t1.id
GROUP BY t1.id
这给了我与 table1 完全相同的记录数。
但是，当我添加 WHERE 语句以仅计算检查字段大于 0 的记录时，我不再获得 table1 中的所有记录！没有匹配详细记录的那些现在被完全排除。为什么会发生这种情况？
SELECT t1.id, count(t2.*)
FROM table1 as t1
LEFT JOIN table2 AS t2 ON t2.id=t1.id
WHERE t2.checkfield != 0
GROUP BY t1.id
（也许我的真实查询中有其他问题，因为我试图在这个示例中简化它，但我认为我做对了）
Let's say I have a mastertable (table1) with a detailtable (table2). There can be multiple detail records for each masterrecord. Now I want a query that counts all detailrecords for each masterrecord :
SELECT t1.id, count(t2.*)
FROM table1 as t1
LEFT JOIN table2 AS t2 ON t2.id=t1.id
GROUP BY t1.id
This gives me exactly the same number of records as table1 has.
But when I add a WHERE statement to only count the records that have a checkfield that's higher than 0, I don't get all records in table1 anymore! The ones with no matching detailrecords are now left out completely. Why is this happening?
SELECT t1.id, count(t2.*)
FROM table1 as t1
LEFT JOIN table2 AS t2 ON t2.id=t1.id
WHERE t2.checkfield != 0
GROUP BY t1.id
(Maybe something else is wrong in my real query, since I tried to simplify it for this example, but I think I got it right)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
WHERE子句限制了聚合的连接结果，因此当您尝试进行外连接时，只有那些具有t2.checkfield != 0的行才能生存，但这排除了所有不匹配的行！另一方面，当您将
WHERE更改为AND时，您现在拥有tab1 LEFT OUTER JOIN tab2 ON(tab1.id = tab2.t1_id AND some_condition)< /code> ——但这仍然是一个外连接，即左侧的记录与右侧没有匹配的记录将被包括在内。The
WHEREclause restricts the joined results which are being aggregated over, so while you're trying do an outer join, only those rows witht2.checkfield != 0survive, but that excludes all the unmatched rows!On the other hand, when you change
WHEREtoAND, you now havetab1 LEFT OUTER JOIN tab2 ON(tab1.id = tab2.t1_id AND some_condition)-- but this is still an outer join, i.e. records on the left which have no match on the right will be included.