부속질의

부속질의

---

부속질의 (subquery)

하나의 SQL 문 안에 다른 SQL 문이 중첩된(nested) 질의
다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용한다.
 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋다.
주질의(main query, 외부질의)와 부속질의(sub query, 내부질의)로 구성된다.

 

<예시>

SELECT SUM(saleprice) 총구매액  FROM orders  WHERE custid = (SELECT custid                                      FROM customer                                      WHERE name = '박지성');

주질의        부속질의

 

---

부속질의의 종류

명칭	위치	영문 및 동의어	설명
스칼라 부속질의	SELECT절	scalar subquery	SELECT 절에서 사용되며 단일 값을 반환  '스칼라 부속질의'라고 함.
인라인 뷰	FROM절	inline view,  table subquery	FROM 절에서 결과를 뷰(view) 형태로 반환 '인라인 뷰'라고 함.
중첩질의	WHERE절	nested subquery,  predicate subquery	WHERE 절에 술어와 같이 사용되며 결과를 한정시키기 위해 사용. 상관 혹은 비 상관 형태.

 

---

 

■ 스칼라 부속질의 (Scala Subquery)

 

: SELECT 절에서 사용되는 부속질의

부속질의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환

원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능하며, 일반적으로 SELECT 문과 UPDATE SET 절에 사용됨
주질의와 부속질의와의 관계는 상관/비상관 모두 가능하다.

SELECT custid, (SELECT name                             FROM customer cs                                    WHERE cs.custid = od.custid) 이름                               , SUM(saleprice) 구매총액  FROM orders od  GROUP BY custid;

스칼라 부속질의

---

 

[스칼라 부속질의] 연습문제

---

 

질의 4-12.   마당서점의 고객별 판매액을 보이시오(고객이름과 고객별 판매액을 출력)

SELECT (SELECT name
	FROM customer cs
        WHERE cs.custid = od.custid) 이름
         ,SUM(saleprice) 총구매액
 FROM orders od
 GROUP BY od.custid;

<쿼리풀이>

 고객(name)별 판매액을 뽑기 위해 제공되는 정보는

customer 테이블의 custid(고객ID), name(고객명)

orders 테이블의 custid(고객ID), saleprice(구매액)

이므로 이 두 테이블의 공통 컬럼을 통해 조인해서 name ↔ custid  ↔  saleprice 간 매칭이 가능하다.

 

 

 

질의 4-12.   Orders 테이블에 각 주문에 맞는 도서이름을 입력하시오.

UPDATE orders	
SET bookname = (SELECT bookname
                 FROM book
                 WHERE book.bookid = orders.bookid);

---

 

■ 인라인 뷰(Inline View) 

: FROM 절에서 사용되는 부속질의
 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있다.
 부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없다.
 가상 테이블인 뷰 형태로 제공되므로 상관 부속질의로 사용될 수는 없다.

---

연습문제

---

질의 4-14.   고객번호가 2 이하인 고객의 판매액을 보이시오(고객이름과 고객별 판매액 출력)

SELECT name 고객이름
	   , SUM(saleprice) 고객판매액
 FROM (SELECT custid, name
        FROM customer
        WHERE custid <= 2) cs,
        orders od
 WHERE cs.custid = od.custid
 GROUP BY cs.name;

<쿼리풀이>

 고객이름과 고객별 판매액을 출력해야 하는데

customer 테이블 컬럼은 custid(고객ID), name(고객명)

orders 테이블 컬럼은 custid(고객ID), saleprie(판매액)

이므로 두 테이블의 공통 컬럼 custid를 기준으로 조인하는데 *그 전에 고객정보에서 문제에서 원하는 조건인 고객번호 2이하인 고객의 custid, name만을 추출해서 조건을 충족하는 인라인 뷰 cs를 생성한 후, cs와 od(orders) 테이블을 조인해주고

고객별로 그룹핑하여 판매액을 출력한다.

---

 

■ 중첩질의 (Nested subquery)

WHERE 절에서 사용되는 부속질의
WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어(predicate)와 같이 사용된다.

