삼팔광땡 MYSQL 입문기 02 - CASE, COALESCE, WHEN절에서 NULL 변환, DEFAULT

삼팔광땡~ MYSQL 입문기 02

자, 이제 첫 걸음이다.  칼을 뽑았으니 이제 무 밭을 발견했으니 무를 뽑아보자.

0. 이런 실수? 할 수 있다. 

비전공자 또는 낙엽줄로 개발 공부하면서 섭섭하게 느끼는 점이 하나 있다면 너무 당연?한 것은 알려주지 않는다는 것이다. 매번 포스팅을 작성하기 앞서 그런 내용들을 기억나는 데로 적어두려한다. 

1) 테이블에서 세로을 컬럼(열), 가로을 레코드(행)이라고 부른다.

1. CASE  

CASE문은 WHEN, ELSE, END 등 다양한 연산자와 함수를 적절히 조합해 특정한 형태로 반환할 수 있다. 

1) 예제가 되는 테이블 하나를 출력해보자

 select * from sample37;

 

2) 테이블을 살펴보니 NULL 값?( 'NULL'은 데이터 값이 아니라, 미지의 값에 대한 표시)이 있다. NULL 값은 더하거나 빼거나 곱하거나 나눌 수 없는 값이다. 때문에 필요에 따라 NULL의 값을 '0' 또는 '원하는 값'으로 변경할 수 있어야한다. 이러한 요구사항을 CASE문을 통해 실습해보자 

(1) 일단 이렇게 해보자. 코드를 설명하자면, ( 괄호 )안의 내용이 CASE 문이다. ( 괄호 )뒤에 있는 a 는 컬럼의 이름이다. Alias 알리아스 라고 불리운다. 

select 
  ( case when a is null 
    then 0 else a
    end
  )  a
from sample37;

+ α )   만약 Alias가 없다면 위의 코드의 결과는 다음과 같다.

+ α )   아, 물론, 위의 코드는 아래와 같이 한 줄로 써도 된다. 오라클에서 써 버릇한 개인적인 취향이다. 개인적으로는 가독성을 위해 양쪽 방식 번갈아가면서 쓴다.

select (case when a is null then 0 else a end) a from sample37; 

(2) 위 예제들에서의 결과를 보면 컬럼이 하나라 비교가 어렵다. 이번엔 컬럼을 추가해서 비교해보자.

select a,
  ( case when a is null 
    then 0 else a
    end 
  ) "a(null=0)"
from sample37;

+ α )   주의깊게 보면 다른 점 하나를 발견할 수 있다.  "a(null=0)"   부분이다. Alias라고 하는 부분인데 위의 예제 a에는 a에 " "가 없었다. 문자열의 경우 " " 없이도 사용가능하지만 a(null=0) 에는 연산구문이 포함되어있기 때문에 강제로 문자열화 하기 위해 " "로 감싸준 것이다. 더불어 이 부분은 " "가 아닌   'a(null=0)'  처럼 ' 싱글 커테이션으로 표현 가능하다. 

 

2. COALESCE  

앞의 예제에서 NULL을 바꾸기 위해    case when a is null then 0 else a   문을 사용했다. 이제와서..라도 해석하면, 아래와 같지 않을까 한다.

-> 경우가( CASE ) 언제냐면( WHEN ) 선택한컬럼( a ) 이 NULL( NULL ) 이면,대신( THEN ) 0을넣고( 0 ) 아니면( THEN ) 선택한컬럼값을넣어라( a ) 

허나, CASE문을 활용해서 길게 서술했던 쿼리를 간편하게 수행해주는 함수가 있었으니 이름하여 COALESCE 가 되시겠다. 

사용 방법은 CASE문 없이도, 아래와 같이 COALESCE(선택한 컬럼, 바꾸고자 하는 값)을 select 문 다음에 넣으면 된다.

select 
  a, 
  coalesce(a,0) 
from sample37;

3. CASE 문의 활용  

그럼 case문은 언제 사용되는가? 에 대한 실습을 진행해보겠다.

실습을 위한 상황극을 연출해보자. 주민등록에서 남자는 1, 여자는 2로 표현된다. 하지만 이를 모르는 사람들에게는 1/2라고 표현하기보다 남자/여자로 표현하기 쉬울 것 같다. 책에 의하면 이와같이 특정한 데이터를 문자의 형태로 바꾸는 것을 '디코드'라고 하고 수치화하는 것을 '인코드'라고 부른다.  

디코드 방식은 2가지 방법이 있다. CASE문 안에서 개별적으로 검색을 하면서 변환하거나 CASE 문에서 비교할 항목 따로 지정하는 방식이 있다.

1) 검색 CACE 

select a AS '코드',
case
  when a=1 then '남자'
  when a=2 then '여자'
  else '미지정'
end as '성별'
from sample37;

2) 단순 CACE 

select a AS '코드',
case a
  when 1 then '남자'
  when 2 then '여자'
  else '미지정'
end as '성별'
from sample37;

+ α )   추가로 CASE는 SELECT 문 외에도 WHERE ORDER BY문에서도 사용할 수 있다.  

3) 그리고 WHEN 절에서도 NULL을 변환할 수 있다. 

select a AS '코드',
case 
  when a=1 then '남자'
  when a=2 then '여자'
  when a is null then '데이터 없음 '
  else '미지정'
end as '성별'
from sample37;

+ α )   추가로 ORACLE 에도 NULL을 변환해주는 NVL라는 전용 함수가 있다. 하지만, 표준 SQL를 사용하도록 해보자.

 

4. DEFAULT

실습01 에서 했던 DESC 명령으로 열 구성을 살피다 보면 종종 Default 라는 컬럼을 볼 수 있다. 이 항목은 자료가 insert될 때 명시적으로 지정하지 않은 경우 빈 값인 NULL을 표시하게 된다. 앞에서 언급했듯이 값 중에 NULL 표시가 있으면 연산에 제약이 되는 경우가 종종 있다. NULL이 생길때마다 앞의 실습에서 처럼 CASE문 또는 COALESCE 함수를 이용해서 변환할 수도 있지만, DEFAULT 함수를 이용하면 처음부터 NULL 대신 지정한(기본 0) 값을 넣을 수 있다.

1) 일단 DESC 명령으로 실습용 테이블의 열 구성을 확인해 보겠습니다.

 desc sample411;

+ α )   추가로 DEFAULT 값은 어떻게 설정하는가! DEFAULT 값은 테이블을 생성할때 설정할 수 있습니다. 물론 ALTER라는 명령을 통해서도 수정 및 추가 설정이 가능합니다. 설정 방법은.. 검색해보니 여기 사이트가 나쁘지 않을 것 같습니다.

2) 먼저 모든 컬럼에 값을 넣어 행을 추가해 보겠습니다.

 insert into sample411(no, d) values(1,1);

 select * from sample411;

3) 먼저 한 개의 컬럼에는 명시적으로 값을 지정하고 나머지 행은 디폴트 함수로 값을 지정해 보겠습니다. 

insert into sample411(no, d) values(2,default);

select * from sample411;

그람, 두번째 실습도 20000 끄읏 ;D