MySQL - ifnull( )

ifnull

 

 

null이라고 되어있는 값을 다른 값으로 변경하는 함수.

 

pandas의 fillna( )와 같음

 

 

select *, ifnull( age, 100 )
from people;

 

 

 

select rv.first_name, rv.last_name, count(r.rating) as COUNT,
		ifnull(min(r.rating) , 0) as MIN,
		ifnull(max(r.rating) , 0) as MAX, 
        ifnull(avg(r.rating) , 0) as AVG, 
        if( count(r.rating) = 0, 'INACTIVE', 'ACTIVE' ) as STATUS
from reviewers rv
left join reviews r
	on rv.id = r.reviewer_id
group by rv.id ;

 

 

ifnull을 여러번 작성해 null을 변경 할수도 있다.

select rv.first_name, rv.last_name, count(r.rating) as COUNT,
	ifnull(min(r.rating) , 0) as MIN,
	ifnull(max(r.rating) , 0) as MAX, 
	ifnull(avg(r.rating) , 0) as AVG, 
	if( count(r.rating) = 0, 'INACTIVE', 'ACTIVE' ) as STATUS