[Oracle] NULL 처리 방법 (null handling), NULL 과 ''(empty string) 차이점, NULL의 함수 및 연산자 특성
NULL 과 ''(empty string) 에 대한 흥미로운 사실
이번 주제는 오라클에서 NULL을 처리하는 방법에 대해 안내해드리려고 합니다. 먼저 null과 ''의 차이에 대해 알아볼 거예요~ NULL과 '' 비교, 바로 시작해 보도록 하겠습니다.
NULL 값은 보통 '알 수 없음', '값이 없음', '정의되지 않은 값', '알려지지 않은 값' 등을 의미합니다. 그리하여 프로그래밍 언어마다 NULL과 ''(빈 문자열)을 비교할 때에, 어떻게 간주하는지에 대한 차이가 있습니다. 적어도 '' 값은 문자열이 비어있다는 것을 명시하고 있기 때문이랍니다~ 적어도 알 수 없는 값은 아니라는 것이겠지요?
오라클DBMS에서는 NULL 과 '' 을 비슷하게 보는 경향이 있습니다. 경향이 있다는 것은, 동일하게 보지는 않으나 어느 정도 비슷하게 본다는 뜻을 의미하기에 이렇게 표현하였답니다. 이것이 무슨 말인지 안내해드리겠습니다~
null과 empty string의 차이에 대해 알아볼 간단한 자료를 준비해 보았습니다. 아래의 쿼리를 참고해 주세요. 아래에 설명이 또 있으니, 자세하게는 안 보아도 돼요!
SELECT CASE WHEN NULL = NULL THEN 'O' ELSE 'X' END "NULL = NULL",
CASE WHEN NULL = '' THEN 'O' ELSE 'X' END "NULL = ''",
CASE WHEN '' = NULL THEN 'O' ELSE 'X' END "'' = NULL",
CASE WHEN '' = '' THEN 'O' ELSE 'X' END "'' = ''",
CASE WHEN NULL IS NULL THEN 'O' ELSE 'X' END "NULL IS NULL",
CASE WHEN '' IS NULL THEN 'O' ELSE 'X' END "'' IS NULL"
FROM DUAL;
NU NU '' '' NU ''
-- -- -- -- -- --
X X X X O O- NULL = NULL => 거짓
- NULL = '' => 거짓
- '' = NULL => 거짓
- '' = '' => 거짓
- NULL IS NULL => 참
- '' IS NULL => 참
NULL 과 '' 를 CASE 구문으로 비교해보았습니다. NULL을 비교할 때는 연산자 IS 를 사용한다는 것, 잊지 마세요~ "NULL IS NULL"과 "'' IS NULL" 은 오라클에서 참인 것을 확인할 수 있습니다.
이번에는 CASE가 아니라 DECODE 함수로 NULL과 ''를 비교해 보겠습니다~
SELECT CASE WHEN '' = '' THEN 'O' ELSE 'X' END "'' = ''",
DECODE ('', '', 'O', 'X') "DECODE ('', '', 'O', 'X')",
DECODE (NULL, NULL, 'O', 'X') "DECODE (NULL, NULL, 'O', 'X')",
DECODE ('', NULL, 'O', 'X') "DECODE ('', NULL, 'O', 'X')",
DECODE (NULL, '', 'O', 'X') "DECODE (NULL, '', 'O', 'X')"
FROM DUAL;
'' DE DE DE DE
-- -- -- -- --
X O O O O오라클에서 '' 는 NULL의 속성이 있어서 '' = '' 비교는 불가하지만, DECODE 함수를 통한 비교는 가능합니다. 디코드 내부에 IS 연산으로 NULL여부를 검증하는 부분이 있는 것으로 보입니다.
참고로 ''이 NULL은 아니기 때문에 " NULL IS '' "과 같은 조건으로 비교하는 것은 불가합니다. 비교하는 경우에는 'ORA-00908: missing NULL keyword' 오류가 발생합니다.
SELECT CASE WHEN NULL IS '' THEN 'O' ELSE 'X' END FROM DUAL;
ERROR at line 1:
ORA-00908: missing NULL keywordSELECT CASE WHEN '' IS '' THEN 'O' ELSE 'X' END FROM DUAL;
ERROR at line 1:
ORA-00908: missing NULL keyword디코드 함수를 사용하면 다음과 같은 사항을 점검할 수 있으며, 이 정도는 알고 가면 나중에 도움이 많이 될 거예요~
- NULL을 비교할 때에, 'column_name = NULL', "column_name = ''"을 사용하는 것은 예상하지 않을 결과를 가져올 수 있습니다.
- DECODE 에서 DECODE('', '') 를 통해 '' 과 '' 를 비교할 수 있습니다.
함수에서 NULL 처리 방법
이제 함수와 NULL의 관계에 대해 안내해드리겠습니다. 살펴보시죠~
- CONCAT : 문자열 연결입니다. NULL 을 포함하여도 문제 없습니다.
- || : 문자를 연결합니다. NULL 을 포함하여도 문제 없습니다.
- REPLACE : 문자를 치환합니다. NULL 을 치환하여도 무관합니다.
- TRIM : 공란을 제거하면 NULL 이 됩니다.
- GREATEST : 매개변수에서 가장 큰 값을 반환합니다. NULL 을 포함하는 경우 의도치 않은 결과가 나올 수 있습니다.
- LEAST : 매개변수에서 가장 작은 값을 반환합니다. NULL 을 포함하는 경우 의도치 않은 결과가 나올 수 있습니다.
- 'Number type Column' + NULL : +, -, *, / 등의 산술 연산은 계산이 불가하기 때문에 NULL을 반환합니다.
- DECODE : 아래에 한 번 더 다루도록 하겠습니다.
SELECT CONCAT (NULL, 'ABC'),
CONCAT ('ABC', NULL),
'ABC' || NULL,
NULL || 'ABC' || NULL,
REPLACE ('ABC', NULL, NULL),
TRIM (' '),
GREATEST (1, 2, 3, NULL),
LEAST (1, 2, 3, NULL),
1 + NULL
FROM DUAL;
CONCAT CONCAT 'ABC'| NULL|| REPLAC T GREATEST(1,2,3,NULL) LEAST(1,2,3,NULL) 1+NULL
------ ------ ------ ------ ------ - -------------------- ----------------- ----------
ABC ABC ABC ABC ABCDECODE 함수를 통해 NULL을 비교한 결과를 안내하겠습니다! 아래의 값은 모두 동일하게 'NULL' 값을 반환할 것입니다.
SELECT DECODE (NULL, NULL, 'NULL', 'NOT NULL') FROM DUAL;
DECODE(N
--------
NULL
SELECT DECODE (NULL, '', 'NULL', 'NOT NULL') FROM DUAL;
DECODE(N
--------
NULL
SELECT DECODE ('', NULL, 'NULL', 'NOT NULL') FROM DUAL;
DECODE('
--------
NULL
SELECT DECODE ('', '', 'NULL', 'NOT NULL') FROM DUAL;
DECODE('
--------
NULLNULL COUNT
NULL 과 COUNT 함수를 비교해보겠습니다. 집계함수(COUNT, SUM, AVG, MIN, MAX 등)는 NULL을 제외하고 연산합니다. 단, COUNT(*)는 예외적으로 NULL을 포함한 모든 로우(레코드) 개수를 카운트 합니다~ 물론 COUNT에 NOT NULL인 컬럼을 매개로 하면 다른 함수처럼 NULL을 제외하여 처리합니다. 간단한 예제를 준비해 보았습니다.
SELECT COUNT (*), COUNT (NY), COUNT (NN)
FROM (SELECT NULL NY, 'NOT NULL' NN FROM DUAL
UNION ALL
SELECT NULL, NULL FROM DUAL
UNION ALL
SELECT NULL, 'NOT NULL' FROM DUAL);
COUNT(*) COUNT(NY) COUNT(NN)
---------- ---------- ----------
3 0 2위의 예제는 총 3개의 로우가 있으며, 첫 번째 컬럼은 모든 레코드가 NULL, 두 번째 컬럼은 NULL이 1개, NOT NULL이 2개입니다. 그리하여 COUNT함수는 위와 같은 결과를 보여준답니다.
NULL 처리 방법 (operator nulls handling)
연산자 사용 시 NULL 처리 유의사항에 대해 안내해드리겠습니다. 연산자 사용 시 NULL 과 관련한 주의사항에 대해 알아보도록 합시다. 우선 다음의 예제 테이블을 확인해 주세요.
테스트 테이블은 EMP이며, [COMM]컬럼을 주목해 주세요. COMM 컬럼이 300, 500, 1400, 0, 그리고 NULL 로 구성되어 있습니다. 조건 연산자는 이 값을 어떻게 처리하는지 알고 있나요? 부정 연산자를 중심으로 안내해드리겠습니다!
<> 은 '같지 않음'을 나타내는 비교 연산자입니다. 그리고 '0 과 같지 않은 조건'은 NULL값을 제외합니다. NULL은 0과 다른 값이기 때문이며 NULL은 알 수 없는 값이기에 비교 연산의 대상이 아니기 때문입니다.
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM <> 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
!= 연산자 또한 '같지 않음'을 나타냅니다. 해당 조건은 NULL 값을 비교대상에서 제외합니다.
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM != 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
^= 연산자 또한 [같지 않음] 을 나타냅니다. 해당 조건은 NULL 값을 비교대상에서 제외합니다.
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM ^= 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
NOT IN 연산자와 같은 부정 연산자는 NULL 값을 제외합니다.
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM NOT IN (0);
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
>, <, >=, <= 비교연산자 또한 NULL 값을 제외합니다.
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM > 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM < 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM >= 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM <= 0;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
'NOT ='을 보면 NULL을 제외하고 0이 아닌 값을 조회합니다. 당연히 NULL은 0이 아니기 때문입니다~
'IN 0'는 이퀄사인과 마찬가지로 긍정 연산이기에 0인 값만 조회합니다. 이퀄 연산과 마찬가지로 NULL을 제외합니다.
between 연산자는 NULL을 어떻게 처리할까요? 범위를 지정하는 연산 또한 NULL을 제외합니다! 'BETWEEN 0 AND 1400'은 NULL을 제외한 0과 1400 사이의 값을 조회한 결과를 보여드릴 것입니다.
NOT BETWEEN 연산도 부정 연산이기에 NULL을 제외합니다.
'NOT comm <> 0'을 해석하면 '0이 아닌 게 아닐 때'라고 볼 수 있습니다. 이 연산도 NULL을 제외합니다.
참고로 comm = comm 조건 또한 NULL을 제외하는 것을 알고 있나요? 이 부분을 잘 모르는 사람이 의외로 많으니 기억하고 가세요~ 조건절을 작성할 때에 comm과 같은 Nullable Column은 'comm = comm'이 항상 참일 수는 없습니다. (물론 기본키나 NOT NULL 제약조건이 있다면 참일 것입니다.)
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND COMM = COMM;
EMPNO ENAME COMM
---------- -------------------- ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
7844 TURNER 0키값(EMPNO)을 기준으로 하면 다음과 같이 NULL을 고민할 필요는 없습니다~
SELECT EMPNO, ENAME, COMM
FROM EMP
WHERE 0 = 0
AND EMPNO = EMPNO;
EMPNO ENAME COMM
---------- -------------------- ----------
7698 BLAKE
7782 CLARK
7566 JONES
7788 SCOTT
7902 FORD
7369 SMITH
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
7844 TURNER 0
7876 ADAMS
7900 JAMES
7934 MILLER
7839 KING
14 rows selected.다음은 NULL 처리 방법과 관련한 예제 쿼리입니다.
SELECT *
FROM EMP
WHERE 0 = 0
-- AND COMM <> 0
-- AND COMM != 0
-- AND COMM ^= 0
-- AND COMM NOT IN (0)
-- AND COMM > 0
-- AND COMM < 0
-- AND COMM >= 0
-- AND COMM <= 0
-- AND NOT COMM = 0
-- AND NOT COMM IN (0)
-- AND COMM BETWEEN 0 AND 1400
-- AND NOT COMM BETWEEN 0 AND 1400
-- AND NOT COMM <> 0
-- AND COMM = COMM;Null = Null? Null is Null? (Oracle)
오라클 데이터베이스에서 NULL에 대해 다시 정리해보겠습니다. 이번 주제의 핵심은 NULL은 NULL인가에 대한 문제랍니다~ 오라클에서 Null은 Null일까요? 널 값에 대해 알아보도록 합시다.
- Null = Null? Null is Null? (Oracle)
- Nullable
Null
Null은 알려지지 않은, 알 수 없는 값(unknown)을 의미합니다. 그리하여 Null = Null 은 거짓입니다. 값 자체를 알 수 없기에 비교대상이 되지 않기 때문입니다.
Oracle Null
오라클은 ''(empty string)을 Null 값으로 간주하는 경향이 있습니다. 경향이 있다는 것은 " NULL = '' "이라는 공식이 항상 참은 아니라는 뜻이랍니다.
- '' is Null 은 참입니다.
- '' = '' 은 거짓입니다.
- '' = Null 은 거짓입니다.
- Null = Null 은 거짓입니다.
오라클은 Null 여부를 비교하기 위해 Is Null 및 Is Not Null 연산을 제공합니다.
DECODE와 CASE 같이 인자 값의 Null여부를 확인하는 함수도 있습니다. DECODE(NULL, NULL)은 참, CASE WHEN NULL IS NULL은 참, CASE NULL WHEN NULL은 거짓입니다. 2가지 케이스 표현식에 관한 내용은 여기를 참고해 주세요~
SELECT DECODE (NULL, NULL, 'TRUE', 'FALSE') "DECODE NULL NULL",
DECODE (NULL, '', 'TRUE', 'FALSE') "DECODE NULL ''",
DECODE ('', NULL, 'TRUE', 'FALSE') "DECODE '' NULL",
DECODE ('', '', 'TRUE', 'FALSE') "DECODE '' ''",
CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END "CASE WHEN NULL = NULL",
CASE WHEN NULL = '' THEN 'TRUE' ELSE 'FALSE' END "CASE WHEN NULL = ''",
CASE WHEN '' = NULL THEN 'TRUE' ELSE 'FALSE' END "CASE WHEN '' = NULL",
CASE WHEN '' = '' THEN 'TRUE' ELSE 'FALSE' END "CASE WHEN '' = ''",
CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END "CASE WHEN NULL IS NULL",
CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END "CASE WHEN '' = NULL",
CASE NULL WHEN NULL THEN 'TRUE' ELSE 'FALSE' END "CASE NULL WHEN NULL",
CASE NULL WHEN '' THEN 'TRUE' ELSE 'FALSE' END "CASE NULL WHEN ''"
FROM DUAL;
DECODE N DECODE N DECODE ' DECODE ' CASE WHEN CASE WHEN CASE WHEN CASE WHEN CASE WHEN CASE WHEN CASE NULL CASE NULL
-------- -------- -------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSENullable
'able'이 '할 수 있는'을 뜻하듯, Nullable은 Null일 수 있는 값을 뜻합니다. 널러블에 대해 알아보도록 합시다.
Null일 수 있는 칼럼은 비교 시 주의가 필요합니다.
상기와 같이 14개의 행으로 이루어진 테이블이 있습니다.
조회조건을 추가하였을 때 결과가 어떻게 달라지는지 확인해보겠습니다.
WHERE 0 = 0 (Always True)
WHERE 0 = 0 은 항상 참인 조건입니다. 그리하여 조건을 추가하여 조회해도 결과에 변화는 없습니다.
SELECT * FROM EMP
WHERE 0 = 0;WHERE comm = comm (Nullable Column)
그렇다면 WHERE comm = comm 을 조건으로 조회하면 값이 어떻게 나올까요?
SELECT * FROM EMP
WHERE COMM = COMM;결과값이 14개가 나올까요?
그렇지 않습니다. [COMM] 컬럼은 Null일 수가 있으므로 Null인 경우 비교대상에서 제외가 되기 때문입니다. 그리하여 값이 있는 4개의 행만 조회가 됩니다. 컬럼이 Nullable인 경우, WHERE column_name = column_name 이 항상 참값을 보장하지는 않습니다. 물론 기본키에 해당하거나, Not Null 제약조건이 있다면 참값을 보장할 것입니다.
댓글