← 정보처리기사 실기 개념정리 목차

SQL 완전 정리 ② 함수·NULL·CASE

1편이 "어떤 행을 고를까"였다면 2편은 "고른 행의 값을 어떻게 가공할까"다. 문자를 자르고, 숫자를 반올림하고, 날짜를 더하고, NULL 을 다른 값으로 바꾸고, 조건에 따라 값을 고르는 일 — 전부 함수와 CASE 식으로 한다. 함수는 표준이 약하고 엔진마다 이름·동작이 많이 다른 영역이라, 각 절에 엔진 대응표를 붙였다. 쿼리와 결과는 SQLite 3.54 · PostgreSQL 에서 실제로 실행해 맞췄고, Oracle·MySQL 전용 함수(NVL, DECODE, ADD_MONTHS 등)는 실행 대신 표준 대응과 함께 동작을 설명한다.


0. 이 편에서 쓰는 테이블

사원

enoenamednojobsalmgrhiredatebonus
1김하늘10팀장500NULL2019-03-01NULL
2이도윤10대리35012021-07-1530
3박서준10사원28022023-01-02NULL
4최지우20팀장450NULL2018-11-2050
5정민재20사원30042022-05-0920
6한유나20사원30042024-02-01NULL
7오세린NULL사원250NULL2025-01-06NULL

문자 함수 예제는 테이블 없이 리터럴('DATABASE' 등)로도 실습한다. SELECT 함수(…); 처럼 FROM 없이 실행할 수 있다(Oracle 은 FROM DUAL 필요).


1. 단일행 함수 vs 다중행 함수

함수는 "몇 행을 받아 몇 값을 내느냐"로 두 종류다.

구분입력 → 출력예쓸 수 있는 곳
단일행 함수행 1개 → 값 1개 (행마다 따로 계산)UPPER, SUBSTR, ROUND, COALESCE, CASESELECT·WHERE·ORDER BY·GROUP BY 등 어디든
다중행 함수(집계 함수)행 여러 개(그룹) → 값 1개COUNT, SUM, AVG, MAX, MINSELECT·HAVING·ORDER BY (WHERE 불가)
SELECT ename, LENGTH(ename) AS len FROM 사원 WHERE dno = 10;   -- 단일행: 3행 → 3값
enamelen
김하늘3
이도윤3
박서준3
SELECT COUNT(*) AS cnt, SUM(sal) AS 합 FROM 사원 WHERE dno = 10;   -- 다중행: 3행 → 1행
cnt합
31130
  • 단일행 함수는 중첩할 수 있고 안쪽부터 계산한다. UPPER(SUBSTR(REPLACE('db-admin', '-', ''), 1, 2)) → REPLACE 로 'dbadmin' → SUBSTR 로 'db' → UPPER 로 'DB'.
  • 집계 함수 안에 단일행 함수를 넣는 것도 된다: SUM(COALESCE(bonus, 0)) = 100, MAX(LENGTH(ename)) = 3.
  • 반대로 집계 결과와 개별 열을 GROUP BY 없이 섞으면 오류다(SELECT ename, COUNT(*) FROM 사원). 7행짜리 ename 과 1행짜리 COUNT 를 한 줄에 놓을 수 없기 때문이다. 집계 함수는 3편에서 자세히 다룬다.

2. 문자 함수

2.1 대소문자·길이 — UPPER · LOWER · INITCAP · LENGTH

SELECT UPPER('Hello Sql') AS u, LOWER('Hello Sql') AS l,
       LENGTH('Hello Sql') AS n, LENGTH('김하늘') AS k;
ulnk
HELLO SQLhello sql93
  • LENGTH 는 공백도 센다(9). 한글은 대소문자가 없어서 UPPER('김a') = '김A'.
  • INITCAP('hello sql world') → 'Hello Sql World' (단어 첫 글자만 대문자). Oracle·PostgreSQL 에 있고 SQLite·MySQL 에는 없다.

길이는 "글자 수"인가 "바이트 수"인가 — 엔진마다 다르다(UTF-8 에서 한글 한 글자 = 3바이트).

엔진글자 수바이트 수LENGTH('김하늘')
OracleLENGTHLENGTHB3
PostgreSQLLENGTH, CHAR_LENGTHOCTET_LENGTH3 (실측)
SQLiteLENGTHLENGTH(CAST(x AS BLOB))3 (실측)
MySQLCHAR_LENGTHLENGTH9
SQL ServerLEN (끝 공백 제외)DATALENGTH3

표준은 CHAR_LENGTH(글자 수)·OCTET_LENGTH(바이트 수). MySQL 에서 LENGTH 를 글자 수로 믿고 VARCHAR 길이 검사를 하면 한글에서 3배로 틀린다.

2.2 부분 문자열 — SUBSTR

SUBSTR(문자열, 시작위치 [, 길이])      -- 표준 표기: SUBSTRING(문자열 FROM 시작 FOR 길이)
  • 위치는 1부터 센다(프로그래밍 언어의 0 기반 인덱스와 다르다).
  • 길이를 생략하면 끝까지.
SELECT SUBSTR('DATABASE', 2, 3) AS a,   -- 2번째부터 3글자
       SUBSTR('DATABASE', 5)    AS b,   -- 5번째부터 끝까지
       SUBSTR(ename, 2, 1)      AS e    -- 김하늘 → 2번째 글자
FROM 사원 WHERE eno = 1;
abe
ATABASE하

D A T A B A S E 에 번호를 붙이면 1 2 3 4 5 6 7 8. 2번부터 3글자 = 2·3·4 = ATA, 5번부터 끝 = BASE.

음수·0 시작 위치는 엔진마다 다르다 — 실기 함정 단골.

