[MySQL] SubQuery

📌 SubQuery (서브쿼리)

• 하위질의(부속질의)
• 하나의 SQL문 안에 SQL문이 중첩(nested)되어 있는 구조
• 질의를 1차 수행한 다음 반환 결과를 기초로 다음 질의를 수행
• 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할때 사용

서브쿼리 연산자

• WHERE 절에서 사용하는 연산자

• 데이터를 선택하는 조건이나 술어 같이 사용

• 연산자 종류

  1) 비교: =, !=, >, <, >=, <=
  

  • 단일행으로 반환

  2) 집합: IN, NOT IN
  

  • 다중행으로 반환

  3) 존재: EXISTS, NOT EXISTS
  

  • 다중행으로 반환

  4) 한정: ALL(모두), ANY(최소 하나라도)
  

  • 다중행으로 반환

---

0️⃣ 사용 데이터

book	bookclient

booksale	publisher

---

1️⃣ 단일 행 서브쿼리

• 서브쿼리의 결과 값이 단일 행(튜플)인 경우
• '=' 연산자 사용
• =, !=, >, <, >=, <=

main query
	WHERE 조건절 = (sub query)

Ex) '호날두' 고객의 총 주문량 조회

• booksale 테이블에는 고객이름(clientName) 없이 고객번호(clientNo) 만 있기에 bookclient에서 '호날두' 고객이름에 해당하는 clientNo을 찾아 WHERE 절에서 booksale의 '호날두' clientNo에 해당하는 데이터를 출력하면 된다.

-- 호날두 고객의 총주문량 조회
SELECT sum(BS.bsQty) AS '총주문량'
	FROM bookdb.booksale BS
    WHERE BS.clientNo = (SELECT BC.clientNo
							FROM bookdb.bookclient BC
                            WHERE BC.clientName = '호날두');

---

2️⃣ 다중 행 서브쿼리

• 서브쿼리의 결과 값이 여러 행인 경우
  • IN, ANY, ALL, EXISTS 연산자 사용

1) IN, NOT IN

Ex) 도서를 구매한 적이 있는 고객명 조회

• booksale 테이블이 전체 구매내역이기에 해당 테이블에 존재하는 clientNo이 bookclient 안에(IN) 있으면 출력하면 된다.

-- 도서를 구매한 적이 있는 고객명 출력
SELECT BC.clientNo, BC.clientName
	FROM bookdb.bookclient BC
	WHERE BC.clientNo IN (SELECT DISTINCT BS.clientNo 
							FROM bookdb.booksale BS);

2) Any, All

• 관계 연산자 뒤에 위치
  • Any: 검색 조건이 서브쿼리의 결과 중에서 하나라도 만족하면 참
  • All: 검색 조건이 서브쿼리의 결과의 모든 값에 만족하면 참
    ( = 하나라도 만족하지 않으면 거짓)

Ex) 2번 고객이 주문한 도서의 최고 주문수량보다 더 많은 도서를 구입한 고객의 정보 조회

• 사실 max() 집계함수를 사용해도 되지만, 다음과 같이 ALL 을 활용할 수 있다. 2번 고객이 주문한 bsQty 데이터들보다 모두 큰 bsQty 를 가진 데이터를 출력한다.

• clientNo 2번 고객이 주문한 주문수량(bsQty) 는 다음과 같다.

SELECT BS.bsQty FROM bookdb.booksale BS WHERE BS.clientNo = '2'

• 여기서 ALL 을 적용하면 bsQty 가 2 와 5 보다 큰 데이터를 추출하게 된다.

-- 2번 고객이 주문한 도서의 최고 주문수량보다 더 많은 도서를 구입한 고객의 고객번호, 주문번호, 주문수량 출력
SELECT BS.clientNo, BS.bsNo, BS.bsQty
	FROM bookdb.booksale BS
    WHERE BS.bsQty > ALL (SELECT BS.bsQty FROM bookdb.booksale BS WHERE BS.clientNo = '2');

---

3️⃣ 서브쿼리 유형

1) 스칼라(Scalar) 서브쿼리

• SELECT절에 사용
• 결과 값을 단일 컬럼의 스칼라 값으로 반환
• 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능
• SELECT문과 UPDATE SET문에 사용

• booksale 테이블의 clientNo 을 bookClient 테이블과 비교하여 clientName 을 SELECT 해 이를 AS 고객명 으로 지정하였다.
• 각 clientNo 에 해당하는 clientName 은 하나의 값인 스칼라 값이기에 SELECT 문에서 사용할 수 있다.

SELECT clientNo AS '고객번호', 
		(SELECT clientName 
		 FROM bookClient 
		 WHERE bookClient.clientNo = bookSale.clientNo) AS '고객명',
         sum(bsQty) AS '총주문수량'
FROM bookSale
GROUP BY clientNo;

- 출력

---

2) 인라인 뷰(Inline View) 서브쿼리

• FROM 절에서 사용
• 테이블명 대신 인라인 뷰 부속질의를 사용(가상테이블)
• 서브쿼리 결과 반환되는 데이터는 다중 행, 다중열이어도 상관X
• 가상 뷰 형태로 제공
• 개발 중에 뷰가 필요한 모든 경우에 뷰를 생성하면 관리할 양이 많아 트랜잭션 관리나 성능상 문제 발생 가능성이 높은 경우에 사용

• 도서 가격이 25,000원 이상인 도서라는 전제조건이 있기에 WHERE book.price >= 25000 로 실행할 수도 있지만 FROM 절에서 필터링한 모습이다.

-- 도서 가격이 25000원 이상인 도서에 대하여 도서별로 
-- 도서명, 도서가격, 총판매수량, 총판매액 출력 (총판매액의 내림차순)
SELECT B.bookName, B.bookPrice,
	   sum(bsQty) AS '총주문량', 
       sum(bookPrice * bsQty) AS '총판매액'
FROM (SELECT bookNo, bookName, bookPrice FROM book
	  WHERE bookPrice >= 25000) B, bookSale
WHERE B.bookNo = bookSale.bookNo
GROUP BY B.bookNo
ORDER BY 총판매액 DESC;

- 출력

---

3) 중첩 서브쿼리

• WHERE 절에 서브쿼리 (결과를 한정하기 위해 사용)
• 부속질의 또는 하위질의
• 한 질의문 안에 다른 질의문이 중첩(nested)
• 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공 목적

• clientName 이 홍길동 인 고객의 clientNo 를 찾아 bookSale 테이블에서 bsQty 값의 합계를 구한다.

SELECT sum(bsQty)
	FROM bookSale
    WHERE clientNo = 
    	(SELECT clientNo FROM bookClient WHERE clientName = '홍길동');