[SQL]오라클 SQL 함수 및 조인 완전 정리 (NVL, DECODE, CASE, RANK, 조인 등)

이번 글에서는 오라클 SQL의 다양한 함수와 조인 방식에 대해 학습한 내용을 정리하였습니다. 실무나 자격시험, 데이터 분석 준비에 활용할 수 있도록 NVL, DECODE, CASE, 그룹함수, RANK 함수, 그리고 조인과 집합 연산자에 이르기까지 폭넓게 다뤄보았습니다.

 

1. NVL / NVL2 함수

 

NVL과 NVL2는 NULL 값을 처리할 때 사용하는 함수입니다.

• NVL(column, value) : 컬럼 값이 NULL일 경우 value를 반환합니다.
• NVL2(column, value_if_not_null, value_if_null) : 컬럼이 NULL이 아니면 첫 번째 값을, NULL이면 두 번째 값을 반환합니다.

예시:

-- commission_pct가 NULL이면 1로 대체하여 계산

SELECT salary * NVL(commission_pct, 1)

FROM employees

ORDER BY commission_pct;



-- commission_pct가 NULL인지 여부에 따라 계산 방식 분기

SELECT FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT,

       NVL2(COMMISSION_PCT, SALARY * (1 + COMMISSION_PCT), SALARY) AS TOTAL_COMPENSATION

FROM EMPLOYEES;

 

2. DECODE 함수

DECODE는 특정 값에 따라 조건을 나누어 처리하는 일종의 IF-ELSE 문입니다.

-- 부서가 60일 경우 급여를 10% 인상

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY AS 원래급여,

       DECODE(DEPARTMENT_ID, 60, SALARY*1.1, SALARY) AS 조정된급여,

       DECODE(DEPARTMENT_ID, 60, '10%인상', '미인상') AS 인상여부

FROM EMPLOYEES;



-- 부서 ID에 따라 과목명 지정

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY,

       DECODE(DEPARTMENT_ID, 10, '과학', 100, '수학', 110, '영어', '기타') AS 과목명

FROM EMPLOYEES;

 

3. CASE / WHEN / THEN 구문

CASE 문은 DECODE보다 더 유연하게 조건 분기를 처리할 수 있습니다.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY,

       CASE

           WHEN SALARY >= 9000 THEN '상위급여'

           WHEN SALARY BETWEEN 6000 AND 8999 THEN '중위급여'

           ELSE '하위급여'

       END AS 급여등급

FROM EMPLOYEES

WHERE JOB_ID = 'IT_PROG';

 

4. RANK / DENSE_RANK / ROW_NUMBER 함수

RANK, DENSE_RANK, ROW_NUMBER는 순위, 랭킹 정보를 처리하는 분석 함수입니다.
OVER 절과 함께 사용됩니다.

SELECT EMPLOYEE_ID, SALARY,

       RANK()        OVER(ORDER BY SALARY DESC) AS RANK_급여,

       DENSE_RANK()  OVER(ORDER BY SALARY DESC) AS DENSE_RANK_급여,

       ROW_NUMBER()  OVER(ORDER BY SALARY DESC) AS ROW_NUMBER_급여

FROM EMPLOYEES;

 

부서별 급여 순위를 구하고자 할 경우:

SELECT E.EMPLOYEE_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME, SALARY,

       RANK()        OVER(PARTITION BY D.DEPARTMENT_ID ORDER BY SALARY DESC) AS RANK_급여,

       DENSE_RANK()  OVER(PARTITION BY D.DEPARTMENT_ID ORDER BY SALARY DESC) AS DENSE_RANK_급여,

       ROW_NUMBER()  OVER(PARTITION BY D.DEPARTMENT_ID ORDER BY SALARY DESC) AS ROW_NUMBER_급여

FROM EMPLOYEES E, DEPARTMENTS D

WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID

ORDER BY D.DEPARTMENT_ID, E.SALARY DESC;

 

5. 그룹 함수 (집계 함수)

• COUNT: 행의 개수
• SUM: 합계
• AVG: 평균
• MIN, MAX: 최소값, 최대값

SELECT COUNT(*) AS 총인원, SUM(SALARY) AS 총급여, AVG(SALARY) AS 평균급여

FROM EMPLOYEES;

 

NULL 처리:

SELECT COUNT(*) - COUNT(COMMISSION_PCT) AS COMMISSION_PCT_NULL_행수

FROM EMPLOYEES;

 

6. GROUP BY 절

GROUP BY는 특정 컬럼 기준으로 데이터를 묶어 집계합니다.

SELECT JOB_ID AS 직무,

       SUM(SALARY) AS 총급여,

       AVG(SALARY) AS 평균급여

FROM EMPLOYEES

GROUP BY JOB_ID;

 

다중 그룹핑:

SELECT JOB_ID, MANAGER_ID,

       SUM(SALARY) AS 총급여

FROM EMPLOYEES

GROUP BY JOB_ID, MANAGER_ID;

 

7. HAVING 절

HAVING은 GROUP BY 결과에 조건을 걸 때 사용합니다.

SELECT JOB_ID,

       SUM(SALARY) AS 총급여

FROM EMPLOYEES

GROUP BY JOB_ID

HAVING SUM(SALARY) > 30000;

 

8. 조인 종류 정리

동등 조인 (Equi Join)

SELECT *

FROM EMPLOYEES A, DEPARTMENTS B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

외부 조인 (Outer Join)

SELECT A.EMPLOYEE_ID, A.FIRST_NAME, B.DEPARTMENT_NAME

FROM EMPLOYEES A, DEPARTMENTS B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);

자체 조인 (Self Join)

SELECT A.EMPLOYEE_ID, A.MANAGER_ID,

       B.FIRST_NAME || ' ' || B.LAST_NAME AS MANAGER_NAME

FROM EMPLOYEES A, EMPLOYEES B

WHERE A.MANAGER_ID = B.EMPLOYEE_ID;

 

9. 집합 연산자 (UNION, INTERSECT, MINUS)

-- 중복 제거 후 합집합

SELECT DEPARTMENT_ID FROM EMPLOYEES

UNION

SELECT DEPARTMENT_ID FROM DEPARTMENTS;



-- 중복 포함 합집합

SELECT DEPARTMENT_ID FROM EMPLOYEES

UNION ALL

SELECT DEPARTMENT_ID FROM DEPARTMENTS;



-- 교집합

SELECT DEPARTMENT_ID FROM EMPLOYEES

INTERSECT

SELECT DEPARTMENT_ID FROM DEPARTMENTS;



-- 차집합

SELECT DEPARTMENT_ID FROM DEPARTMENTS

MINUS

SELECT DEPARTMENT_ID FROM EMPLOYEES;

 

10. 마무리하며

이번 포스팅에서는 오라클 SQL에서 자주 사용되는 함수들과 다양한 조인 방법, 집합 연산자에 대해 학습한 내용을 정리하였습니다.
각 함수는 실무 데이터 분석, BI 리포트, ERP 데이터 관리 등 다양한 업무에 매우 유용하게 사용됩니다.