식OracleSQLite (실측)PostgreSQL (실측)MySQL
SUBSTR('DATABASE', -3)ASE (뒤에서 3번째부터)ASEDATABASEASE
SUBSTR('DATABASE', -3, 2)ASAS'' (빈 문자열)AS
SUBSTR('DATABASE', 0, 3)DAT (0 을 1로 취급)DADA''

PostgreSQL 은 음수 위치를 "1보다 앞에 있는 가상의 위치"로 본다. -3 부터 2글자 = 위치 -3, -2 → 실제 글자가 하나도 없어 빈 문자열. Oracle·MySQL·SQLite 는 음수를 뒤에서부터 센다. 시험은 Oracle 방식(뒤에서부터)을 기준으로 낸다.

2.3 공백·문자 제거 — TRIM · LTRIM · RTRIM

SELECT '[' || TRIM('  ab c  ')  || ']' AS t,
       '[' || LTRIM('  ab  ')   || ']' AS l,
       '[' || RTRIM('  ab  ')   || ']' AS r;
tlr
[ab c][ab ][ ab]
  • TRIM 은 양 끝만 지운다. 가운데 공백(ab c 의 공백)은 남는다. 가운데까지 없애려면 REPLACE(x, ' ', '').
  • LTRIM = 왼쪽(Left), RTRIM = 오른쪽(Right).

공백 말고 다른 문자를 지우기 — 표준 문법과 함수형 문법이 있다.

-- 표준 (Oracle·PostgreSQL·MySQL)
SELECT TRIM(BOTH 'x' FROM 'xxabxx')     AS b,   -- ab
       TRIM(LEADING 'x' FROM 'xxabxx')  AS l,   -- abxx
       TRIM(TRAILING 'x' FROM 'xxabxx') AS t;   -- xxab
-- 함수형 (Oracle·PostgreSQL·SQLite)
SELECT LTRIM('xxabxx', 'x') AS l, RTRIM('xxabxx', 'x') AS r;   -- abxx | xxab

BOTH 가 기본값이라 TRIM('x' FROM 'xxabxx') 도 같다. SQLite 는 표준 BOTH … FROM 문법이 없고 TRIM('xxabxx', 'x') 함수형만 받는다.

함정 — LTRIM 의 두 번째 인자는 "문자열"이 아니라 "문자 집합"이다.

SELECT LTRIM('abcabxyz', 'abc') AS s;   -- xyz

'abc' 라는 덩어리를 한 번 지우는 게 아니라, a·b·c 중 어느 글자든 왼쪽에서 계속 지운다. a,b,c,a,b 를 지우고 x 에서 멈춰 xyz. (Oracle 의 TRIM 은 한 글자만 받는다는 점도 다르다.)

2.4 바꾸기 — REPLACE

REPLACE(문자열, 찾을문자열, 바꿀문자열)    -- 바꿀문자열 생략 시 Oracle 은 삭제
SELECT REPLACE('2024-01-02', '-', '') AS a,
       REPLACE('banana', 'an', 'AN')  AS b,
       REPLACE('a b c', ' ', '')      AS c;
abc
20240102bANANaabc
  • 모든 일치를 바꾼다(첫 번째만이 아님). banana 의 an 두 개가 모두 바뀌었다.
  • 대소문자를 구분한다. 한 글자씩 대응 치환은 Oracle·PG 의 TRANSLATE('abc', 'ab', 'xy') → 'xyc'.

2.5 위치 찾기 — INSTR · POSITION

INSTR(문자열, 찾을문자열 [, 시작 [, n번째]])    -- Oracle, MySQL(인자 2개), SQLite(인자 2개)
POSITION(찾을문자열 IN 문자열)                  -- 표준: PostgreSQL, MySQL
SELECT INSTR('DATABASE', 'A') AS a, INSTR('DATABASE', 'Z') AS b, INSTR('DATABASE', 'BASE') AS c;   -- SQLite
SELECT POSITION('A' IN 'DATABASE') AS a, POSITION('Z' IN 'DATABASE') AS b, STRPOS('DATABASE', 'BASE') AS c;  -- PostgreSQL
abc
205
  • 처음 나타나는 위치를 1부터 센다. DATABASE 의 첫 A 는 2번째.
  • 없으면 0 (NULL 도 -1 도 아니다). 그래서 WHERE INSTR(ename, '하') > 0 은 LIKE '%하%' 와 같은 조건이 된다.
  • 인자 순서가 반대인 것에 주의: INSTR(문자열, 찾을것) vs POSITION(찾을것 IN 문자열) vs MySQL LOCATE(찾을것, 문자열) vs SQL Server CHARINDEX(찾을것, 문자열).
  • Oracle 은 INSTR('DATABASE', 'A', 1, 2) = 4 (1번째 위치부터 찾아 2번째로 나오는 A).

2.6 채우기 — LPAD · RPAD

LPAD(문자열, 전체길이 [, 채울문자])    -- 왼쪽을 채워 전체길이로
RPAD(문자열, 전체길이 [, 채울문자])    -- 오른쪽을 채워
SELECT LPAD('7', 3, '0') AS a, RPAD('AB', 5, '*') AS b,
       LPAD('ABCDE', 3, '*') AS c, LPAD(CAST(eno AS VARCHAR), 4, '0') AS d
FROM 사원 WHERE eno = 2;      -- PostgreSQL
abcd
007AB***ABC0002
  • 두 번째 인자는 "채울 개수"가 아니라 결과의 전체 길이다. LPAD('7', 3, '0') → 0 을 2개 붙여 길이 3.
  • 원래 문자열이 전체 길이보다 길면 잘린다 — LPAD('ABCDE', 3, '*') 는 ***ABCDE 가 아니라 ABC.
  • 채울 문자를 생략하면 공백. Oracle·MySQL·PostgreSQL 에 있고 SQLite 에는 없다.

2.7 연결 — CONCAT

