Subquery (서브쿼리)

서브 쿼리 (Subquery; 하위 질의; 부속 질의)

 : 하나의 SQL문 안에 다른 SQL문이 중첩되어 있는 형식

 : 하나의 쿼리 안에 또 다른 하나의 쿼리가 담겨 있는 것

 : 질의를 1차 수행한 다음, 반환값을 다음 질의에 포함시켜 사용

 : 다른 테이블에서 결과로 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공 시 사용

 

 

서브 쿼리 구성 및 형식

 : 메인 쿼리와 서브 쿼리로 구성

 : 서브 쿼리에서 검색한 결과로 메인 쿼리 검색 수행

SELECT 속성명 FROM 테이블명 WHERE 조건 연산자 (SELECT 속성명 FROM 테이블명 WHERE 조건);

 

서브 쿼리 연산자

 : WHERE 절에서 사용

연산	연산자	반환 행
비교	=, !=(<>) , <=, >=, <, >	단일
집합	IN, NOT IN	다중
존재	EXISTS, NOT EXISTS	다중
한정	ALL, ANY	다중

 

ALL과 ANY 차이점 (아래 링크 참조)

https://olli2.tistory.com/28

ALL과 ANY 차이점

IN과 EXISTS 차이점 (아래 링크 참조)

https://olli2.tistory.com/27

 

서브 쿼리의 종류

[반환되는 데이터 형태에 따른 분류]

단일 행 서브 쿼리

 : 서브 쿼리 결과 값이 단일 행을 반환 -> 반환된 결과를 메인 쿼리로 전달하여 메인 쿼리 수행

 : 가장 일반적으로 사용되는 유형

 : WHERE 절에 비교 연산자 사용 ( =, !=(<>) , <=, >=, <, > )

 

다중 행 서브 쿼리

 : 서브 쿼리 결과 값이 여러 행을 반환

: WHERE 절에 IN, ANY, ALL, EXISTS 연산자 사용

 

 

[동작 방식에 따른 분류]

비연관 서브쿼리 (Un-Correlated 서브 쿼리)

 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않음

 : 메인쿼리에 값을 제공하기 위해 사용 (값 = 서브쿼리가 실행된 결과)

​

연관 서브쿼리 (Correlated 서브 쿼리)

 : 서브쿼리가 메인쿼리 칼럼을 가지고 있음

 : 일반적으로 메인쿼리가 먼저 수행 -> 서브쿼리에서는 데이터의 조건이 맞는지 확인하기 위해 사용

 

 

[사용되는 위치에 따른 분류]

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

 : SELECT 절에서 사용

 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브쿼리

 : 결과 값을 단일 열의 스칼라 값으로 반환 ( = 단일 열 반환 )

    -> 스칼라 값이 들어 갈 수 있는 곳에서 사용 가능 (속성명 위치/열 위치에 표기)

 : 일반적으로  SELECT 문과 UPDATE SET 문에서 사용 가능 

 

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

 : FROM 절에서 사용

 : 테이블명 위치에 표기

 : 결과를 뷰(가상 테이블) 형태로 반환 (서브쿼리 반환 결과가 다중 행 및 다중 열 가능)

 : 뷰가 필요한 모든 경우에 뷰를 생성 시 관리할 양이 너무나 많아짐

    -> 트랜잭션 관리 및 성능 상의 문제가 발생 가능

    -> 이와 같은 상황에 필요한 뷰를 인라인 뷰로 생성해서 사용

 

중첩 서브 쿼리(Nested subquery)

 : WHERE 절에서 조건절의 일부로 사용 ( = 메인 쿼리 테이블의 특정 컬럼 값과 비교한 값을 반환하는 용도)

 : 서브쿼리 반환 결과가 다중 행 및 다중 열 가능

 :  술어와 같이 사용 (LIKE, BETWEEN, IS NULL, IS NOT NULL, IN, EXISTS 등)

 : 결과를 한정시키기 위해 사용

 

 

