소스: sql-src/mid_06_null_funcs/02_aggregate_sort.sql, 03_dialects.sql. 집계·정렬에서 NULL 의 영향과 DB 별 함수 이름 차이를 확인합니다.
SELECT
COUNT(*) AS cnt_all
, COUNT(mgr) AS cnt_mgr
FROM emp;CNT_ALL | CNT_MGR
--------+--------
5 | 4
(1행)직원은 5명이지만 mgr 이 NULL 인 김대표(최상위 관리자)를 빼면 4명입니다. COUNT(*) 는 행 개수, COUNT(mgr) 은 mgr 이 NULL 이 아닌 행 개수입니다.
SELECT
AVG(mgr) AS avg_ignore_null
, AVG(COALESCE(mgr, 0)) AS avg_treat_zero
FROM emp;AVG_IGNORE_NULL | AVG_TREAT_ZERO
----------------+---------------
1.5 | 1.2
(1행)AVG(mgr) 은 NULL 인 1명을 빼고 나머지 4명의 평균(1.5)이고, AVG(COALESCE(mgr, 0)) 은 NULL 을 0 으로 바꿔 5명 전체로 나눈 평균(1.2)입니다. 같은 컬럼인데 COALESCE 하나로 분모가 달라집니다.
SELECT
id
, name
, mgr
FROM emp
ORDER BY mgr;ID | NAME | MGR
---+--------+-----
1 | 김대표 | NULL
2 | 이팀장 | 1
5 | 정팀장 | 1
3 | 박선임 | 2
4 | 최사원 | 2
(5행)H2 기본 모드는 오름차순에서 NULL 을 맨 앞에 둡니다. MySQL·MSSQL 의 기본값과 같고, Oracle 의 기본값(NULL 이 마지막)과는 다릅니다.
SELECT
id
, name
, mgr
FROM emp
ORDER BY mgr NULLS LAST;ID | NAME | MGR
---+--------+-----
2 | 이팀장 | 1
5 | 정팀장 | 1
3 | 박선임 | 2
4 | 최사원 | 2
1 | 김대표 | NULL
(5행)NULLS LAST 를 붙이면 기본값과 반대로 NULL 이 맨 뒤로 갑니다. H2 는 NULLS FIRST·NULLS LAST 를 기본 모드에서 그대로 지원하고, DESC NULLS LAST 처럼 내림차순과 함께 써도 동작합니다. sql-src/mid_06_null_funcs/02_aggregate_sort.sql 에서 확인해 뒀습니다.
Oracle 도 같은 문법을 지원하지만, MySQL·MSSQL 은 이 문법이 없어 2.5절의 CASE 방식으로 대체합니다.
-- Oracle · Tibero
SET MODE Oracle;
SELECT
id
, mgr
, NVL(mgr, 0) AS nvl_mgr
, NVL2(mgr, 'Y', 'N') AS has_mgr
FROM emp
ORDER BY id;ID | MGR | NVL_MGR | HAS_MGR
---+------+---------+--------
1 | NULL | 0 | N
2 | 1 | 1 | Y
3 | 2 | 2 | Y
(3행)NVL(mgr, 0) 은 COALESCE(mgr, 0) 과 같은 결과입니다. NVL2(mgr, 'Y', 'N') 은 NULL 여부에 따라 완전히 다른 두 값 중 하나를 고르는 함수로, 표준에는 없어 CASE WHEN mgr IS NULL THEN 'N' ELSE 'Y' END 로 풀어 써야 합니다.
SELECT
id
, val
, NVL(val, '(빈값)') AS nvl_val
, CASE WHEN val IS NULL THEN 'Y' ELSE 'N' END AS is_null
FROM ora_test
ORDER BY id;ID | VAL | NVL_VAL | IS_NULL
---+------+---------+--------
1 | NULL | (빈값) | Y
2 | NULL | (빈값) | Y
3 | x | x | N
(3행)빈 문자열 '' 로 넣은 1번 행이 val 컬럼에서 NULL 로 조회됩니다. Oracle 은 빈 문자열을 저장하는 순간 NULL 로 바꾸는 유일한 DB 로, 이 동작은 H2 의 Oracle 모드에서도 그대로 재현됩니다.
-- MySQL
SET MODE MySQL;
SELECT
id
, mgr
, IFNULL(mgr, 0) AS ifnull_mgr
FROM emp
ORDER BY id;ID | MGR | IFNULL_MGR
---+------+-----------
1 | NULL | 0
2 | 1 | 1
3 | 2 | 2
(3행)IFNULL(mgr, 0) 은 COALESCE(mgr, 0) 과 같은 결과입니다. 이어서 MySQL 의 1개짜리 판별 함수 ISNULL(x) 이 H2 에서 되는지 확인합니다.
-- @error
SELECT
id
, mgr
, ISNULL(mgr) AS is_null_flag
FROM emp
ORDER BY id;예상 오류: Function "ISNULL" not foundH2 의 MySQL 모드는 인자 1개짜리 ISNULL 을 지원하지 않아 함수를 찾을 수 없다는 오류가 납니다(H2 미지원). 실제 MySQL 에서는 ISNULL(mgr) 이 mgr 이 NULL 이면 1, 아니면 0을 반환하며, NULL 을 다른 값으로 바꾸는 용도가 아니라 NULL 여부만 확인하는 용도입니다.
-- MSSQL
SET MODE MSSQLServer;
SELECT
id
, mgr
, ISNULL(mgr, 0) AS isnull_mgr
FROM emp
ORDER BY id;ID | MGR | ISNULL_MGR
---+------+-----------
1 | NULL | 0
2 | 1 | 1
3 | 2 | 2
(3행)MSSQL 의 ISNULL(a, b) 는 NVL·IFNULL 과 같은 2인자 대체 함수입니다. code 컬럼을 VARCHAR(3) 으로 만든 테이블로, 두 번째 인자가 더 길 때 결과가 잘리는지 확인합니다.
SELECT
id
, code
, ISNULL(code, '미지정값') AS code_or_default
FROM ms_test
ORDER BY id;ID | CODE | CODE_OR_DEFAULT
---+------+----------------
1 | AB | AB
2 | NULL | 미지정값
(2행)주의code 컬럼은 VARCHAR(3) 이라 실제 MSSQL 이라면 ISNULL(code, '미지정값') 의 결과가 첫 인자 타입(3자)을 따라 "미지"로 잘립니다. H2 는 이 잘림을 재현하지 않고 전체 문자열을 그대로 돌려줘 실제 DB 와 결과가 다릅니다(H2 에서 재현 안 됨, 문법 검토만).
SELECT
id
, code
, COALESCE(code, '미지정값') AS code_or_default
FROM ms_test
ORDER BY id;ID | CODE | CODE_OR_DEFAULT
---+------+----------------
1 | AB | AB
2 | NULL | 미지정값
(2행)COALESCE 는 인자들의 타입 중 더 넓은 쪽으로 결과 타입을 정해, 실제 MSSQL 에서도 잘리지 않고 전체 문자열이 나옵니다. 기본값 대체가 필요할 때 ISNULL 대신 COALESCE 를 쓰면 이 잘림 문제 자체를 피할 수 있습니다.