[DB] 부속 질의(subquery)

🎈 부속 질의(subquery)

하나의 SQL문 안에 다른 SQL문이 중첩 된 질의를 말한다.

 

🎈 위치에 따른 부속 질의의 종류

위치에 따라 부속 질의의 형태가 다르다. 

명칭	위치	영문 및 동의어
스칼라 부속 질의	SELECT	scalar subquery
인라인 뷰	FROM	inline view / table subquery
중첩 질의	WHERE	nested subquery / predicate subquery

 

🎈  주 질의(main query)와 부속 질의(sub query) 간의 상관관계

👉 상관 부속 질의

주 질의의 내용을 참조해서 부속 질의에 적용할 때 상관 부속 질의이다. 

주 질의에서 먼저 수행되어서 결과를 가지고 부속 질의의 내용을 적용하는 것이다.

왜 이렇게 하냐면 결과를 저장할 수 있는 기능이 없기 때문이다. 

 

👉  비상관 부속 질의

서로 독립적인 경우, 부속 질의 결과를 가지고 주 질의를 수행하는 경우이다.

 

🎈  스칼라 부속 질의 (SELECT)

부속질의의 결과 값을 단일 행(스칼라), 단일 열의 벡터 값으로 반환한다. 단일행(스칼라, 단일 값)이거나 단일 열(여러 행)로 결과가 나와야한다.

상관관계: 상관/비상관 모두 가능하다.

 

예제: 마당서점의 고객별 판매액을 보이시오. (결과는 고객이름과 고객별 판매액을 출력) = 상관 질의 형태

1번과 같이 부속 질의를 이용하거나 2번과 같이 조인을 사용하면 정답을 출력할 수 있다.하지만 1번처럼 부속 질의를 이용하면 2번보다 속도가 빠르게 결과를 확인할 수 있다.

-- 1.
SELECT (SELECT NAME 
        FROM CUSTOMER CS 
        WHERE CS.CUSTID = OD.CUSTID) "NAME", SUM(SALEPRICE) "TOTAL"
FROM ORDERS OD
GROUP BY OD.CUSTID;

-- 2.
SELECT NAME, SUM(SALEPRICE)
FROM ORDERS OD, CUSTOMER CS
WHERE OD.CUSTID = CS.CUSTID
GROUP BY NAME;

 

🎈  인라인 뷰 (FROM)

FROM절에는 테이블만 작성할 수 있다. 그렇기 때문에 데이터는 다중 행, 다중 열이어도 상관없다. 2차원 테이블이면 된다. 단일행, 단일 열은 불가능하다. 부속 질의의 결과를 사용해 주 질의를 수행한다.

상관관계: 비상관만 가능하다. 가상의 테이블인 뷰 형태로 제공되니 상관 부속 질의는 사용할 수 없다.

 

예제: 고객번호가 2 이하인 고객의 판매액을 보이시오. (결과는 고객이름과 고객별 판매액 출력)

SELECT CS.NAME, SUM(OD.SALEPRICE) "TOTAL"
FROM (SELECT CUSTID, NAME
      FROM CUSTOMER
      WHERE CUSTID <=2) CS, ORDERS OD
WHERE CS.CUSTID = OD.CUSTID
GROUP BY CS.NAME;

변수를 저장하는 기능이 없기 때문에 앞에 수행한 결과를 저장할 수 없으니 부속 질의를 사용해 순차적 실행을 수행하게 되는 것이다. 

 

🎈  중첩 질의 (WHERE)

WHERE절은 조건을 명시하기 위해 사용한다. 따라서 보통 데이터를 선택하는 조건 술어와 함께 사용된다. 그래서 술어 부속 질의라고 부르기도 한다.

술어	연산자	반환 행	반환 열	상관
비교	=, ＞, ＜, ＞=, ＜=, ＜＞	단일	단일	가능
집합	IN, NOT IN	다중	단일	가능
한정	ALL, SOME(ANY)	다중	단일	가능
존재	EXISTS, NOT EXISTS	다중	다중	필수

 

1) 비교 

부속 질의가 반드시 단일행, 단일 열을 반환해야한다. 그래야 비교를 할 수 있기 때문이다.

 

예제: 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

예제: 각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오.

-- 1. 비상관 서브 질의
SELECT ORDERID, SALEPRICE
FROM ORDERS
WHERE SALEPRICE <= (SELECT AVG(SALEPRICE)
                    FROM ORDERS);
-- 2. 상관 서브 질의
SELECT ORDERID, CUSTID, SALEPRICE
FROM ORDERS OD
WHERE SALEPRICE > (SELECT AVG(SALEPRICE)
                   FROM ORDERS SOD
                   WHERE OD.CUSTID = SOD.CUSTID);

 

2)  집합: IN, NOT IN

IN(OR연산)은 부속 질의의 결과 집합에 주 질의 값이 있는지 확인하는 역할이다(집합연산). 다중 행을 가질 수 있다. NOT IN은 값이 존재하지 않으면 참이된다.

 

예제: 대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

SELECT SUM(SALEPRICE) "TOTAL"
FROM ORDERS
WHERE CUSTID IN (SELECT CUSTID
                 FROM CUSTOMER
                 WHERE ADDRESS LIKE '%대한민국%');

 

3) 한정: ALL, SOME(ANY)

ALL은 모두(AND 연산을 이어서 쓸 때 유용하다.), SOME은 하나라도 참이면 된다.

 

예제: 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

SELECT ORDERID, SALEPRICE
FROM ORDERS
WHERE SALEPRICE > ALL (SELECT SALEPRICE 
			FROM ORDERS 
			WHERE CUSTID = 3);

 

4) 존재: EXISTS, NOT EXISTS

데이터의 존재 유무 확인한다. IN과 헷갈릴 수 있지만 EXISTS는 앞에 변량, 대상자가 없다(문법적으로). 그리고 값을 확인하지않고 존재 유무만 확인한다. FROM절의 전체 대상자가 부속 질의와 비교해서 존재하는지 확인하는 것이기 때문에 100% 상관 질의만 가능하다.

 

예제: EXISTS 연산자로 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하시오.

SELECT SUM(SALEPRICE) "TOTAL"
FROM ORDERS OD
WHERE EXISTS (SELECT *
              FROM CUSTOMER CS
              WHERE ADDRESS LIKE '%대한민국%' AND CS.CUSTID = OD.CUSTID);