1편 §4.4 에서 본 대로 || 와 CONCAT 은 NULL 처리가 다르다.

SELECT CONCAT(ename, '-', job) AS a, CONCAT(ename, bonus) AS b
FROM 사원 WHERE eno IN (1, 2) ORDER BY eno;    -- SQLite·PostgreSQL
ab
김하늘-팀장김하늘
이도윤-대리이도윤30
엔진CONCAT 인자 수인자에 NULL 이 있으면
PostgreSQL · SQLite · SQL Server여러 개NULL 을 무시 (위 결과처럼 김하늘)
MySQL여러 개결과 전체가 NULL
Oracle딱 2개 (CONCAT(CONCAT(a, b), c) 로 중첩)NULL 을 빈 문자열로 (무시와 같음)

함수 조합 예 — 이름 마스킹

SELECT ename, SUBSTR(ename, 1, 1) || '**' AS 마스킹 FROM 사원 WHERE dno = 20 ORDER BY eno;
ename마스킹
최지우최**
정민재정**
한유나한**

3. 숫자 함수

3.1 반올림·버림·올림 — ROUND · TRUNC · CEIL · FLOOR

ROUND(수 [, 자릿수])   -- 반올림.  자릿수 기본 0
TRUNC(수 [, 자릿수])   -- 버림(0 쪽으로 자름). MySQL 은 TRUNCATE(수, 자릿수)
CEIL(수)              -- 올림: 그 수 이상인 가장 작은 정수.  SQL Server 는 CEILING
FLOOR(수)             -- 내림: 그 수 이하인 가장 큰 정수

자릿수의 의미 — 양수는 소수점 오른쪽 n자리까지 남기고, 음수는 소수점 왼쪽(정수부) n자리를 0으로 만든다.

자릿수남는 단위ROUND(123.456, n)
20.01123.46
10.1123.5
0 (생략)1123
-110120
-2100100
SELECT ROUND(123.456) AS a, ROUND(123.456, 1) AS b, ROUND(123.456, 2) AS c,
       ROUND(123.456, -1) AS d, ROUND(155, -2) AS e;     -- PostgreSQL
abcde
123123.5123.46120200

SQLite 는 음수 자릿수를 지원하지 않는다. 같은 쿼리가 SQLite 에서는 d = 123.0, e = 155.0 으로 나온다(음수를 0 으로 취급). Oracle·PostgreSQL·MySQL 은 위 표대로 동작한다.

SELECT TRUNC(123.456, 1) AS a, TRUNC(123.456) AS b, TRUNC(-2.7) AS c, TRUNC(155, -1) AS d;  -- PostgreSQL
SELECT CEIL(2.1) AS a, CEIL(-2.1) AS b, FLOOR(2.9) AS c, FLOOR(-2.1) AS d;
TRUNC(123.456,1)TRUNC(123.456)TRUNC(-2.7)TRUNC(155,-1)
123.4123-2150
CEIL(2.1)CEIL(-2.1)FLOOR(2.9)FLOOR(-2.1)
3-22-3

음수에서 세 함수가 갈린다 — 수직선으로 보면 명확하다.

함수방향2.7-2.7
TRUNC0 쪽으로 (소수부 잘라내기)2-2
FLOOR아래로 (−∞ 쪽)2-3
CEIL위로 (+∞ 쪽)3-2
ROUND가까운 쪽3-3

양수에서는 TRUNC = FLOOR 이지만 음수에서는 TRUNC = CEIL 이다.

.5 의 반올림 — ROUND(2.5), ROUND(-2.5) 는 Oracle·PostgreSQL(NUMERIC)·SQLite·MySQL(정확한 수) 모두 3, -3 (0에서 먼 쪽으로, "사사오입")이다. 단 PostgreSQL 의 double precision(부동소수) 타입은 짝수 쪽 반올림이라 ROUND(2.5::float8) = 2 로 실측된다. 금액 계산에 부동소수 타입을 쓰지 않는 이유 중 하나.

3.2 나머지·절댓값·거듭제곱 — MOD · ABS · POWER

SELECT MOD(7, 3) AS a, MOD(-7, 3) AS b, MOD(7, -3) AS c, 7 % 3 AS d;
SELECT ABS(-5) AS a, POWER(2, 10) AS b, SQRT(16) AS c, SIGN(-3) AS s;
MOD(7,3)MOD(-7,3)MOD(7,-3)7 % 3
1-111
ABS(-5)POWER(2,10)SQRT(16)SIGN(-3)
510244-1
  • MOD 결과의 부호는 나누어지는 수(왼쪽)를 따른다. MOD(-7, 3) = -1. (Python 의 -7 % 3 = 2 와 다르다. C·Java 의 % 는 SQL 과 같이 -1.)
  • % 연산자는 SQLite·PostgreSQL·MySQL·SQL Server 에서 되지만 Oracle 은 MOD 함수만.
  • MOD(x, 2) = 0 으로 짝수 행을 고르는 식이 자주 나온다.
  • SIGN 은 양수 1, 0 은 0, 음수 -1.

3.3 정수 나눗셈 — 엔진마다 결과가 다르다

SELECT 7 / 2 AS a, 7 / 2.0 AS b, 7.0 / 2 AS c, -7 / 2 AS e;
엔진7 / 27 / 2.0-7 / 2정수 몫 전용
PostgreSQL (실측)33.5000000000000000-3DIV(7, 2)
SQLite (실측)33.5-3—
SQL Server33.500000-3—
Oracle3.53.5-3.5TRUNC(7/2)
MySQL3.50003.5000-3.50007 DIV 2
  • 정수 ÷ 정수를 정수로 자르는 엔진(PG·SQLite·SQL Server)과 소수로 계산하는 엔진(Oracle·MySQL)이 갈린다. 자르는 엔진은 0 쪽으로 자른다(-3.5 → -3, FLOOR 가 아니다).
  • 한쪽만 소수(2.0)거나 형 변환(CAST(7 AS REAL) / 2)하면 어느 엔진이든 3.5.

