LeetCode MySQL - COALESCE, IFNULL 함수

LeetCode SQL 50 - 584. Find Customer Feferee

https://leetcode.com/problems/find-customer-referee/description/?envType=study-plan-v2&envId=top-sql-50
 

Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

 
Solution 1 :

# Write your MySQL query statement below
SELECT name 
FROM   Customer
WHERE  referee_id <> '2' OR referee_id IS NULL

 
Solution 2 :  

# Write your MySQL query statement below
SELECT name 
FROM   Customer
WHERE  COALESCE(referee_id, '') <> '2'

 
Solution 3 : 

# Write your MySQL query statement below
select name 
from Customer
where IFNULL(referee_id, '') <> '2'

 

COALESCE 함수 사용하기

SELECT COALESCE(a_section, b_section, c_section) 
FROM 테이블명

 
- a_section이 NULL인 경우 b_section을 반환
- b_section도 NULL 인 경우 c_section을 반환 
- 인자가 3개
 

IFNULL 함수 사용하기

SELECT IFNULL(a_section, b_section) 
FROM 테이블명

- a_section이 NULL인 경우 b_section을 반환
- 인자가 2개