서브 쿼리 사용 시 주의사항

 : 서브쿼리는 메인쿼리의 칼럼을 사용할 수 있으나, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음

   ( = 서브쿼리는 괄호 밖의 테이블도 접근 가능 )

 : 쿼리 결과에 서브 쿼리 칼럼을 표시하고 싶은 경우, 조인 방식으로 변환하거나 함수 또는 스칼라 서브쿼리 등 사용

 : 반드시 괄호로 감싸서 사용

 : 서브쿼리에서는 ORDER BY절 사용 불가 -> (ORDER BY절은 메인쿼리 마지막에 사용

 : SELECT절,  FROM절,  WHERE절,  HAVING절, ORDER BY절, INSERT문의 VALUES절, UPDATE문의 SET절에서 사용 가능

 

 

조인과 서브쿼리 차이점 (아래 링크 참조)

https://olli2.tistory.com/26

 

서브 쿼리 활용 예제

단일 행 서브쿼리

-- 고객 호날두의 주문수량 조회
-- 1. client 테이블에서 고객명 '호날두'의 clientNo를 찾아서
-- 2. bookSale 테이블에서 이 clientNo에 해당되는 주문에 대해
-- 주문일, 주문수량 출력
SELECT bsDate, bsQty FROM bookSale
WHERE clientNo = (SELECT clientNo FROM client WHERE clientName = '호날두');


-- 고객 호날두가 주문한 총 주문수량 출력
-- 1. client 테이블에서 고객명 '호날두'의 clientNo를 찾아서
-- 2. bookSale 테이블에서 이 clientNo에 해당되는 주문에 대해 
-- 총 주문수량 구해서 출력
SELECT SUM(bsQty) AS "총 주문수량" FROM bookSale
WHERE clientNo = (SELECT clientNo FROM client WHERE clientName = '호날두');


-- 가장 비싼 도서의 도서명과 가격 출력
-- 1. 가장 비싼 도서 찾아서
-- 2. 해당 도서의 도서명과 가격 출력
SELECT bookName, bookPrice FROM book
WHERE bookPrice = (SELECT MAX(bookPrice) FROM book);


-- 비교 연산자 사용
-- 1. 도서의 평균 가격보다 비싼 도서에 대해 (가격이 더 큰)
-- 2. 도서명, 가격 출력
-- 서브 쿼리에서 '평균 도서 가격'(단일행) 반환
SELECT bookName, bookPrice FROM book
WHERE bookPrice > (SELECT AVG(bookPrice) FROM book);

 

다중 행 서브쿼리

-- IN, NOT IN

-- 도서를 구매한 적이 있는 고객의 고객명 출력
-- 1. bookSale에 있는  clientNo는 모두 구매한 고객
-- 2. client 테이블에서 이 clientNo에 해당되는 고객을 찾아서
-- 고객번호, 고객명 출력
SELECT clientNo, clientName FROM client
WHERE clientNo IN (SELECT clientNo FROM bookSale);


-- 한 번도 주문한 적이 없는 고객의 고객번호, 고객명 출력
SELECT clientNo, clientName FROM client
WHERE clientNo NOT IN (SELECT clientNo FROM bookSale);


-- 도서명이 '안드로이드 프로그래밍'인 도서를 구매한 고객의 고객명 출력
-- 1. book 테이블에서 도서명이 '안드로이드 프로그래밍'인 도서의 bookNo를 찾아서
-- 2. bookSale 테이블에서 이 bookNo에 해당되는 도서를 구매한 clientNo를 찾고 
-- 3. client 테이블에서 이 clientNo에 해당되는 고객명을 찾아서 출력
SELECT clientName FROM client
WHERE clientNo IN (SELECT clientNo FROM bookSale
                   WHERE bookNo IN (SELECT bookNo FROM book
                                    WHERE bookName = '안드로이드 프로그래밍'));

-- 고객명 기준으로 정렬
SELECT clientName
FROM client
WHERE clientNo IN (SELECT clientNo FROM bookSale
                   WHERE bookNo IN (SELECT bookNo FROM book
                                    WHERE bookName = '안드로이드 프로그래밍'))
ORDER BY clientName;

-- EXIST, NOT EXISTS

-- 도서를 구매한 적이 있는 고객
-- 1. bookSale에 조건에 해당되는 행이 존재하면 참 반환
-- 2. client 테이블에서 이 clientNo에 해당되는 고객의
-- 고객번호, 고객명 출력
SELECT clientNo, clientName FROM client
WHERE EXISTS (SELECT clientNo FROM bookSale
			  WHERE client.clientNo = bookSale.clientNo);


-- 한 번도 주문적이 없는 고객의 고객번호, 고객명 출력
-- 서브 쿼리에 조건에 해당되는 행이 없으면 TRUE 반환
SELECT clientNo, clientName FROM client
WHERE NOT EXISTS (SELECT clientNo FROM bookSale
			     WHERE client.clientNo = bookSale.clientNo);

 

스칼라 서브 쿼리

-- 스칼라 서브 쿼리
-- 고객별로 고객번호, 고객명,  총 주문수량 출력
SELECT clientNo, (SELECT clientName FROM client
                  WHERE client.clientNo = bookSale.clientNo), SUM(bsQty)
FROM bookSale GROUP BY clientNo;

-- 스칼라 서브 쿼리 결과 열에 이름 지정
SELECT clientNo, (SELECT clientName FROM client
                  WHERE client.clientNo = bookSale.clientNo) AS "고객명", SUM(bsQty) AS "총 주문수량"
FROM bookSale GROUP BY clientNo;

 

인라인 뷰 서브 쿼리

-- 인라인 뷰 서브 쿼리 
-- 도서 가격이 25,000원 이상인 도서에 대하여
-- 도서별로 도서명, 도서가격, 총 판매 수량, 총 판매액 출력
-- 총 판매액으로 내림차순 정렬
SELECT bookName, bookPrice, SUM(bsQty) AS 총판매량, SUM(bookPrice * bsQty) AS "총판매액"
FROM (SELECT bookNo, bookName, bookPrice
	  FROM book
      WHERE bookPrice >= 25000) book, bookSale
WHERE book.bookNo = bookSale.bookNo
GROUP BY book.bookNo
ORDER BY 총판매량 DESC;