실무에서 이게 터지는 예 — 평균을 손으로 계산할 때:

SELECT SUM(sal) / COUNT(*) AS a, AVG(sal) AS b FROM 사원 WHERE dno = 10;
엔진a (1130 / 3)b (AVG)
PostgreSQL376376.6666666666666667
SQLite376376.66666666666669

비율을 구할 때는 sal * 100.0 / 합계 처럼 소수 리터럴을 먼저 곱해 정수 나눗셈을 피한다.

SELECT ename, sal, ROUND(sal * 100.0 / 2430, 1) AS 비율 FROM 사원 WHERE dno = 10 ORDER BY eno;
enamesal비율
김하늘50020.6
이도윤35014.4
박서준28011.5

0 으로 나누기: PostgreSQL·Oracle·SQL Server 는 오류(division by zero), SQLite·MySQL 은 NULL. 안전하게 쓰려면 x / NULLIF(y, 0) (§5.3).


4. 날짜 함수

날짜는 표준이 가장 약한 영역이다. 개념(현재 시각, 날짜 ± 기간, 날짜 − 날짜 = 일수, 부분 추출, 문자 ↔ 날짜 변환)을 잡고, 엔진별 이름은 표로 대응시킨다.

4.1 현재 날짜·시각

의미표준OracleMySQLPostgreSQLSQL Server
오늘 날짜CURRENT_DATESYSDATE*, CURRENT_DATECURDATE(), CURRENT_DATECURRENT_DATECAST(GETDATE() AS DATE)
현재 일시CURRENT_TIMESTAMPSYSTIMESTAMPNOW()NOW(), CURRENT_TIMESTAMPGETDATE()

* Oracle 의 DATE 타입은 시·분·초까지 들어 있다. SYSDATE 는 날짜만이 아니라 현재 시각이다. 그래서 Oracle 에서 "오늘 입사자"를 hiredate = SYSDATE 로 찾으면 0행 — TRUNC(SYSDATE) 로 시각을 잘라야 한다.

4.2 날짜 연산 — 더하기·빼기

연산결과 타입PostgreSQLOracleMySQLSQLite
날짜 + n일날짜d + 30d + 30DATE_ADD(d, INTERVAL 30 DAY)date(d, '+30 days')
날짜 + n개월날짜d + INTERVAL '1 month'ADD_MONTHS(d, 1)DATE_ADD(d, INTERVAL 1 MONTH)date(d, '+1 month')
날짜 − 날짜일수d1 - d2 (정수)d1 - d2 (소수 포함)DATEDIFF(d1, d2)julianday(d1) - julianday(d2)
개월 차이수AGE(d1, d2)MONTHS_BETWEEN(d1, d2)TIMESTAMPDIFF(MONTH, d2, d1)—
-- PostgreSQL
SELECT hiredate, hiredate + 30 AS a, hiredate + INTERVAL '1 month' AS b,
       DATE '2025-01-06' - hiredate AS 근속일
FROM 사원 WHERE eno = 2;
hiredateab근속일
2021-07-152021-08-142021-08-15 00:00:001271

SQLite (date(hiredate, '+30 days'), julianday(…) 차이)로도 같은 날짜 · 1271.0 일이 나온다.

  • PostgreSQL 에서 date + INTERVAL 은 timestamp 가 되어 00:00:00 이 붙는다. 날짜만 원하면 CAST(… AS DATE).
  • DATE '2025-01-06' 은 표준 날짜 리터럴 — 문자열이 아니라 날짜 값임을 명시하는 표기.

월말 + 1개월 — 엔진마다 다르다

식PostgreSQL (실측)Oracle ADD_MONTHSMySQLSQLite (실측)
2024-01-31 + 1개월2024-02-292024-02-292024-02-292024-03-02

2월 31일이 없으니 PG·Oracle·MySQL 은 그 달의 마지막 날로 맞추고, SQLite 는 1월 31일 + 1개월 = "2월 31일" 을 그대로 넘겨 3월 2일로 정규화한다. (Oracle ADD_MONTHS 는 원래 날짜가 월말이면 결과도 월말로 맞춘다.)

날짜 조건 예 — 기준일(2025-01-06)로부터 3년 이내 입사자 (PostgreSQL):

SELECT ename, hiredate FROM 사원
WHERE hiredate >= DATE '2025-01-06' - INTERVAL '3 year'
ORDER BY hiredate;
enamehiredate
정민재2022-05-09
박서준2023-01-02
한유나2024-02-01
오세린2025-01-06

4.3 부분 추출 — EXTRACT

EXTRACT(필드 FROM 날짜)      -- 필드: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND ...
SELECT ename, EXTRACT(YEAR FROM hiredate) AS y, EXTRACT(MONTH FROM hiredate) AS m
FROM 사원 WHERE eno IN (1, 2) ORDER BY eno;     -- PostgreSQL (Oracle·MySQL 도 같은 문법)
enameym
김하늘20193
이도윤20217
  • 결과는 숫자다(3, '03' 이 아님).
  • 대응: MySQL YEAR(d)·MONTH(d), SQL Server YEAR(d)·DATEPART(year, d), SQLite strftime('%Y', d)(문자열 반환 — 숫자 비교하려면 CAST(strftime('%m', d) AS INTEGER)).
  • WHERE EXTRACT(YEAR FROM hiredate) >= 2023 → 박서준·한유나·오세린. 단 열에 함수를 씌우면 인덱스를 못 탄다. 같은 조건을 hiredate >= DATE '2023-01-01' 로 쓰는 것이 정석.

4.4 형 변환 함수 — TO_CHAR · TO_DATE (소개)

