서브 쿼리

서브 쿼리란 하나의 쿼리문 안에 포함되어 있는 또 하나의 쿼리문을 뜻한다. 서브 쿼리는 여러 번의 쿼리를 수행해야만 얻을 수 있는 결과를 하나의 중첩된 SQL 문장으로 간편하게 결과를 얻을 수 있게 해준다. 서브 쿼리는 연산자의 오른쪽에 위치하여 사용하며, ANSI나 오라클 SQL 상에서 특별한 사용법의 차이는 없다.

서브 쿼리는 삽입되는 위치, 반환되는 결과에 따라 종류가 구분된다. 삽입되는 위치가 SELECT절인 경우 ‘SELECT 절 서브 쿼리’ 또는 ‘스칼라 서브 쿼리’ 라고 불리며, FROM절일 경우 ‘FROM 절 서브 쿼리’ 또는 ‘인라인뷰 서브 쿼리’ 라고 불린다. WHERE 절에 삽입될 경우에는 ‘WHERE 절 서브 쿼리’ 또는 ‘중첩 서브 쿼리’ 라고 한다. 그리고 반환되는 결과가 하나일 경우는 ‘단일 행 서브 쿼리’, 둘 이상일 경우에는 ‘다중 행 서브 쿼리’ 이다.

SELECT 절 서브 쿼리 (스칼라 서브 쿼리)

스칼라 서브 쿼리 (Scalar Subqueries)라고 불리며, 서브 쿼리를 끝마친 값 하나를 메인 쿼리에서 SELECT하기 때문에 서브 쿼리의 결과는 반드시 단일 행 또는 집계 함수를 거쳐야 한다.

SELECT 컬럼명, (서브쿼리) AS 별명
FROM 테이블명;

ex. 가입만 하고 구매를 하지 않은 고객들을 파악하여 해당 고객이 왜 구매를 하지 않았는지 이유를 알아보려고 한다. 고객 테이블의 멤버십등급은 구매금액이 없어도 SILVER등급이기 때문에 구매하지 않은 고객을 구분하기 어렵다. 구매 테이블을 활용하여 고객의 총 구매가격을 출력하라. (고객ID, 고객명, 멤버십등급, 총구매가격 출력)

SELECT 고객ID, 고객명, 멤버십등급,
    (SELECT SUM(총구매가격)
    FROM 구매
    WHERE 구매.고객ID = 고객.고객ID
    GROUP BY 고객ID) "총구매가격"
FROM 고객;

FROM 절 서브 쿼리 (인라인뷰 서브 쿼리)

인라인뷰 서브 쿼리 (Inline views Subqueries)라고 불리며, 서브 쿼리를 끝마친 테이블 하나를 메인 쿼리의 FROM절 테이블로 사용하기 때문에 서브 쿼리의 결과는 반드시 하나의 테이블로 반환되어야 한다.

SELECT 컬럼명
FROM (서브 쿼리) ;

ex. 앞의 예제의 쿼리를 인라인뷰 서브 쿼리로 활용하여 멤버십등급별 평균 구매금액을 구하여라 (멤버십등급, 평균금액 출력)

SELECT 멤버십등급, ROUND(AVG(총구매가격))"평균금액"
FROM (
    SELECT 고객ID, 고객명, 멤버십등급,
        (SELECT SUM(총구매가격)
        FROM 구매
        WHERE 구매.고객ID = 고객.고객ID
        GROUP BY 고객ID) "총구매가격"
    FROM 고객
    )
GROUP BY 멤버십등급;

WHERE 절 서브 쿼리 (중첩 서브 쿼리)

중첩 서브 쿼리 (Nested Subqueries)라고 불리며, 서브 쿼리를 끝마친 값들을 메인 쿼리의 조건절에 사용하기 때문에 단일 행, 복수 행 둘 다 반환해도 된다. 단일 행, 복수 행에 대한 내용은 이어지는 단일 행 서브 쿼리, 다중 행 서브 쿼리에서 다루도록 하겠다.

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 {비교연산자, IN, ANY, ALL} (서브 쿼리)

단일 행 서브 쿼리

단일 행 서브 쿼리는 서브 쿼리의 결과로서 단 하나의 값만 추출하고, 이 결과를 토대로 다시 메인 쿼리가 진행된다.

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 비교연산자 (서브 쿼리);

ex. 구매 테이블에서 총구매가격이 평균값보다 높은 구매내역의 구매코드, 고객ID, 구매일을 출력하여라.

SELECT 구매코드, 고객ID, 구매일
FROM 구매 
WHERE 총구매가격 > (SELECT AVG(총구매가격) FROM 구매);

다중 행 서브 쿼리

