다중 컬럼과 상관 서브쿼리

#핵심 요약

non pairwise 방식은 컬럼을 각각 비교하고, pairwise 방식은 컬럼들의 조합을 비교함

pairwise: WHERE (sal, NVL(comm, 0)) IN (SELECT sal, NVL(comm, 0) FROM...)
급여와 커미션 조합이 서브쿼리의 한 행과 정확히 같아야 함

non pairwise: sal IN (...) AND NVL(comm, 0) IN (...)
급여가 누군가의 급여와 같고, 커미션이 누군가의 커미션과 같으면 됨
같은 사람일 필요가 없음

상관 서브쿼리는 바깥 문장의 컬럼을 참조하며, 개념상 바깥 행마다 한 번씩 평가됨

#개념과 동작 원리

SALESMAN 중에 월급 1250인 사람과 커미션 300인 사람이 따로 있으면, KING은 non pairwise 결과에는 나오고 pairwise 결과에는 나오지 않음(결과 확인 불가)

설명: MSSQL은 pairwise 방식을 지원하지 않고 non pairwise 방식만 지원함
PostgreSQL은 행 생성자와 서브쿼리의 행 조합 IN 비교를 지원함

중첩 서브쿼리가 한 단계 이상 위 부모 문장의 테이블 컬럼을 참조하면 상관 서브쿼리임
부모 문장은 SELECT, UPDATE, DELETE일 수 있음

#주요 용어와 구성 요소

#필수 명령어 또는 SQL

#pairwise와 non pairwise

-- non pairwise
SELECT ename, sal, job, comm
  FROM emp
 WHERE sal IN (SELECT sal FROM emp WHERE job = 'SALESMAN')
   AND NVL(comm, 0) IN (SELECT NVL(comm, 0) FROM emp WHERE job = 'SALESMAN');

-- pairwise
SELECT ename, sal, job, comm
  FROM emp
 WHERE (sal, NVL(comm, 0)) IN (SELECT sal, NVL(comm, 0)
                                 FROM emp
                                WHERE job = 'SALESMAN');

UPDATE emp
   SET sal = 1250, comm = 300
 WHERE ename = 'KING';
COMMIT;

결과 해석:

실습에서는 위 변경 뒤 두 문장을 다시 실행해 결과를 비교했음
실습 테이블을 바꾸는 문장임
NVL(comm, 0)을 쓴 이유는 커미션이 NULL인 사원끼리도 같은 값으로 비교하려는 것임(NULL은 =로 같지 않으므로)

연습 문제(답 확인 불가): 할인금액이 NULL일 수 있는 주문 테이블에서 VIP(또는 ROCKET) 고객의 (주문금액, 할인금액) 조합과 같은 주문 찾기를 두 방식으로 작성하고 결과 비교하기

#상관 서브쿼리

스크립트는 사원(employee_id, last_name, job_id)과 직무 이력(job_history: 사원, 시작일, 종료일, 직무, 부서)을 만들고, 주석에 "두 번 이상 직무 변경 = 행이 2개 이상", 이력이 한 건뿐인 사원은 "결과에서 제외되어야 함"이라고 적었음

직무 이력이 두 건 이상인 사원을 찾는 상관 서브쿼리는 다음과 같음

SELECT e.employee_id, e.last_name, e.job_id
  FROM emp e
 WHERE 2 <= (SELECT COUNT(*)
               FROM job_history j
              WHERE j.employee_id = e.employee_id);

결과 해석: 바깥 emp 행마다 그 사원의 직무 이력 건수를 세어 2 이상인 사원만 남김

#구문과 옵션 설명

행 조합 IN은 같은 순서의 열끼리 대응함
NVL(comm, 0)은 NULL과 실제 0을 같은 비교값으로 만들므로 업무상 두 값을 구별해야 한다면 그대로 적용할 수 없음
상관 서브쿼리는 개념상 바깥 행마다 평가되지만 옵티마이저의 조인 변환 등으로 물리적인 실행 횟수는 달라질 수 있음

#사용 조건과 주의사항

앞 절의 emp와 직무 이력 절의 emp는 열 구조가 다르므로 같은 테이블로 연속 실행할 수 없음
기존 KING 비교 설명의 실제 행 포함 여부와 SQL Server 지원 주장은 확인 불가