Oracle·PostgreSQL 은 형식 문자열로 날짜·숫자를 문자로, 문자를 날짜로 바꾼다.

SELECT TO_CHAR(hiredate, 'YYYY-MM')           AS a,
       TO_CHAR(hiredate, 'YYYY"년" MM"월"')    AS b,
       TO_CHAR(sal * 10000, 'FM9,999,999')    AS c,
       TO_DATE('20240201', 'YYYYMMDD')        AS d
FROM 사원 WHERE eno = 6;                        -- PostgreSQL
abcd
2024-022024년 02월3,000,0002024-02-01
형식 요소뜻형식 요소뜻
YYYY4자리 연도HH240~23시
MM월(01~12)MI분 (MM 아님)
DD일SS초
DY / DAY요일9, 0숫자 자리 (0 은 빈자리를 0으로)
  • MM(월) 과 MI(분) 혼동이 가장 흔한 실수.
  • 대응: MySQL DATE_FORMAT(d, '%Y-%m') · STR_TO_DATE('20240201', '%Y%m%d'), SQL Server FORMAT·CONVERT, SQLite strftime('%Y-%m', d).

5. NULL 관련 함수

5.1 NULL 은 산술로 전파된다

1편에서 본 규칙의 재확인: NULL 이 낀 산술·연결은 NULL. 그래서 NULL 을 다른 값으로 바꿔 주는 함수가 필요하다.

SELECT ename, bonus, COALESCE(bonus, 0) AS b0,
       sal + bonus AS raw, sal + COALESCE(bonus, 0) AS 총액
FROM 사원 WHERE dno = 20 ORDER BY eno;
enamebonusb0raw총액
최지우5050500500
정민재2020320320
한유나NULL0NULL300

집계 함수에서는 반대로 NULL 이 무시된다. 행 단위 산술(전파)과 집계(무시)가 섞이면 결과가 갈린다.

SELECT SUM(sal + bonus) AS a, SUM(sal) + SUM(bonus) AS b FROM 사원;
ab
12002530
  • a: 행마다 sal + bonus 를 먼저 계산 → 보너스 없는 4행은 NULL → SUM 이 NULL 을 버림 → 보너스 있는 3행만 합산(380 + 500 + 320 = 1200).
  • b: 열별로 먼저 합산(2430 + 100 = 2530).
SELECT AVG(bonus) AS a, AVG(COALESCE(bonus, 0)) AS b, SUM(bonus) AS s, COUNT(bonus) AS c FROM 사원;
absc
33.33…14.28…1003

AVG(bonus) = 100 / 3 (NULL 제외), AVG(COALESCE(bonus, 0)) = 100 / 7. "보너스 평균"이 "받은 사람의 평균"인지 "전 직원 평균"인지에 따라 쓰는 식이 다르다.

5.2 COALESCE — 처음으로 NULL 이 아닌 값

COALESCE(식1, 식2, ..., 식n)    -- 왼쪽부터 보아 처음 나오는 NULL 아닌 값. 전부 NULL 이면 NULL
SELECT ename, COALESCE(bonus, mgr, -1) AS c FROM 사원 WHERE eno IN (1, 2, 3) ORDER BY eno;
enamebonusmgrc이유
김하늘NULLNULL-1둘 다 NULL → 마지막 값
이도윤30130bonus 가 먼저
박서준NULL22bonus NULL → mgr
  • 표준 SQL 이라 모든 주요 엔진에서 동작한다. 인자 개수 제한이 없다.
  • 내부적으로는 CASE WHEN 식1 IS NOT NULL THEN 식1 WHEN 식2 IS NOT NULL THEN 식2 … END 와 같다.
  • 인자들의 타입이 맞아야 한다.
SELECT COALESCE(dno, '미배정') FROM 사원 WHERE eno = 7;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "미배정"
-- SQLite 는 타입이 느슨해 '미배정' 을 그대로 반환

SELECT ename, COALESCE(CAST(dno AS VARCHAR(10)), '미배정') AS 부서
FROM 사원 WHERE eno IN (6, 7) ORDER BY eno;     -- 숫자를 문자로 바꾼 뒤 사용
ename부서
한유나20
오세린미배정

5.3 NULLIF — 같으면 NULL

NULLIF(식1, 식2)    -- 식1 = 식2 이면 NULL, 아니면 식1
SELECT ename, sal, NULLIF(sal, 300) AS n FROM 사원 WHERE dno = 20 ORDER BY eno;
enamesaln
최지우450450
정민재300NULL
한유나300NULL

COALESCE 의 반대 방향(값 → NULL)이다. 대표 용도 두 가지:

  1. 0 나누기 방지 — 100 / NULLIF(분모, 0): 분모가 0 이면 NULL 로 바꿔 오류 대신 NULL 결과. SELECT 100 / NULLIF(0, 0) → NULL (PG 에서 100 / 0 은 ERROR: division by zero).
  2. 특정 값을 집계에서 빼기 — COUNT(NULLIF(job, '사원')) = 3 (사원 4명이 NULL 이 되어 제외, 팀장 2 + 대리 1).

5.4 엔진별 NULL 함수 대응표

