3. 4 한 테이블에서 다른 테이블에 존재하지 않는 값 검색하기

Q. 일부 대상 테이블에는 존재하지 않는 값을, '소스 테이블'이라는 어떤 테이블에서 찾을 수 있다.

예를 들어, 'EMP' 테이블에 없는 'DEPT' 테이블의 부서 정보를 찾으려고 한다. 'EMP' 테이블의 'DEPTNO'를 살펴보면, 40이라는 값은 없는 것을 알 수 있다. 반면 'DEPT' 테이블의 'DEPTNO'에는 40이라는 값을 가진 행이 있는 것을 볼 수 있다.
따라서 'EMP' 테이블에는 없지만, 'DEPT' 테이블에서만 가진 데이터를 출력했을 때 결과셋은 다음과 같다.

	DEPTNO
----------
		40

A. 차집합(set difference)을 수행하는 함수가 있다면 이 문제에 특히 유용하다. DBMS에서 이를 지원하지 않을 때는 서브쿼리를 사용한다.

case: Postgre

EXCEPT를 사용한다

select deptno from dept
except
select deptno from emp

• EXCEPT는 첫 번째 결과셋을 가져와서 두 번째 결과셋과 중복되는 부분을 모두 제거한다
• 여기서는 10, 20, 30, 40을 가져오고 두 번째 결과셋과 중복되는 10, 20, 30을 날려서 최종적으로는 40만 나오게 되는 것이다
  • 사용 제한 / 특징
    • 비교할 데이터 유명 및 값의 개수는 두 SELECT 목록에서 일치해야 한다
    • 중복 항목을 반환하지 않는다
    • NOT IN을 사용하는 서브쿼리와 달리, NULL은 문제되지 않는다
    • 상위 쿼리에서 행을 반환한다

case: Oracle

MINUS를 사용한다

select deptno from dept
minus
select deptno from emp
>>

DEPTNO
40

• MINUS는 Postgre의 EXCEPT와 동일하게 작동한다
  
  

case: Maria

• 서브쿼리를 사용하여 'EMP' 테이블의 모든 'DEPTNO'를 반환한다
• 그 결과셋에 포함되지 않은 행에 대해 'DEPT' 테이블을 검색하는 외부 쿼리를 수행한다

# deptno 칼럼을 출력한다
select deptno
	# DEPT 테이블로 부터
	from dept
    # 단, deptno의 값이 괄호 안의 행 소스에 없어야 한다
    # ( EMP 테이블로부터 deptno 값을 가져온다 )
	where deptno not in (select deptno from emp)
>>
+--------+
| deptno |
+--------+
|     40 |
+--------+

• 서브쿼리는 'EMP' 테이블에서 모든 'DEPTNO'를 반환한다

• 외부쿼리는 서브쿼리에서 반환된 결과셋에 빠져 있거나 포함되지 않은 'DEPT' 테이블의 모든 'DEPTNO'를 반환한다

• 고려해야할 것

  • 중복제거
    • 다른 플랫폼에 쓰이는 EXCEPT와 MINUS는 결과셋에서 중복 행을 제거하여 각 'DEPTNO'가 한 번씩만 나오도록 한다
      (이 예제에선 'DEPT' 테이블의 'DEPTNO' 칼럼이 KEY이기 때문에 어쨌든 한 번만 나오긴 할 것이지만)
    • 만약 KEY가 아닌 칼럼에 대해 이러한 작업을 수행한다면 DISTINCT를 사용하여 'EMP'에는 없는 각 'DEPTNO'의 값이 한 번만 나오도록 할 수 있다

select distinct deptno
   from dept
  where deptno not in (select deptno from emp)

• NOT IN을 사용할 때엔 NULL에 주의한다

테이블을 새로 하나 만들어보겠다.

create table new_dept(deptno integer);

insert into new_dept values(10);

insert into new_dept values(50);

insert into new_dept values(NULL);

select * from new_dept;
>>
+--------+
| deptno |
+--------+
|     10 |
|     50 |
|   NULL |
+--------+

이런 상황에서 'DEPT' 테이블에서 'NEW_DEPT' 테이블에 없는 'DEPTNO' 값을 찾고, NOT IN 서브쿼리를 사용하려고 하면, 쿼리가 행을 반환하지 않는다.

select * from dept
	where deptno not in (select deptno from new_dept)
>>
Empty set (0.001 sec)

