SQL 함수에 대해 알아봅시다!

SQL 함수

반응형

 

1) SQL 내장 함수란?

• DB가 미리 만들어 둔 “도구”입니다.
• 데이터를 가공(변환), 계산, 정리할 때 씁니다.

✅ 예시
이름을 소문자로 바꾸기, 날짜에 3개월 더하기, NULL을 0으로 바꾸기 등.

---

2) 함수의 큰 분류: 단일행 vs 다중행

① 단일행 함수(Single-Row)

• 행 1개 입력 → 결과 1개
• 문자열/숫자/날짜를 한 행씩 가공할 때 사용

✅ 예: LOWER(ENAME), ROUND(SAL, 2)

② 다중행 함수(Multi-Row = 집계 함수)

• 여러 행 입력 → 결과 1개
• 합계/평균/개수 같은 통계

✅ 예: SUM(SAL), AVG(SAL), COUNT(*)
(이미지에서는 집계/윈도우는 뒤에서 다룬다고 언급)

---

3) 문자형 함수(대표 기능 + 예시)

• LOWER('Hello') → hello (소문자)
• UPPER('hello') → HELLO (대문자)
• ASCII('A') → 65 (문자의 코드값)
• CHR(65) → A (코드값을 문자로)
• CONCAT('Hello','World') → HelloWorld (문자 연결)
• SUBSTR('Hello', 1, 2) → He (부분 추출)
• LENGTH('Hello') → 5 (길이)
• LTRIM/RTRIM/TRIM → 좌/우/양쪽 공백 제거

✅ 예시(사원 이름 앞뒤 공백 정리)

SELECT TRIM(ENAME) AS clean_name
FROM EMP;

 

 

 

---

4) 숫자형 함수(대표 기능 + 예시)

• ABS(-15) → 15 (절댓값)
• SIGN(-10/0/10) → -1/0/1 (부호)
• MOD(17,5) → 2 (나머지, 오라클)
• CEIL(4.3) → 5 (올림)
• FLOOR(4.7) → 4 (내림)
• ROUND(3.14159,2) → 3.14 (반올림)
• TRUNC(3.14159,2) → 3.14 (버림)
• POWER(2,3) → 8 (제곱)
• SQRT(9) → 3 (제곱근)
• LOG, LN, EXP 등 로그/지수 함수

✅ 예시(연봉 계산)

SELECT ENAME, SAL, (SAL * 12) AS ANNUAL_SAL
FROM EMP;

 

 

 

---

5) 날짜형 함수(대표 기능 + 예시)

• SYSDATE: DB 서버의 현재 날짜/시간(오라클)
• CURRENT_DATE: 세션(사용자 환경)의 현재 날짜/시간(시간대 영향 가능)
• ADD_MONTHS(date, n): n개월 더하기
• MONTHS_BETWEEN(d1, d2): 두 날짜 간 개월 차
• NEXT_DAY(date, 'MONDAY'): 다음 특정 요일
• LAST_DAY(date): 그 달의 마지막 날
• TRUNC(date): 시간 제거(날짜만)
• ROUND(date, 'MONTH'): 월 단위 반올림
• EXTRACT(YEAR FROM date): 연/월/일 같은 일부만 뽑기

✅ 예시(오늘 기준 3개월 뒤)

SELECT ADD_MONTHS(SYSDATE, 3) AS after_3_months
FROM DUAL;

 

 

 

---

6) 변환 함수(타입 바꾸기)

• TO_CHAR: 날짜/숫자 → 문자열
• TO_DATE: 문자열 → 날짜
• TO_NUMBER: 문자열 → 숫자
• CAST: 타입을 명시적으로 변환
• CONVERT: 문자셋 변환(인코딩)

✅ 예시(날짜를 “YYYY-MM-DD” 문자열로)

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS today
FROM DUAL;

 

 

 

✅ 예시(문자 ‘12345’를 숫자로)

SELECT TO_NUMBER('12345') AS num_value
FROM DUAL;

 

 

 

---

7) CASE 문(조건 분기)

• 조건에 따라 값을 다르게 출력하는 SQL의 if-else 입니다.

(1) 검색 CASE (조건식 기반)

SELECT
ENAME,
SAL,
CASE
WHEN SAL < 1000 THEN 'Low'
WHEN SAL BETWEEN 1000 AND 3000 THEN 'Medium'
ELSE 'High'
END AS SALARY_GRADE
FROM EMP;

 

 

 