기능표준OracleMySQLSQL Server동작
NULL 이면 대체값 (2인자)COALESCE(a, b)NVL(a, b)IFNULL(a, b)ISNULL(a, b)a 가 NULL 이면 b, 아니면 a
NULL 여부로 둘 중 선택CASE WHEN a IS NOT NULL THEN b ELSE c ENDNVL2(a, b, c)——a 가 NULL 아니면 b, NULL 이면 c
여러 개 중 첫 non-NULLCOALESCE(a, b, c, …)COALESCECOALESCECOALESCE
같으면 NULLNULLIF(a, b)NULLIFNULLIFNULLIF
  • NVL2 의 인자 순서가 단골 함정: "NULL 이 아니면 두 번째, NULL 이면 세 번째". NVL2(bonus, '지급', '미지급'). 표준 CASE 로 쓰면:

    SELECT ename, CASE WHEN bonus IS NOT NULL THEN '지급' ELSE '미지급' END AS n2
    FROM 사원 WHERE dno = 10 ORDER BY eno;
    
    enamen2
    김하늘미지급
    이도윤지급
    박서준미지급
  • MySQL 의 ISNULL(x) 는 인자 1개짜리 판정 함수(NULL 이면 1, 아니면 0)다. SQL Server 의 ISNULL(a, b)(대체) 와 이름만 같고 뜻이 다르다.

  • SQLite 는 IFNULL(a, b) 를 지원(실측 IFNULL(NULL 보너스, 0) → 0), PostgreSQL 은 COALESCE 만 있다 (IFNULL 호출 시 function ifnull does not exist).

  • Oracle 은 빈 문자열 '' 을 NULL 로 취급한다. Oracle 에서 LENGTH('') 는 NULL, WHERE x = '' 는 항상 0행. 다른 엔진에서 '' 은 길이 0 인 정상 문자열(SQLite·PG 실측 LENGTH('') = 0, '' IS NULL 은 거짓).


6. CASE 식 — SQL 의 if/else

CASE 는 함수가 아니라 식(expression) 이라 값이 들어갈 수 있는 곳(SELECT·WHERE·ORDER BY·GROUP BY·집계 함수 안) 어디든 쓸 수 있다. 형태는 두 가지다.

6.1 단순 CASE — 하나의 값과 = 비교

CASE 비교식
    WHEN 값1 THEN 결과1
    WHEN 값2 THEN 결과2
    [ELSE 기본결과]
END
SELECT ename, job,
       CASE job WHEN '팀장' THEN 'L' WHEN '대리' THEN 'S' ELSE 'M' END AS 등급
FROM 사원 WHERE dno = 10 ORDER BY eno;
enamejob등급
김하늘팀장L
이도윤대리S
박서준사원M

job = '팀장' 인가, job = '대리' 인가를 차례로 본다. 같다(=) 비교만 할 수 있다(범위 비교 불가).

6.2 검색 CASE — 조건식을 자유롭게

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    [ELSE 기본결과]
END
SELECT ename, sal,
       CASE WHEN sal >= 450 THEN '고' WHEN sal >= 300 THEN '중' ELSE '저' END AS 구간
FROM 사원 ORDER BY eno;
enamesal구간
김하늘500고
이도윤350중
박서준280저
최지우450고
정민재300중
한유나300중
오세린250저

동작 원리 — 위에서부터 첫 번째로 TRUE 인 WHEN 하나만 적용하고 나머지는 보지 않는다(프로그래밍의 if / else if 체인). 그래서 WHEN 의 순서가 결과를 바꾼다.

-- 넓은 조건을 먼저 두면 아래 WHEN 은 영영 선택되지 않는다
SELECT ename, sal,
       CASE WHEN sal >= 300 THEN '중' WHEN sal >= 450 THEN '고' ELSE '저' END AS 구간
FROM 사원 WHERE eno IN (1, 4, 5) ORDER BY eno;
enamesal구간
김하늘500중
최지우450중
정민재300중

500 도 sal >= 300 에서 먼저 걸려 '중'. 범위 구간을 나눌 때는 좁은(큰) 조건부터 쓴다.

6.3 ELSE 를 생략하면 NULL

SELECT ename, CASE WHEN bonus >= 30 THEN '우수' END AS 평가
FROM 사원 WHERE dno = 20 ORDER BY eno;
enamebonus평가
최지우50우수
정민재20NULL
한유나NULLNULL

어떤 WHEN 도 TRUE 가 아니고 ELSE 가 없으면 결과는 NULL. 한유나는 NULL >= 30 이 UNKNOWN 이라 WHEN 에 걸리지 않는다 (CASE 의 WHEN 도 TRUE 만 통과한다 — WHERE 와 같은 규칙).

이 성질을 이용한 것이 조건부 집계다. COUNT 는 NULL 을 세지 않으므로:

SELECT COUNT(CASE WHEN sal >= 300 THEN 1 END)         AS 고,       -- ELSE 없음 → 조건 밖은 NULL → 안 셈
       SUM(CASE WHEN job = '사원' THEN 1 ELSE 0 END)  AS 사원수
FROM 사원;
고사원수
54

(급여 300 이상: 김하늘·이도윤·최지우·정민재·한유나 = 5.) 행을 열로 돌리는 피벗도 이 패턴의 응용이다(3편).

6.4 CASE x WHEN NULL 함정

SELECT ename, bonus,
       CASE bonus WHEN NULL THEN '없음' ELSE '있음' END AS a,     -- 잘못
       CASE WHEN bonus IS NULL THEN '없음' ELSE '있음' END AS b   -- 올바름
FROM 사원 WHERE dno = 10 ORDER BY eno;
enamebonusab
김하늘NULL있음없음
이도윤30있음있음
박서준NULL있음없음

단순 CASE 는 내부적으로 bonus = NULL 을 비교한다. 이것은 항상 UNKNOWN 이므로 WHEN NULL 은 절대 선택되지 않고 모든 행이 ELSE 로 간다. NULL 판정은 반드시 검색 CASE + IS NULL.

6.5 CASE 의 결과 타입은 하나로 맞춘다

SELECT CASE WHEN sal > 400 THEN sal ELSE '낮음' END FROM 사원 WHERE eno = 1;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "낮음"
-- (SQLite 는 통과 — 타입이 느슨하다)

THEN·ELSE 의 결과들은 같은(호환되는) 타입이어야 한다. 숫자와 문자를 섞으려면 CAST(sal AS VARCHAR(10)) 로 맞춘다.

6.6 ORDER BY 에서 CASE — 사용자 정의 정렬 순서

