데이터베이스 서브쿼리 심화하기

서브쿼리 심화

이미 앞서서 서브 쿼리에 대해서 학습하였다. 이번엔 다른 종류의 서브 쿼리들에 대해서 공부하도록 하겠다. 이는 오라클 9i부터 추가된 고급 활용 기법으로써 복수 칼럼 서브 쿼리, 스칼라 서브쿼리, 상관관계 서브쿼리가 있다.

복수 칼럼 서브쿼리

먼저 살펴볼 서브쿼리는 복수 칼럼 서브쿼리이다. 이는 리턴되는 행들이 두 개 이상의 칼럼을 가지는 경우를 말한다. 그리고 비교 조건에 따라 pairwise 비교와 nonpariwise 비교로 구분된다. 차례대로 학습해보도록 하자.

pairwise

먼저 pairwise 비교이다. 아래 예문을 살펴보면 job과 deptno를 ()로 묶어 동시에 비교문을 수행하고 있다. 이렇게 묶인 한 쌍을 pairwise라고 부른다.

이 때는 7369와 7499 사이의 직원번호를 가진 사원에 대해서 서브 쿼리에서 여러 행 데이터를 출력하는데, 이 조건에 해당하는 직책과 부서번호를 가진 사원 중  사번이 7369와 7499 사이에 있지 않은 사원을 찾는다.

일단 위의 테이블을 살펴보면, 전체 사원들 중에서 7369와 7499 사이에 사번이 존재하는 사원은 두 명이 존재한다. 하지만 부서번호가 20이면서 직책이 clerk인 사원은 존재하지 않는다. 따라서 부서번호가 30이면서 salesman 직책인 3명의 사원만 출력된다.

nonpairwise

다음은 nonpairwise 비교조건이다. pairwise와 달리 이 경우에는 하나의 칼럼에 대해서 비교 조건을 수행한다. 따라서 7369와 7499 사이의 사원 번호를 가진 사원의 직책과 동일한 직책을 가진 사원들에 대해서 두 번째 조건을 수행한다. 즉 pairwise에서는 (부서번호, 직책)이 (20, clerk) 이거나 (30, salesman)이였다면, nonpairwise에서는 부서번호는 20 또는 30이면서 직책은 salesman 또는 clerk인 사원들만 출력한다.

스칼라 서브쿼리

다음은 스칼라 서브 쿼리를 보도록 하겠다. 서브쿼리가 1개의 행에 1개의 칼럼값을 리턴하는 경우를 스칼라 서브쿼리라고 말한다. 스칼라 서브쿼리는 1개의 행과 칼럼만을 가져오기 때문에 케이스 구문에서도 서브쿼리를 사용할 수 있다.

chicago 지역의 부서번호와 동일한 부서 번호에 대해서 지역을 뉴욕으로 변경, 나머진 기타로 변경한다.

케이스뿐만 아니라 Order by 구문에도 서브 쿼리를 적용시킬 수 있다. 이를 통해서 dname으로 정렬하고 있다.

상관관계 서브 쿼리

상관관계 서브 쿼리는 서브 쿼리가 메인 쿼리의 컬럼을 참조하도록 되어 있어 서브 쿼리가 단독으로 실행이 불가능한 경우를 말한다. 이를 위해서 메인 쿼리 처리 -> 서브 쿼리 - > 메인 쿼리 처리 순으로 동작한다. 이는 프로그래밍에서 for문처럼 동작하는 듯 하다.

예시를 살펴보면, 현재의 서브 쿼리에서 e의 deptno를 가져와야 하는데 이는 메인 쿼리에 존재한다. 따라서 먼저 메인 쿼리에서 e를 참조하여 deptno를 가져온다. 그리고 이를 서브쿼리에 넣어 deptno가 같은 경우에 대해서 평균 임금을 검색하도록 한다. 그리고 가져온 평균 임금이 우리가 참조하고 있는 행 데이터의 임금보다 작은지를 확인하고, 조건이 참이되면 이를 출력한다. 그리고 이를 반복문처럼 emp에서 가져오는 모든 행에 대해서 수행한다. 이를 통해서 해당 부서의 평균 임금보다 높은 임금을 받는 사원들에 대해 출력한다.

