[MySQL] JOIN 이후 NULL 처리

https://leetcode.com/problems/combine-two-tables/

Combine Two Tables - LeetCode

 

 

 

데이터 생성

CREATE table If Not Exists Person (personId int, firstName varchar(255), lastName varchar(255));
Create table If Not Exists Address (addressId int, personId int, city varchar(255), state varchar(255));
insert into Person (personId, lastName, firstName) values ('1', 'Wang', 'Allen');
insert into Person (personId, lastName, firstName) values ('2', 'Alice', 'Bob');
insert into Address (addressId, personId, city, state) values ('1', '2', 'New York City', 'New York');
insert into Address (addressId, personId, city, state) values ('2', '3', 'Leetcode', 'California');

 

LEFT OUTER JOIN 쿼리

SELECT firstName, lastName, city, state
FROM Person p LEFT JOIN Address a 
ON p.personId = a.personId
;

 

Address 테이블에 PersonId가 없어도 불러오게 left outer join

디폴트가 null

이렇게 문제는 풀었는데, 만약 디폴트를 Null 말고 다른 것으로 설정 하고 싶으면?

 

IFNULL을 사용

SELECT firstName, lastName, IFNULL(state, 'No state') AS state, city
FROM Person p LEFT JOIN Address a 
ON p.personId = a.personId
;

No state로 들어가 있다