(→ 중첩질의를 술어 부속질의(predicate subquery)라고도 함)

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

 

1) 비교 연산자   =, <>, <, <=, >, >=

부속질의가 반드시 단일 행, 단일 열을 반환해야 하며, 아닌 경우 질의를 처리할 수 없다.

---

연습문제

---

질의 4-15.  평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

SELECT orderid, saleprice
 FROM orders
 WHERE saleprice <= (SELECT AVG(saleprice)
                      FROM orders);

 

질의 4-16.   각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서 주문번호, 고객번호, 금액을 보이시오.

SELECT orderid, custid, saleprice
 FROM orders od1
 WHERE saleprice > (SELECT AVG(saleprice)
					 FROM orders od2
					 WHERE od1.custid = od2.custid);

<쿼리풀이>

문제에서 주어진 조건은 '각 고객의 평균 주문금액보다 큰 금액의 주문 내역에 대해서' 

 전체 주문에 대한 평균 주문금액이 아닌 '각 고객의 평균 주문금액'보다 큰 이므로 고객별 평균 주문금액을 구하기 위해 주질의의 조건절에서 saleprice와 비교할 기준의 평균값을 추출하는 범위를 각 고객별로 맞추어주어야 한다.

 

 

2) 집합 연산자  IN, NOT IN

 IN 연산자는 주질의 속성 값이 부속질의에서 제공한 결과의 집합에 있는지 확인하는 역할
 IN 연산자는 부속질의의 결과 다중 행을 가질 수 있다.

 주질의는 WHERE 절에 사용되는 속성 값을 부속질의의 결과 집합과 비교해 하나라도 있으면 참이 된다.
 (NOT IN은 이와 반대로 값이 존재하지 않으면 참)

---

연습문제

---

질의  4-17 .  대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice) 총판매액
 FROM orders
 WHERE custid IN (SELECT custid 
			       FROM customer 
				   WHERE address LIKE '%대한민국%');

 

 

3) 한정 연산자  ALL, SOME(ANY)

ALL은 모두

SOME(ANY)은 어떠한(최소한 하나라도)이라는 의미

---

연습문제

---

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

SELECT orderid, saleprice
 FROM orders
 WHERE saleprice > ALL (SELECT saleprice
                        FROM orders
                        WHERE custid = 3);

 

<쿼리풀이>

 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 <- 즉, 3번 고객이 주문한 모든 도서의 금액들 보다 비싼

 

 

 

4) 존재 연산자  EXISTS, NOT EXISTS

데이터의 존재 유무를 확인하는 연산자
주질의에서 부속질의로 제공된 속성의 값을 가지고

부속질의에 조건을 만족하여 값이 존재하면 참이 되고, 주질의는 해당 행의 데이터를 출력함

 

WHERE EXISTS | NOT EXISTS ~

 

---

연습문제

---

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

SELECT SUM(saleprice) 총판매액
 FROM orders od
 WHERE EXISTS (SELECT *
				FROM customer cs
                WHERE address LIKE '%대한민국%'
                AND cs.custid = od.custid);

 

<조인 해주지 않았을 때 나타나는 오류>

SELECT SUM(saleprice) 총판매액
 FROM orders od
 WHERE EXISTS (SELECT *
                FROM customer cs
                WHERE address LIKE '%대한민국%');

 

<쿼리풀이>

customer 테이블의 custid와 orders 테이블의 custid 조인O	customer 테이블의 custid와 orders 테이블의 custid 조인X
SELECT *  FROM orders od  WHERE EXISTS (SELECT * FROM customer cs                 WHERE address LIKE '%대한민국%'                 AND cs.custid = od.custid);	SELECT *  FROM orders od  WHERE EXISTS (SELECT * FROM customer cs                 WHERE address LIKE '%대한민국%');

-> custid에 대한 조인을 해주지 않으면

 

---

상관 부속질의 (correlated subquery)

상위 부속질의의 투플을 이용하여 하위 부속질의를 계산함.

즉 상위 부속질의와 하위 부속질의가 독립적이지 않고 관련을 맺고있음.