이번엔 해당 직책의 평균 급여보다 높은 사원에 대한 사원번호, 사원명,  급여, 부서번호를 구해보자.

일단 우리는 emp e로부터 사원번호, 사원명, 급여, 부서번호를 가져온다. 그리고 서브쿼리로 가져온 행 데이터의 job 정보를 보낸다. 이제 조건절에서 급여를 비교하는데 우리가 검색한 행 데이터의 job과 동일한 job에 대해서 평균 급여를 가져오고 이를 해당 행 데이터의 sal과 비교한다. 조건이 참이 되면 해당 행 데이터를 출력한다.

이를 꼭 상관관계 서브쿼리에서 수행해야하는가?

우리가 기존에 사용하던 방법을 살펴보겠다. 서브 쿼리로 해당 직책마다의 평균 임금 정보를 받아오자. 그리고 이를 메인 쿼리에서 sal과 비교하게 해보겠다. 하지만 이 경우 단일 행 쿼리에서 2개 이상의 행 데이터를 반환하기 때문에 문제가 발생한다.

그렇다면 복수 행 서브 쿼리를 통해서 해결해보자. 복수행의 경우에는 다음 예시처럼 가져온 평균 임금 집합에 대해서 all 또는 any를 통해 연산한다. 따라서 이를 통해서 개별 정보에 대해서 처리할 순없다.

즉 그룹함수를 통해서 서브쿼리로 구한 것에 대해서 개개인의 값을 구할 수가 없기 때문에 이러한 상관 관계 쿼리문을 사용한다. 물론 다른 방법이 있을 수 있지만 이에 대해서는 일단 넘어가도록 하자.

Exists

Exists는 서브쿼리의 결과가 한 개의 행이라도 존재할 때 참이 되도록 한다. 하나도 없을 경우에만 거짓이 된다. 그리고 하나의 행이라도 검색되면 다음 행을 검색하지 않고 바로 참을 리턴하기 때문에 검색 속도가 굉장히 빠른 편이다.

간단하게 예문을 통해서 이를 확인해보자. 위와 같이 예문을 작성하였다. 이 때 X는 아무 의미가 없다. 단순하게 return을 받기 위해 둔 변수라고 생각하면 된다.

여기서 mgr에 자신의 사원번호가 존재하는가에 대한 exists문을 작성하고 있다. 한 명이라도 자신의 사원번호를 mgr로 참조하고 있다면 이는 true를 출력할 것이고, 이에 대한 행 데이터를 출력하고 있다.

WITH

With는 쿼리 문장 안에 반복해서 포함되는 서브쿼리를 블록으로 선언하여 별칭을 붙인다. 그리고 해당 쿼리문을 활용할 때 별칭을 사용한다. 일종의 인라인 뷰에 대해서 별칭을 붙이는 것으로 생각해도 무방하다.

게다가 with문에 선언된 서브 쿼리들은 임시 테이블 스페이스에 저장되기 때문에 호출할 때 동작 시간을 향상시키는 효과가 있다.

예문을 하나 살펴보고 넘어가도록 하자.

전체 부서별 총 급여의 평균보다 높은 부서의 총 급여를 검색하는 방법이다. 현재 두 개의 쿼리문을 별칭을 사용하여 저장하고 있다.

dept_sal은 부서별 부서이름과 해당 부서의 사원들 임금 합을 호출한다. 두 번째 쿼리문 dept_avg는 dept_sal을 통해서 각 부서의 총 임금의 평균을 구하고 있다.

이를 통해서 마지막 쿼리문에서 dept_avg에서 구한 평균값보다 사원들의 임금 합계가 큰 부서를 검색한다.