SELECT ename, job FROM 사원 WHERE dno = 10
ORDER BY CASE job WHEN '팀장' THEN 1 WHEN '대리' THEN 2 ELSE 3 END DESC;
enamejob
박서준사원
이도윤대리
김하늘팀장

가나다순이 아닌 "직급 순"으로 정렬할 때 쓴다(DESC 라 3 → 2 → 1).

6.7 Oracle DECODE

Oracle 에는 단순 CASE 의 함수판인 DECODE 가 있다.

DECODE(식, 값1, 결과1, 값2, 결과2, ..., [기본결과])
-- = CASE 식 WHEN 값1 THEN 결과1 WHEN 값2 THEN 결과2 ... ELSE 기본결과 END
DECODE (Oracle)표준 CASE
DECODE(job, '팀장', 'L', '대리', 'S', 'M')CASE job WHEN '팀장' THEN 'L' WHEN '대리' THEN 'S' ELSE 'M' END
DECODE(job, '팀장', 'L') (기본결과 생략)CASE job WHEN '팀장' THEN 'L' END → 나머지 NULL
DECODE(bonus, NULL, '없음', '있음')CASE WHEN bonus IS NULL THEN '없음' ELSE '있음' END
  • 인자 구성은 식 1개 + (값, 결과) 쌍 n개 [+ 기본결과 1개]. 그래서 전체 인자 수가 짝수면 마지막 인자가 기본결과, 홀수면 기본결과 없음(나머지는 NULL). 위 첫 줄은 6개(식 + 쌍 2개 + 기본) → 기본결과 'M'.
  • DECODE 는 NULL 을 NULL 과 같다고 본다 — 마지막 줄처럼 DECODE(bonus, NULL, …) 은 NULL 보너스를 '없음' 으로 잡는다. CASE bonus WHEN NULL 이 실패하는 것(§6.4)과 정반대라서, Oracle 코드를 표준 CASE 로 옮길 때 그대로 옮기면 버그가 된다.
  • DECODE 는 = 비교만 가능 — 범위 조건은 검색 CASE 로.

7. 형 변환 — 묵시적 vs 명시적

7.1 명시적 변환 — CAST

CAST(식 AS 타입)          -- 표준. PostgreSQL 은 식::타입 단축 표기도 가능
SELECT CAST('123' AS INTEGER) + 1     AS a,
       CAST(3.7 AS INTEGER)           AS b,
       CAST(sal AS VARCHAR(10)) || '만원' AS c,
       CAST('2024-02-01' AS DATE)     AS d
FROM 사원 WHERE eno = 6;
엔진abcd
PostgreSQL (실측)1244300만원2024-02-01
SQLite (실측)1243300만원2024
  • 실수 → 정수 변환은 엔진마다 다르다. PostgreSQL·Oracle 은 반올림(PG 실측: 3.7 → 4, -3.7 → -4, 2.5 → 3), SQLite·SQL Server 는 0 쪽으로 버림(SQLite 실측: 3.7 → 3, -3.7 → -3). 확실하게 하려면 ROUND · TRUNC · FLOOR 를 먼저 적용하고 CAST 한다.
  • SQLite 에는 진짜 DATE 타입이 없다. CAST('2024-02-01' AS DATE) 는 숫자로 바꾸려다 앞의 2024 만 남긴다. SQLite 는 날짜를 'YYYY-MM-DD' 문자열로 저장하고 date() 함수로 다룬다.
  • 그 밖의 명시적 변환: Oracle·PG TO_CHAR·TO_DATE·TO_NUMBER, MySQL CONVERT(x, 타입), SQL Server CONVERT(타입, x).

7.2 묵시적 변환 — DBMS 가 알아서 바꾸는 것

타입이 다른 값끼리 연산·비교하면 DBMS 가 한쪽을 자동으로 바꾼다. 편하지만 엔진마다 규칙이 달라 결과·오류가 갈린다.

SELECT '10' + 5 AS a, '10' || 5 AS b;
ab
15105

+ 는 숫자 연산이라 '10' 을 숫자로, || 는 문자 연산이라 5 를 문자로 바꿨다(SQLite·PG 동일). 그런데 조금만 바꾸면 갈린다.

식SQLite (실측)PostgreSQL (실측)MySQLOracle
'10' + 51515 (타입 미지정 리터럴이라 정수로 해석)1515
CAST('10' AS TEXT) + 515오류 (text + integer 연산자 없음)1515
'1abc' + 12 (앞 숫자만)오류2 (경고)오류 (ORA-01722 invalid number)
WHERE dno = '10'매칭됨매칭됨매칭됨매칭됨

PostgreSQL 은 묵시적 변환을 가장 엄격하게 막는 편이고, MySQL·SQLite 는 가장 관대하다.

문자 비교 vs 숫자 비교

SELECT '10' < '9' AS a, 10 < 9 AS b;
ab
참거짓

문자열은 사전순으로 첫 글자부터 비교한다. '1' < '9' 라서 '10' < '9' 가 참. 숫자를 VARCHAR 열에 저장하면 ORDER BY 결과가 1, 10, 2, 20, 3 … 처럼 나오는 이유다.

묵시적 변환의 실무 함정 — 인덱스 무력화. 문자 타입 열 phone 에 WHERE phone = 01012345678(숫자) 로 조건을 걸면, 엔진에 따라 열 쪽을 숫자로 변환해 비교한다(TO_NUMBER(phone) = …). 열에 함수가 씌워지는 셈이라 인덱스를 쓰지 못하고, 숫자로 못 바꾸는 값이 섞여 있으면 오류까지 난다. 조건 값의 타입을 열의 타입에 맞추는 것이 원칙.


확인 문제

1. (결과 쓰기) Oracle 기준 결과 a, b, c 는?