이유는 무엇일까?

IN과 NOT IN은 본질적으로 OR연산이기 때문이다.
'NULL'값이 논리 연산 OR의 평가에 처리되는 방식 때문에 빈 결과셋이 나온 것이다.

SQL에서 TRUE or NULL은 TRUE로 평가되지만 FALSE or NULL은 NULL이다

1. SELECT * FROM dept의 결과셋이 준비되고
   WHERE절에서 조건판단을 시작한다

2. SELECT deptno FROM new_dept는 테이블 'NEW_DEPT'의 'DEPTNO'칼럼이 가진 값들을 불러올 것이다

3. 1번의 결과셋 중에서 2번의 결과셋에 없는 것들을 불러온다
   이에 대한 논리 흐름을 살펴본다

/* 1번의 결과셋에서 deptno = 10일 떄 */
DEPTNO = 10 # 1번의 결과셋 중 첫 번째

NOT IN (DEPTNO = 10, DEPTNO = 50, DEPTNO = NULL) # 3번 결과셋이 평가되고 WHERE절 평가시작

# IN은 본질적으로 OR 연산이다 
NOT in (DEPTNO = 10, DEPTNO = 50, DEPTNO = NULL)
= NOT (10=10 or 10=50 or 10=NULL)
= NOT (TRUE or FALSE or NULL)
= NOT (TRUE or NULL)
= NOT TRUE
= FALSE

이런 식으로 평가되기 때문이다.

다른 예로 보겠다.

select deptno 
	from dept
   where deptno in (10, 50, null)
>>
+--------+
| deptno |
+--------+
|     10 |
+--------+

• 'DEPT' 테이블에는 4개의 DEPTNO가 있다(10, 20, 30, 40)
• 이 값들은 각각의 서술어 in 10, in 50 in NULL에 대해 평가된다

/* deptno = 10일 때 */

/* in은 본질적으로 or연산 */
10 in (10, 50, NULL)
= (10=10 or 10=50 or 10=NULL)
= (TRUE or FALSE or NULL)
= (TRUE or NULL)
= TRUE
>> deptno = 10 반환
/* dept테이블의 deptno가 20, 30, 40일 때도 동일하게 반복*/
/* 이후에는 TRUE가 없으므로 반환값은 NULL */

/* 결과적으로 아래의 내용만 반환된다 */
>>
+--------+
| deptno |
+--------+
|     10 |
+--------+

---

A. NOT IN 및 NULL 문제의 방지를 위해 NOT EXISTS와 함께 서브쿼리를 사용한다

외부쿼리의 행이 서브쿼리에서 참조되므로 상관 서브쿼리(correlated subquerry)라는 용어를 사용한다.
다음의 예는 NULL행의 영향을 받지 않는, 대체 해법이다.

# 'DEPT'테이블로부터 'DEPTNO'를 가져올 것이다
select d.deptno
	from dept d
   # 단 'DEPT' 테이블의 'DEPTNO'와 'EMP' 테이블의 'DEPTNO'가 동일한 경우 존재하지 않을 떄(동일한 경우가 아닐 때)
   where not exists(
    select 1
   	 from emp e
    where d.deptno = e.deptno
   )

• 서브쿼리가 결과를 반환했다
  : EXISTS는 참으로 평가 / NOT EXISTS는 거짓으로 평가된다 (서브쿼리가 결과를 반환하면 -> 존재: TRUE이므로)
  이에 따라 외부쿼리에서 고려 중인 행은 삭제된다

• 서브쿼리의 SELECT 목록은 EXSISTS 또는 NOT EXISTS와 쓰일 때 중요하지 않다
  EXISTS 또는 NOT EXISTS에서 무엇이 반환되는지에만 집중하기 때문이다

---

정리

• IN 및 NULL문제의 방지를 위한다는 말이 나왔는데 정확히 어떤 뜻인지 잘 모르겠다
  예제에서 NOT IN의 서브쿼리를 사용했을 때, 쿼리가 아예 행을 반환하지 않았는데 그에 관한 문제가 대표적인 것 같다.

• 서브쿼리에 일일이 칼럼의 값을 적어줄 수는 없으니, EXISTS를 여러번 사용하면서 익혀야 겠다

• FALSE or NULL은 NULL으로 평가된다는 점에 주의해야할 것 같다

• 용어의 뜻은 어려우나 쿼리 구조는 읽기 쉬운 편에 속했다