SQLD 를 위한 SQL기본문법 정리 6 - Function_2

<제어함수>

 

1) CASE Expression

- 표현식이지만 함수 성격 가지고 있음

- IF ~ THEN ~ ELSE 논리 흐름

SELECT PLAYER_NAME, HEIGHT,
        CASE WHEN HEIGHT > 180
             THEN HEIGHT
             ELSE 180
        END AS NEW_HEIGHT
FROM PLAYER;

180보다 크면 그대로 출력하고 180보다 작으면 180을 출력하도록 함

 

1-1) SEARCHED CASE EXPRESSION

  : 다양한 조건 사용 가능, 표현식이 복잡, 중첩도 

SELECT PLAYER_NAME,POSITION,
    CASE
        WHEN POSITION = 'GK' THEN '골키퍼'
        WHEN POSITION = 'DF' THEN '수비수'
        WHEN POSITION = 'MF' THEN '미드필더'
        WHEN POSITION = 'FW' THEN '공격수'
        ELSE '그 외'
    END AS 포지션
FROM PLAYER;

▶ 이거는 다 동등(=) 조건이라 Simple Case Expression으로 가능함.

SELECT PLAYER_NAME,
    CASE
        WHEN HEIGHT > 185 THEN '장식'
        WHEN HEIGHT BETWEEN 175 AND 185 THEN '평균'
        ELSE '단신'
    END AS 신장
FROM PLAYER;

▶ 이런 것은 동등 조건이 아니여서 Simple Case Expression에서 불가. 

▶ ▶ Searched Case Expression에서만 가능

 

SELECT PLAYER_NAME,HEIGHT,
    CASE
        WHEN HEIGHT > 185 THEN '장신'
        ELSE (
            CASE
                WHEN HEIGHT BETWEEN 175 AND 185 THEN '평균'
                ELSE '단신'
            END)
    END AS 신장
FROM PLAYER;

 

1-2) SIMPLE CASE EXPRESSION

 : 동등(=) 비교에만 가능, 표현식이 명료

 : 동일한 컬럼만 가능 (POSITION이 사용된것처럼), 모두 이퀄일때만 가능

SELECT PLAYER_NAME, POSITION,
    CASE POSITION
        WHEN 'GK' THEN '골키퍼'
        WHEN 'DF' THEN '수비수'
        WHEN 'MF' THEN '미드필더'
        WHEN 'FW' THEN '공격수'
        ELSE '그 외'
    END AS 포지션
FROM PLAYER;

 

▶ 위의 첫번째 예제와 동일한 결과 값이 나옴. 

 

 

2) DECODE

- 오라클에서만 사용되는 함수

- DECODE(표현식, 기준값1, 출력값1 [기준값2, 출력값2,..., 디폴트값])

- 표현식의 값이 기준값1이면 값1을 출력, 기준값2이면 값 2를 출력

- 기준값이 없으면 디폴트 값을 출력

- Simple Case Expression 경우만 가능함.

SELECT PLAYER_NAME, POSITION,
       DECODE (POSITION,
               'GK', '골키퍼',
               'DF', '수비수',
               'MF', '미드필더',
               'FW', '공격수',
               '그 외') AS POSITION
FROM PLAYER;

 

 

<NULL>

- 비어있는 값. 공백. 0과는 다른 의미. NULL을 포함하는 모든 산술연산의 결과는 NULL. 

- NULL과 공집합도 역시 다른 의미

 

결과가 없는 공집합과 NULL은 다르다.

 

남현우의 POSITION의 값은 NULL이다.

 

<NULL 관련함수>

1) NVL(표현식, 대체값) : 표현식의 값이 NULL이면 대체값, NULL이 아니면 표현식의 값을 반환

표현식과 대체값의 데이터 타입이 같아야 함

SELECT PLAYER_NAME, POSITION, NVL(POSITION, '없음') AS 포지션
FROM PLAYER
WHERE PLAYER_NAME='남현우';

Q. CASE문으로 바꿔보기

SELECT PLAYER_NAME, POSITION,
    CASE 
        WHEN POSITION IS NULL THEN '없음'
        ELSE POSITION
    END AS 포지션
FROM PLAYER;

 

Q. emp 테이블에서 연봉 = SQL * 12 + COMM 이라고 할 때, COMM인 NULL인 사람의 결과는?

SELECT ENAME, SAL, COMM, (SAL*12)+COMM AS 연봉
FROM EMP
WHERE EMPNO = 7369;

 

COMM이 NULL이기 때문에 어떠한 산술연산도 되지 않아 결과는 결국 NULL로 나온다.

 

그래서 NVL을 꼭 써야겠구만

SAL도 받는데 연봉이 NULL이면 억울하자너

 

SELECT ENAME, SAL, COMM, (SAL*12)+NVL(COMM,0) AS 연봉
FROM EMP
WHERE EMPNO = 7369;

** 사실 NVL(SAL,0)*12도 해야함 : 안전한 방식으로 모든 숫자는 NULL을 확인해서 진행

 

그래서 산술연산하는 것을 NVL을 통해 NULL값을 확인하여 0으로 바꿔줘서

계산한다면 연봉 계산 가능!

곱셈이라면 *1 을 해야함.

 

2) NULLIF(표현식1, 표현식2) : 특정 값을 NULL로 변환할 때 사용.

두 식이 같으면 NULL, 같지 않으면 표현식1의 값을 반환

SELECT PLAYER_NAME, POSITION, NULLIF(POSITION,'GK') AS 포지션
FROM PLAYER;

: CASE구문으로 표현도 된다. 

SELECT PLAYER_NAME, POSITION,
       CASE 
            WHEN POSITION = 'GK' THEN NULL
            ELSE POSITION
        END AS 포지션
FROM PLAYER;

 

3) COALESCE(표현식1, 표현식2,...) : 임의의 개수의 표현식에서 NULL이 아닌 최초의 표현식 반환. 

모든 표현식이 NULL이라면 NULL을 반환

SELECT E_PLAYER_NAME, NICKNAME, PLAYER_NAME,
COALESCE(E_PLAYER_NAME, NICKNAME, PLAYER_NAME)
FROM PLAYER;

 

: COALESCE안에 지정한 순서대로 NULL값이 아닌 것을 출력함