SELECT SUBSTR('INFORMATION', 3, 4) AS a,
       LENGTH(TRIM('  AB C ')) AS b,
       INSTR('INFORMATION', 'O') AS c
FROM DUAL;

2. (결과 쓰기) PostgreSQL 기준 결과 a, b, c, d 는?

SELECT ROUND(156.78, -1) AS a, TRUNC(156.78, 1) AS b, CEIL(-1.2) AS c, MOD(17, 5) AS d;

3. (결과 쓰기) 사원 테이블에서 결과 a, b 는?

SELECT SUM(sal + bonus) AS a, SUM(sal) + SUM(bonus) AS b FROM 사원;

4. (결과 쓰기) 20번 부서 세 사람의 수당 값을 eno 순으로 쓰시오.

SELECT ename,
       CASE WHEN job = '팀장' THEN sal * 0.1
            WHEN bonus IS NOT NULL THEN bonus END AS 수당
FROM 사원 WHERE dno = 20 ORDER BY eno;

5. (OX) CASE bonus WHEN NULL THEN 0 ELSE bonus END 는 보너스가 NULL 인 행에서 0 을 반환한다.

6. (빈칸) Oracle NVL2(bonus, sal + bonus, sal) 를 표준 SQL 로 바꾸시오. CASE WHEN bonus ( ① ) THEN sal + bonus ELSE ( ② ) END

7. (결과 쓰기) 결과 값은?

SELECT COUNT(NULLIF(job, '사원')) FROM 사원;

8. (결과 쓰기) PostgreSQL 과 Oracle 에서 각각 SELECT 7 / 2 의 결과는? (Oracle 은 FROM DUAL)

정답과 풀이

#정답풀이
1a = FORM, b = 4, c = 4I N F O R M … 3번째(F)부터 4글자 = FORM. TRIM 은 양 끝만 지워 'AB C'(가운데 공백 포함 4). 첫 O 는 4번째
2a = 160, b = 156.7, c = -1, d = 2자릿수 -1 은 10의 자리로 반올림(156.78 → 160). TRUNC 는 소수 1자리 아래를 버림. CEIL 은 위(+∞) 쪽이라 -1.2 → -1. 17 = 5×3 + 2
3a = 1200, b = 2530a 는 행마다 더한 뒤 합산 — bonus NULL 인 4행은 NULL 이 되어 제외(380+500+320). b 는 열별 합(2430+100)
445.0 (또는 45), 20, NULL최지우는 팀장 → 450×0.1. 정민재는 첫 WHEN 거짓, 둘째 참 → 20. 한유나는 둘 다 거짓이고 ELSE 가 없어 NULL
5X단순 CASE 는 bonus = NULL 로 비교하므로 항상 UNKNOWN → ELSE 로 가서 NULL(bonus 값) 반환. CASE WHEN bonus IS NULL THEN 0 … 또는 COALESCE(bonus, 0)
6① IS NOT NULL ② salNVL2 는 첫 인자가 NULL 이 아니면 두 번째, NULL 이면 세 번째
73'사원' 4명은 NULLIF 로 NULL 이 되어 COUNT 에서 빠지고 팀장 2 + 대리 1 만 남는다
8PostgreSQL 3, Oracle 3.5PG 는 정수 ÷ 정수를 정수로 자른다. Oracle 의 NUMBER 는 정수·실수 구분이 없어 3.5

한 줄 요약

주제한 줄
단일행 vs 다중행단일행은 행마다 1값(어디든 사용), 다중행(집계)은 그룹마다 1값(WHERE 불가)
LENGTH글자 수가 기본, MySQL 만 바이트 수(글자 수는 CHAR_LENGTH)
SUBSTR위치는 1부터, 길이 생략 시 끝까지, 음수 시작은 Oracle 에서 뒤에서부터(PG 는 다름)
TRIM양 끝만 제거, 가운데 공백은 REPLACE. LTRIM 둘째 인자는 문자 집합
INSTR / POSITION처음 위치(1부터), 없으면 0. 인자 순서가 함수마다 반대
LPAD / RPAD둘째 인자는 결과 전체 길이, 원문이 길면 잘림
ROUND / TRUNC자릿수 양수 = 소수부, 음수 = 정수부(-1 → 10 단위). SQLite 는 음수 자릿수 미지원
TRUNC · FLOOR · CEIL음수에서 갈린다: -2.7 → TRUNC -2 / FLOOR -3 / CEIL -2
MOD부호는 왼쪽(나누어지는 수)을 따름, Oracle 은 % 없음
정수 나눗셈7/2 = PG·SQLite·SQL Server 3, Oracle·MySQL 3.5
날짜날짜 ± n일 = 날짜, 날짜 − 날짜 = 일수, 부분 추출은 EXTRACT, 월 더하기는 엔진별(ADD_MONTHS 등)
NULL 전파산술·연결은 NULL 로 전파, 집계는 NULL 을 무시 → SUM(a+b) ≠ SUM(a)+SUM(b)
COALESCE표준, 첫 non-NULL, 인자 타입 일치 필요
NULL 함수 대응Oracle NVL / MySQL IFNULL / SQL Server ISNULL = COALESCE(a, b). NVL2(a, 아닐때, NULL일때)
NULLIF같으면 NULL — 0 나누기 방지 x / NULLIF(y, 0)
CASE위에서부터 첫 TRUE 하나, ELSE 생략 시 NULL, 결과 타입 통일
CASE x WHEN NULL절대 안 걸림(= 비교) → CASE WHEN x IS NULL. DECODE 는 반대로 NULL 끼리 일치
형 변환CAST 가 표준. 실수→정수는 PG·Oracle 반올림, SQLite·SQL Server 버림. 묵시적 변환은 PG 가 가장 엄격
← SQL ① 기초·WHERE·정렬SQL ③ 집계·GROUP BY →