다중 행 서브 쿼리는 서브 쿼리의 결과가 2건 이상 출력되고, 메인 쿼리에서는 다중 행 비교 연산자인 IN, ANY, ALL 등을 활용하여 서브 쿼리 결과들에 대한 연산을 수행한다.

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 {IN, ANY, ALL} (서브 쿼리);

(1) IN 연산자 활용

IN 연산자는 주어진 조건에 일치하는 값을 찾는 연산자이다. 즉, 서브 쿼리가 출력하는 값 중에서 하나라도 만족하는 경우에 결과를 출력한다.

ex. 상품공급 테이블에서 공급수량이 190 이상인 공급사가 공급한 상품공급코드, 상품코드, 공급사ID를 출력하여라.

SELECT 상품공급코드, 상품코드, 공급사ID
FROM 상품공급
WHERE 공급사ID IN (SELECT 공급사ID FROM 상품공급 WHERE 공급수량 >= 190);

(2) ANY 연산자 활용

ANY 연산자는 특정한 값이 아닌 범위로 비교 연산을 처리한다. ANY 연산자는 서브 쿼리에서 출력되는 어떠한 값이라도 만족하면 조건이 성립한다.

ex. ANY 연산자를 활용하여 상품공급 테이블의 공급사ID가 S02인 공급사가 제공하는 최소 공급 단가보다 큰 공급 단가를 갖는 상품코드, 공급사ID, 공급단가를 출력하여라.

SELECT 상품코드, 공급사ID, 공급단가 
FROM 상품공급
WHERE 공급단가 > ANY(SELECT 공급단가 FROM 상품공급 WHERE 공급사ID = 'S02');

(3) ALL 연산자 활용

ALL 연산자 역시 ANY 연산자와 비슷하게 어떤 특정한 값이 아닌 범위로 비교 연산을 처리한다. ALL 연산자는 서브 쿼리에서 출력되는 모든 값을 만족하면 조건이 성립된다.

ex. 연산자 ALL를 활용하여 상품 테이블의 의류 카테고리의 모든 상품정가보다 낮은 상품의 상품코드, 상품명, 상품정가를 출력하여라.

SELECT 상품코드, 상품명, 상품정가
FROM 상품
WHERE 상품정가 < ALL (SELECT 상품정가 FROM 상품 WHERE 상품카테고리 = '의류');

기타 유용한 서브 쿼리 기능

SQL 작업을 하다 보면 쿼리 결과를 따로 테이블로 만들어 저장 싶을 때가 많다. 물론, 작업 결과를 엑셀 파일로 편집하여 SQL Developer와 같은 프로그램으로 테이블을 임포트(import)할 수 있으나, 다소 번거로운 것이 사실이다. 쿼리 결과로 생성된 테이블을 저장하거나, 데이터를 수정하는 작업은 서브 쿼리를 이용하면 더욱 편리하다.

(1) 서브 쿼리를 활용한 테이블 생성

서브 쿼리를 활용하면 작업 도중 추출한 데이터 세트를 별도의 테이블로 생성할 수 있다.

CREATE TABLE 테이블명
AS
[SELECT 컬럼명 FROM 테이블명 WHERE 조건];

ex. 고객 테이블에서 멤버십 등급이 ‘VIP’인 고객을 추출하여 ‘고객_VIP’ 테이블을 생성하여라.

CREATE TABLE 고객_VIP
AS
SELECT *
FROM 고객
WHERE 멤버십등급 = 'VIP';

(2) 서브 쿼리를 활용한 데이터 입력

데이터 삽입 역시 기존 테이블에서 추출하여 서브 쿼리로 새로 만든 테이블에 간단하게 삽입할 수 있다. 기본적인 문법은 다음과 같다.

INSERT INTO 테이블명
AS
[SELECT 컬럼명 FROM 테이블명 WHERE 조건];

ex. 기존의 고객 테이블에서 고객ID가 ‘C0004’인 고객을 찾아 고객_VIP 테이블에 삽입하여라.

INSERT INTO 고객_VIP
SELECT *
FROM 고객 
WHERE 고객ID = 'C0004';

(3) 서브 쿼리를 활용한 데이터 수정

데이터 수정 역시 서브 쿼리를 활용하여 실행할 수 있다.

UPDATE 테이블명
SET 컬럼명 = (서브 쿼리)
WHERE 조건;

ex. 고객_VIP 테이블에서 고객ID가 ‘C0004’인 고객의 멤버십 등급을 ‘C0005’ 고객과 동일하게 변경하여라.

UPDATE 고객_VIP
SET 멤버십등급 = (SELECT 멤버십등급 FROM 고객_VIP WHERE 고객ID = 'C0005')
WHERE 고객ID = 'C0004';

This post was written based on what I read and studied the book below.

http://www.yes24.com/Product/Goods/99002458

저작자표시 비영리 변경금지

"What's this world coming to?"