[오라클 MySQL] COALESCE 두 개 컬럼 중 존재하는 값으로 합치고 싶을 때, 컬럼 병합하는 함수, 여러 열 NULL아닌 컬럼 찾기

[DBMS 데이터베이스 SQL 완전정복 목차]

안녕하세요 양햄찌 블로그 주인장입니다.

오늘은 SQL 함수 중 COALESCE 에 대해 작성하려고 해요.

COALESCE 자체가 원래 기본적으로 합치다 라는 뜻이 있죠?? 넹 컬럼을 합친다고 생각하면 돼요.

COALESCE 언제 사용할까

예를 들어 A테이블에 phone이라는 컬럼과 B테이블에 tel이라는 테이블이 있는데, 사실 컬럼명은 다르지만 의미하는건 같다고 생각해봅시다. 둘다 전화번호를 뜻해요

 

그런데 어떤 경우에는 phone이라는 컬럼에 전화번호 데이터가 insert되고 

또 다른 경우에는 tel이라는 컬럼에 전화번호 데이터가 insert된다고 해봐요.

phone을 확인했다가, 없으면 tel을 확인하고,,ㅊ 왔다갔다 검색하는게 귀찮기도 하고, 한 번에 정보를 확인할 수 없으니 불편하죠!

 

COALESCE 함수는 정확히 첫 번째 인자부터 차례대로 확인하는데, 처음으로 NULL이 아닌 값을 만나면 그 값을 리턴시키는 함수입니다. 위와 같은 상황에서 그럴 때에 'COALESCE(phone, tel)'하면 

phone 필드에 값이 있을 경우 phone 데이터를 리턴하고 phone의 값이 NULL이면 tel 데이터를 리턴하니 합치는(?) 효과가 있는 것이죠~!

 

COALESCE 문법

COALESCE 함수는 처음으로 NULL이 아닌 컬럼 값을 만나면 그 컬럼 값을 리턴합니다.

 

컬럼1이 NULL이 아니면 컬럼1을 리턴.

컬럼1이 NULL이고 컬럼2가 NULL이 아니면 컬럼2를 리턴..

컬럼1부터 컬럼 n-1까지 데이터가 NULL이면 컬럼N값을 리턴!

 

어라 NVL도 첫 번째 인자가 NULL이면 두 번째 인자 리턴하는 함수가 아닌가? 하실텐데 NVL은 인자를 두 개만 받을 수 있는 함수지만 COALESCE는 사진에 보다시피 여러개의 인자를 받을 수 있습니다.

즉 어찌보면 NVL함수를 일반화한 함수가 COALESCE인거죠.

 

COALESCE 내부 구현

사실 COALESCE 함수는 내부적으로 CASE WHEN을 사용해 구현된것과 같다고 해요.

CASE
	WHEN A is NOT NULL Then A
	WHEN B is NOT NULL Then B
	ELSE C
END

COALESCE(A, B, C)는 A가 NULL이 아니면 A를 리턴하고 B가 NULL이 아니면 B를 리턴하고 둘 다 아닐 경우에는 C를 리턴하는 거니까요~!

 

COALESCE 사용법

[더미테이블에서 간단한 문법체크]

간단하게 더미테이블(DUAL)로 체크해봅시다. 

SELECT COALESCE('A', 'B', 'C') as COALESCE_TEST FROM DUAL UNION ALL
SELECT COALESCE(NULL, 'B', 'C') FROM DUAL UNION ALL
SELECT COALESCE(NULL, NULL, 'C') FROM DUAL UNION ALL
SELECT COALESCE(NULL, NULL, NULL) FROM DUAL

결과가 어떻게 될지 예측해보시겠어요? 

첫 번째 'A', 'B', 'C'를 인자로 줬을 때에는 'A'부터 NULL이 아니니까 A를 리턴합니다

그 다음에는 NULL, 'B', 'C'와 같은 경우 NULL을 지나쳐 B가 들어갔음을 알 수 있어요

마지막 모두 NULL일 경우에는 COALESCE_TEST컬럼 값에 또한 NULL이 들어갑니다.

 

[총연봉계산 - 수식활용]

처음 시작할 때 포스팅에서 모두 SAMPLE 데이터용으로 EMPLOYEES 깔았었죠? 

employees.xlsx

0.02MB

거기서 간단하게 테스트 해봅시다. 혹시모르니 employees 테이블을 엑셀파일로 첨부해놓았어요

EMPLOYEE 테이블에 SALARY 연봉컬럼이 있고 COMMISSION_PCT 커미션퍼센트가 있다고 합시다. 

커미션 퍼센트가 없는 경우에는 당연히 연봉이 최종 연봉이 될거고

커미션 퍼센트가 있을 경우에는 커미션퍼센트만큼 금액이 더해져야 최종 연봉이 될거예요.

저걸 일일이 다 언제 계산하고 있냐! 그냥 최종연봉 하나만 보겠다 할 때 

 

SELECT employee_id, coalesce((commission_pct + 1) * salary, salary) tot_salary
FROM EMPLOYEES ;

요렇게 작성해볼 수 있습니다. NULL은 사칙연산해도 NULL이니까 만약 commision_pct가 NULL이라면 단순한 salary가 tot_salary값이 될거예요. 하지만 NULL이 아니면 해당 퍼센트만큼 추가로 더해진게 최종 연봉이 되겠죠? 

짠 잘 수행된 것을 확인하실 수 있습니다. 요렇게 활용하실 수도 있어요.

 

[join할때도 종종 쓰게 되던..]

심플 예시

이 외에도 A테이블에 a라는 컬럼과 B테이블에 b라는 컬럼이 있는데, A테이블에 insert되는 경우 a에 저장되고 B테이블에 insert되는 경우 b라는 컬럼에 저장된다고 합시다. 그런데 둘다 의미하는바가 사실 같다면

coalesce(A.a, B.b) 를 통해 하나로 합칠 수 있어요.

요렇게~~!

 

오늘은 간단하게 COALESCE함수에 대해 살펴봤습니다 :) 

광고클릭/댓글/좋아요는 정보공유를 위해 힘쓰는 글쓴이에게 큰 힘이 됩니다. 그럼 다음 포스팅에서 찾아뵐게요~!