반응형
COALESCE 란?
- 인수를 순서대로 평가하고 처음으로 NULL이 아닌 첫 번째 식의 현재 값 반환
- 예시) select coalesce(null, null, 'third_value', 'fourth_value');
- null이 아닌 값 : 세 번째 값('third_value') 반환
구문
COALESCE (n1, n2, n3...)- n : 모든 형식의 인자
반환 형식
- 우선 순위가 가장 높은 식의 데이터 형식 반환
- 참고 : 데이터 타입 우선 순위
- 모든 인자가 null일 경우, null 반환
사용 예시
select coalesce(null, 'A')- 인자가 null일 경우, null이 아닌 첫 번째 값인 'A' 반환
CASE vs COALESCE
- 공통점 : 둘 다 조건에 따라 값을 반환
- CASE
- 조건에 따라 값을 반환
- 조건이 참인 경우 반환할 값 지정
- ELSE 절을 사용하여 모든 조건이 거짓인 경우에 반환 값 지정
- COALESCE
- 여러 개의 인수 중에서 NULL이 아닌 첫 번째 값 반환
- 같은 null 인 조건으로 비교한 두 함수의 문법 비교
DECLARE @val1 CHAR(1)
DECLARE @val2 CHAR(3)
/* Case, Coalesce */
SELECT
CASE
WHEN (@val1 IS NULL) THEN 'val1 is null value'
WHEN (@val2 IS NULL) THEN 'val2 is null value'
ELSE 'this is null value'
END as 'use CASE',
COALESCE(@val1, @val2, 'this is null value') as 'use COALESCE'
ISNULL vs COALESCE
- 공통점 : null을 확인하기 위한 함수
| isnull | coalesce | |
| 평가(null 체크) | 한 번 | 인자에 따라서 여러 번 |
| 매개 변수 | 두 개 사용(널 확인 값, 대체 값) | 인자에 따라 가변적 |
| 반환 데이터 타입 | 첫 번째 매개 변수의 데이터 타입 | 우선 순위가 가장 높은 값의 데이터 타입 |
| 널 허용 여부 | 허용 안함 null일 경우, 값 대체 |
허용 함 null일 경우, null 반환 |
평가 여부, 매개 변수
select isnull(null, 'A') as 'isnull',
coalesce(null, null, 'A') as 'coalesce'
- 이해를 위한 예시
- isnull
- 첫 번째 매개 변수가 null인지 아닌지 평가를 한 번 수행
- coalesce
- 첫 번째, 두 번째 매개 변수가 null인지 아닌지 평가를 두 번 수행
반환 데이터 타입
DECLARE @val1 CHAR(1)
DECLARE @val2 CHAR(3)
select isnull(@val1, 'ABC') as 'replace_val1',
isnull(@val2, 'ABC') as 'replace_val2',
coalesce(@val1, @val2, 'ABCDEFG') as 'replace data'
- 이해를 위한 예시, val1과 val2는 선언만 했을 뿐 초기화를 진행하지 않음(=null 상태)
- isnull
- 첫 번째 매개 변수의 데이터 타입 반환
- val1의 데이터 타입 : CHAR(1)
- 'ABC'를 입력하여도 해당 데이터 타입을 반환하기 때문에 'A' 반환
- coalesce
- 우선 순위가 가장 높은 데이터 타입 반환
- val1 : char(1)
- val2 : char(3)
- 세 번째 매개 변수 : char(7) : ['ABCDEFG']
- 세 번째 매개 변수의 데이터 타입 반환하므로 글자 그대로 출력
- 즉, isnull보다 상대적으로 데이터 타입에 많은 제한을 받지 않음
- 우선 순위가 가장 높은 데이터 타입 반환
널 허용 여부
DECLARE @val1 CHAR(1)
DECLARE @val2 CHAR(1)
select isnull(@val1, 'A') as 'isnull',
coalesce(@val1, @val2, null) as 'coalesce'
- 이해를 위한 예시
- isnull
- 매개 변수에 대한 null을 확인하고 대체값을 대입하기 때문에 '굳이' null로 다시 처리 할 필요가 없음
- 예) isnull(null, null)
- 즉, isnull로 null값에 대한 출력을 허용하지 않음
- 매개 변수에 대한 null을 확인하고 대체값을 대입하기 때문에 '굳이' null로 다시 처리 할 필요가 없음
- coalesce
- 여러 개의 매개 변수에 대한 null을 확인하고 모든 값이 null일 경우, null값 반환
- 즉, null값에 대한 출력을 허용함으로써 null일수도, 아닐수도 있는 조건식을 좀 더 유연하게 처리 할 수 있음
COALESCE 반환 값 우선 순위 Quiz
아래의 구문은 본격적으로 내가 포스팅을 하게 된 이유이다.
왜??? 어째서??? 라는 시작에서 이 글을 작성하게 되었다.
해당 결과는 SSMS(MS-SQL)에서 확인하였으며 다른 DBMS에서는 다르게 보일 수 있다.
이는 서로 다루는 데이터 타입의 크기가 다르기 때문이다.
Q. 다음 구문에서 출력될 '값'과 '데이터 타입'은?
select coalesce(1, 3.0, 'a')A. 출력될 값 : 1.0 / 데이터 타입 : float < 드래그하면 답이 보입니다.
설명.
- 값
- null이 아닌 첫 번째 값을 반환하므로 값은 1을 반환함
- 데이터 타입
- '우선 순위가 가장 높은' 데이터 타입을 반환한다.
- 그런데 이상한 점은 정수와 실수의 리터럴 타입은 int와 float.
- 즉, 각각 4바이트로 알고 있었기에 당연히 정수로 반환되는 줄 알았다.
- 하지만 같은 숫자 계열일지라도 '범위가 더 큰'의 조건을 따지면,
- float는 기본적으로 지정해주지 않으면 float(53)의 값을 가지므로 8바이트로 설정된다.
- 때문에 더 큰 크기의 float의 데이터 타입으로 반환 되는 것.
참고
반응형