第七章 6-ifnull and coalesce函数|the ifnull and coalesce functions--Mosh my SQL

MySQL中COALESCE()和IFNULL()的区别-优快云博客

1，查找没有shipper_id的记录，并把标题改为shipper

use sql_store;
select
    order_id,
    ifnull(shipper_id,'not assigned') as shipper
from orders

2，用coalesce函数，如果shipper_id是空的，返回comments那列的值，如果comments也是空的，就显示not assigned

use sql_store;
select
    order_id,
    coalesce(shipper_id,comments,'not assigned') as shipper
from orders

对比原表，没有问题

3，在2的基础上，加上一列    ifnull(shipper_id,'...')，shipper_id null 的时候显示...

use sql_store;
select
    order_id,
    ifnull(shipper_id,'...'),
    coalesce(shipper_id,comments,'not assigned') as shipper
from orders

4，练习：写一段查询

答案：这里用ifnull也是可以的

use sql_store;
select
    concat(first_name, ' ',last_name)as customer,
    coalesce(phone,'unknown') as phone
from customers