在使用left join等联表查询时，常遇到某些字段为null，一般都在程序处理使用if a==null判断做处理，其实MySQL本身也有一个IFNULL函数可以处理。
name:
mysql> select * from name;
+------+-----------+
| id | user |
+------+-----------+
| 1 | xiaoqiang |
| 2 | lisi |
| 3 | xiaowu |
| 4 | laoliu |
+------+-----------+
4 rows in set (0.00 sec)
phone:
mysql> select * from phone;
+------+-------+
| id | phone |
+------+-------+
| 2 | 110 |
| 3 | 119 |
+------+-------+
2 rows in set (0.00 sec)
联表查询结果
mysql> select a.*,b.phone from name a left join phone b on a.id=b.id;
+------+-----------+-------+
| id | user | phone |
+------+-----------+-------+
| 1 | xiaoqiang | NULL |
| 2 | lisi | 110 |
| 3 | xiaowu | 119 |
| 4 | laoliu | NULL |
+------+-----------+-------+
4 rows in set (0.01 sec)
其中id=1、id=4两行的phone值为null，我们可以使用IFNULL函数判断它为null时给一个默认值:
mysql> select a.*,ifnull(b.phone,0) from name a left join phone b on a.id=b.id;
+------+-----------+-------------------+
| id | user | ifnull(b.phone,0) |
+------+-----------+-------------------+
| 1 | xiaoqiang | 0 |
| 2 | lisi | 110 |
| 3 | xiaowu | 119 |
| 4 | laoliu | 0 |
+------+-----------+-------------------+
4 rows in set (0.00 sec)
设置也可以指定某一个字段来填补
mysql> select a.*,ifnull(b.phone,a.id) from name a left join phone b on a.id=b.id;
+------+-----------+----------------------+
| id | user | ifnull(b.phone,a.id) |
+------+-----------+----------------------+
| 1 | xiaoqiang | 1 |
| 2 | lisi | 110 |
| 3 | xiaowu | 119 |
| 4 | laoliu | 4 |
+------+-----------+----------------------+
4 rows in set (0.00 sec)
当b.phone的值为null时，使用name表中的id字段来填补。但使用ifnull后列名看起来很不友好，可以用as还原
mysql> select a.*,ifnull(b.phone,a.id) as phone from name a left join phone b on a.id=b.id;
+------+-----------+-------+
| id | user | phone |
+------+-----------+-------+
| 1 | xiaoqiang | 1 |
| 2 | lisi | 110 |
| 3 | xiaowu | 119 |
| 4 | laoliu | 4 |
+------+-----------+-------+
4 rows in set (0.00 sec)
