database - 상관 서브쿼리

상관 서브쿼리는 작은 그룹으로 나누어 비교할 때 사용한다.

상품분류 별로 평균 판매단가보다 높은 상품을 상품분류그룹에서부터 추출하고 싶다.

 

SELECT goods_classify, goods_name, sell_price
FROM Goods
WHERE sell_price > (SELECT AVG(sell_price)
                            FROM Goods
                            GROUP BY goods_classify);

에러 발생 !! 스칼라 서브쿼리는 단일 데이터값을 반환해야 하기 때문이다.

해당 쿼리는 3행 값을 반환하고 있어서 스칼라 서브쿼리로서 역할을 못하고 있다.

WHERE구에서 서브쿼리를 사용할 경우네는 반드시 결과가 1행이여야 한다.

 

해결 방법은?

여기서 등장하는 강력한 도구가 상관 서브쿼리이다.

SELECT goods_classify, goods_name, sell_price
FROM Goods AS S1
WHERE sell_price > (SELECT AVG(sell_price)
                           FROM Goods AS S2
                           WHERE S1.goods_classify = S2.goods_classify -- 이 조건이 핵심
                           GROUP BY goods_classify);

 

핵심 포인트는 서브쿼리 내에 추가한 WHERE구 조건이다.

이 조건을 우리말로 표현하면, '각 상품의 판매단가와 평균 단가 비교를 같은 상품분류 내에서 한다'가 된다.

비교 대상 테이블이 Goods라는 동일 테이블이기 때문에, 구별을 위해서 S1, S2를 별명으로 사용했다.

상관 서브쿼리의 경우 이런 테이블 별명을 열명 앞에 '<테이블명>.<열명>' 형식으로 기술해 줄 필요가 있다.

 

이와 같이 상관 서브쿼리는 테이블 전체가 아닌, 테이블 일부 레코드 집합에 한정된 비교를 하고 싶을 때 사용한다.

따라서 상관 서브쿼리를 사용할 때 묶다, 또는 제한하다 등으로 표현하기도 한다.

이번 예의 경우, 상품 분류별로 묶어서 평균 단가와 비교하고 있다.

 

상품분류가 바뀌면 비교할 평균단가도 바뀐다.

각각의 상품분류별로 판매단가를 계산하고, 이것으로 각 상품테이블의 레코드와 비교하기 때문에 상관서브쿼리가 레코드에 대해 1행만 반환한다고 본다. 이것이 상관서브쿼리가 에러처리 되지 않는 이유이다.

 

상관서브쿼리는 내부동작이 잘 파악되지 않아서 초보자들이 이해하기 어려운 기능으로 유명하지만, 이러한 내부 동작을 도식화해서 따라가면 의외로 간단하다.

 

결합 조건은 반드시 서브쿼리 안에 기술해야한다.

"상관명 스코프" 때문이다.

스코프(scope)란, 생존 범위를 말하는 것. 즉, 상관명 통용 범위에 제한이 있다는 것이다.

서브쿼리 내부에서 부여된 상관명은 해당 서브쿼리 내에서만 사용할 수 있다는 것이다.

즉, '안에서 밖은 볼 수 있어도 밖에서 안은 보이지 않는다.' 라고 할 수 있다.

 

다음 포스팅부터는 드디어 함수, 술어, CASE 식에 대한 정리를 할 것이다!!