not in 과 null처리 0 4 1,243

by 물통20병 [Oracle 기초] [2017.12.13 10:28:45]


테이블에 특정열에 대해서 null 값이 존재하는 경우 not exists 시에는 true 가 나와서 값을 처리하고 데이터 정상출력

not IN 을 사용하는 경우 null이 존재한다면 FALSE 값이 나와서  결과 값이 나오지 않습니다.

IN은 multiple or 이고 not IN 은 multiple or의 부정인데 왜 NOT IN을 사용할때 null 값이 있는 경우 왜 결과 값이 

나오지 않는건가요?

그 원리가 궁금합니다.

by 우리집아찌 [2017.12.13 10:46:25]
WITH T AS (
SELECT '1' VAL FROM DUAL UNION ALL
SELECT '2' VAL FROM DUAL UNION ALL
SELECT '3' VAL FROM DUAL UNION ALL
SELECT '4' VAL FROM DUAL UNION ALL
SELECT NULL VAL FROM DUAL 
)

-- IN ( '1'  , NULL )
SELECT * FROM T 
 WHERE VAL = '1' 
    OR VAL = NULL     

-- NOT IN ( '1'  , NULL )
SELECT * FROM T 
 WHERE VAL != '1' 
   AND VAL != NULL           

이렇게 풀리는걸로 알고있습니다. NULL 이 '!=' 로 처리되서 안나올겁니다.


by 고수가되고싶어요 [2017.12.13 10:50:20]

<NOT IN과 NOT EXISTS>

IN과 EXISTS는 동일한 결과값을 출력한다.

반면 NOT IN과 NOT EXISTS는 결과값이 다를 수 있다. 바로 NOT IN 은 NULL 값을 포함하지 않기 때문이다.

 

예제)

department_id 가 null인 레코드 1건 존재-->

select employee_id, department_id

from employees

where department_id is null;

1개 recored

 

NOT IN -->

select department_id, employee_id from employees

where  department_id not in (30,60,90)

order by 1, 2;

92개 records

 

NOT EXISTS -->

select emp.department_id, emp.employee_id

from employees emp

where not exists

(select 1

from departments dep

where dep.department_id in (30, 60, 90)

and emp.department_id=dep.department_id)

order by 1, 2;

93개 records

 

※차이가 발생하는 이유

IN은 OR이다.

department_id IN (30, 60, 90) --> department_id = 30 or department_id = 60 or department_id = 90 으로 바꿔 쓸 수 있다.

NOT IN의 경우 (AUB)^C 이므로 A^C교집합B^C으로 쓸 수 있다.

department_id NOT IN (30, 60, 90) --> (a)department_id <> 30 and (b)department_id <> 60 and (c)department_id <> 90 으로 바꿔 쓸 수 있다.

문제는 null 값은 '=', '<>' 연산을 했을때 TRUE/FALSE를 반환하지 않고 UNKNOWN을 반환한다는 것에 있다. 따라서 department_id가 null인 178번 사원은 (a), (b), (c) 어디에도 포함되지 않고, 따라서 반환되지 않는다.

 

반면 EXISTS의 경우 해당 로우의 존재여부만 체크하기 때문에, NOT EXISTS는 department_id 값이 30, 60, 90이 아닌 모든 로우를 조회하게 되며, 따라서 department_id가 null인 값도 출력에 포함되는 것이다.

 

<IN과 EXISTS의 활용>

컬럼값에 대해 비교할 값이 문자나 숫자 등의 상수 리스트를 사용할 경우 IN을 사용하도록 하고, 만약 서브쿼리를 사용한다면 IN보다는 EXISTS를 사용하는 것이 훨씬 성능이 좋다.


by 물통20병 [2017.12.13 11:23:48]

답변 잘보았습니다.

그러면 null의 경우는 =, <> 연산외에 다른 사칙연산(*,/,+,-)의 결과도 unknow으로 나오는건가요?

null 존재하지 않는 값이기 떄문에  연산을 허용하지 않는개념으로 생각하는게 맞는건가요


by 마농 [2017.12.13 11:37:07]

1. 대부분의 연산자, 함수 가 null 포함시 정확한 결과가 나오지 않습니다.
  - 0 + null ---> null
  - 1 <> null ---> False
2. NULL 허용 함수도 종종 있습니다.
  - 'a' || null ---> 'a'
  - decode('', '', 1) ---> 1
3. NULL 전용 연산자나 함수도 있습니다.
  - IS NULL / IS NOT NULL
  - NVL, NVL2, COALSCE

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입