(2) 단순 CASE (값 비교)

SELECT
ENAME,
JOB,
CASE JOB
WHEN 'CLERK' THEN 'Clerk Job'
WHEN 'SALESMAN' THEN 'Sales Job'
ELSE 'Other Job'
END AS JOB_DESC
FROM EMP;

 

---

8) NULL 핵심 성질(다시 정리)

• NULL은 0이나 빈문자('')가 아니라 “값이 없음/모름”입니다.
• 그래서 보통 다음이 성립합니다.
  • 연산: 5 + NULL = NULL
  • 문자 결합: 'Hello' || NULL = NULL (오라클)
  • 비교: NULL = 1 같은 비교는 TRUE/FALSE가 아니라 “판단 불가” 성격이라 주의
  • 집계함수: SUM, AVG 같은 집계는 보통 NULL을 무시하고 계산합니다.

---

9) NVL (오라클 전용)

의미

• NVL(값, 대체값)
• 값이 NULL이면 대체값으로 바꿉니다.

예시(커미션 NULL이면 0)

SELECT ENAME, NVL(COMM, 0) AS COMMISSION
FROM EMP;

• COMM이 NULL인 사람은 0으로 보여줌

---

10) COALESCE (표준 SQL, 대부분 DB 지원)

의미

• COALESCE(a, b, c, ...)
• 왼쪽부터 검사해서 처음으로 NULL이 아닌 값을 반환합니다.
• 단, 전부 NULL이면 NULL이 반환됩니다.

예시(커미션이 없으면 월급을, 월급도 없으면 0)

SELECT ENAME,
COALESCE(COMM, SAL, 0) AS VALUE
FROM EMP;

• COMM이 있으면 COMM
• COMM이 NULL이면 SAL
• COMM도 SAL도 NULL이면 0

✅ 실무에서 “우선순위 있는 대체값” 만들 때 가장 많이 씁니다.

---

11) NULLIF (표준 SQL, 대부분 DB 지원)

의미

• NULLIF(a, b)
• a와 b가 같으면 NULL, 다르면 a를 반환합니다.

예시(0이면 NULL로 바꾸기)

SELECT ENAME,
NULLIF(COMM, 0) AS NULLIF_VALUE
FROM EMP;

• COMM이 0이면 “의미 없는 값”이라고 보고 NULL로 바꿈
• COMM이 300이면 300 유지

✅ 자주 쓰는 패턴

• “0을 ‘없음’으로 취급하고 싶을 때”
• “분모가 0일 때 0으로 나누기 오류 피하기” 같은 상황에도 응용

---

12) IFNULL (MySQL)

의미

• IFNULL(값, 대체값)
• NVL과 동일: NULL이면 대체값 반환

예시

SELECT ENAME, IFNULL(COMM, 0) AS IFNULL_VALUE
FROM EMP;

 

 

 

---

13) ISNULL (SQL Server)

의미

• ISNULL(값, 대체값)
• NULL이면 대체값 반환 (NVL/IFNULL과 같은 역할)

예시

SELECT ENAME, ISNULL(COMM, 0) AS ISNULL_VALUE
FROM EMP;

 

---

14) 간단  예시 3개

(1) 총 보상 = 월급 + 커미션(커미션 NULL이면 0)

• Oracle: SAL + NVL(COMM, 0)
• 표준: SAL + COALESCE(COMM, 0)

(2) “커미션 없으면 월급을 대신 보여주기”

• COALESCE(COMM, SAL)

(3) 커미션이 0이면 “없음(NULL)”로 처리

• NULLIF(COMM, 0)

 

 

---

초간단 요약

• 내장 함수: DB가 제공하는 데이터 가공 도구
• 단일행 함수: 행 1개 → 결과 1개 (문자/숫자/날짜/변환)
• 집계 함수: 여러 행 → 결과 1개 (SUM/AVG/COUNT 등)
• CASE: SQL의 조건문(if-else)
• NVL(Oracle): NULL이면 대체
• IFNULL(MySQL): NULL이면 대체
• ISNULL(SQL Server): NULL이면 대체
• COALESCE(표준): 여러 후보 중 “첫 번째 NULL 아닌 값”
• NULLIF(표준): 두 값이 같으면 NULL로 바꿈