标题: 使用左连接查询值的遗漏 [打印本页] 作者: leoxqing 时间: 2009-08-04 22:55 标题: 使用左连接查询值的遗漏 大家帮我看看一条左连接语句，为什么会漏掉值呢？
select
a.uid,a.expiretime,a.servicetype,b.pcphonecity
from
T_W_ValueAddedService a
left join
DB.T_BasicInfo2 b
on a.uid=b.uid
and
b.pccity in ('0755','0769','0750','0760','020')
where
a.expiretime>DATE_ADD(NOW(),INTERVAL 100 DAY)
and servicetype='phone_paymonthly'
and b.pccity is not null;
查询结果：
+----------+---------------------+------------------+-------------+
| uid | expiretime | servicetype | pcphonecity |
+----------+---------------------+------------------+-------------+
| 12580 | 2009-11-30 21:15:10 | phone_paymonthly | 0769 |
| 358851 | 2011-06-17 00:00:00 | phone_paymonthly | 0755 |
| 128827 | 2009-11-17 18:15:03 | phone_paymonthly | 020 |
| 61762040 | 2009-11-19 17:28:59 | phone_paymonthly | 020 |
+----------+---------------------+------------------+-------------+
直接查询T_W_ValueAddedService表
mysql> select * from T_W_ValueAddedService where uid=270280 and servicetype='phone_paymonthly';
+--------+------------------+---------------------+
| uid | servicetype | expiretime |
+--------+------------------+---------------------+
| 270280 | phone_paymonthly | 2011-06-01 00:00:00 |
+--------+------------------+---------------------+
事实上我知道还有一部份这样的数据通过左连接没查到，请各位帮我分析下数据遗漏的原因？作者: denniswwh 时间: 2009-08-05 09:33
原因应该出在and b.pccity is not null这个条件上，连接后的结果里，uid=270280 这条记录的pccity 刚好是null，不信你去掉and b.pccity is not nul这个条件看一下。作者: sunnyfun 时间: 2009-08-05 16:44
既然 left join 怎么还 pccity is not null？
