[SQL] 서브쿼리 - Any, All, Exists

서브쿼리(Subquery)란?

• 서브쿼리란 SQL문에서 메인 쿼리가 아닌 하위에 존재하는 쿼리다
• 위치에 따라 Select절/From절/Where절로 나뉜다

SELECT col1, (SELECT ...)     -- 스칼라 서브쿼리: 하나의 컬럼처럼 사용
FROM (SELECT ...)             -- 인라인 뷰: 하나의 테이블처럼 사용
WHERE col = (SELECT ...)      -- 일반 서브쿼리

 

일반 서브쿼리

• 보통 Where절 안에서 서브쿼리의 결과에 따라 달라지는 하나의 상수처럼 사용

---------- # 평균 구하기
SELECT
      AVG(RENTAL_RATE)
 FROM
      FILM
;

---------- # 평균보다 큰 RENTAL_RATE집합 구하기
SELECT
       FILM_ID
     , TITLE
     , RENTAL_RATE
  FROM FILM
 WHERE RENTAL_RATE > 2.98
;

---------- # 위에 두개를 합쳐서 하나의 중첩 서브쿼리로
SELECT
       FILM_ID
     , TITLE
     , RENTAL_RATE
  FROM FILM
 WHERE RENTAL_RATE > (
  SELECT
    AVG (RENTAL_RATE)
    FROM FILM)
;

 

스칼라 서브쿼리(Scalar Subquery)

• Select절 안 서브쿼리로 하나의 컬럼처럼 사용

SELECT 	  
       A.FILM_ID
     , A.TITLE
     , A.RENTAL_RATE 
 FROM (
  SELECT
        A.FILM_ID
      , A.TITLE
      , A.RENTAL_RATE
      , (SELECT AVG(L.RENTAL_RATE) FROM FILM L) AS AVG_RENTAL_RATE 
    FROM FILM A) A
WHERE A.RENTAL_RATE > A.AVG_RENTAL_RATE
;

 

인라인뷰(Inline View)

• From절 안에 있는 서브쿼리로 하나의 테이블처럼 사용

SELECT
       A.FILM_ID
     , A.TITLE
     , A.RENTAL_RATE
FROM FILM A, (SELECT AVG(RENTAL_RATE) AS AVG_RENTAL_RATE FROM FILM) B
WHERE A.RENTAL_RATE > B.AVG_RENTAL_RATE
;

 

Any - 어떤 값이든 가능

• Any 연산자는 서브쿼리의 값이 어떠한 값이라도 만족하면 조건이 성립됨

---------- # >= ANY
SELECT TITLE, LENGTH
  FROM FILM
WHERE LENGTH >= ANY (
    SELECT MAX(LENGTH)        -- 상영시간이 가장 긴
    FROM FILM A
       , FILM_CATEGORY B 
    WHERE A.FILM_ID = B.FILM_ID
    GROUP BY B.CATEGORY_ID
    )
;

---------- # Any가 없으면 SQL 에러 발생
SELECT TITLE, LENGTH
  FROM FILM
  WHERE LENGTH >= (
    SELECT MAX(LENGTH)
    FROM FILM A
        , FILM_CATEGORY B 
    WHERE A.FILM_ID = B.FILM_ID
    GROUP BY B.CATEGORY_ID
    )
;

---------- # '=ANY'
SELECT TITLE, LENGTH
  FROM FILM
WHERE LENGTH = ANY (
    SELECT MAX(LENGTH)
    FROM FILM A
       , FILM_CATEGORY B 
    WHERE A.FILM_ID = B.FILM_ID
    GROUP BY B.CATEGORY_ID
    )
;

---------- # '=ANY'는 'IN'과 동일
SELECT TITLE, LENGTH
  FROM FILM
WHERE LENGTH IN (
    SELECT MAX(LENGTH)
    FROM FILM A
       , FILM_CATEGORY B 
    WHERE A.FILM_ID = B.FILM_ID
    GROUP BY B.CATEGORY_ID 
    )
;

 

All - 모든 값 만족해야

• All 연산자는 서브쿼리의 모든 값이 만족해야만 조건이 성립됨

---------- # All
SELECT TITLE, LENGTH
  FROM FILM
WHERE LENGTH >= ALL (            -- 상영시간이 가장 긴 영화의 모든 상영시간 보다 크거나 같아야만 조건 성립
  SELECT MAX(LENGTH)             -- 영화 분류별 상영시간이 가장 긴 상영시간을 구함
    FROM FILM A
       , FILM_CATEGORY B
    WHERE A.FILM_ID = B.FILM_ID
    GROUP BY B.CATEGORY_ID
    )
;

---------- # All 없으면 에러 발생
SELECT TITLE, LENGTH
  FROM FILM
WHERE LENGTH >= (                 -- ALL이 없다면 SQL 에러가 발생.
  SELECT MAX(LENGTH)              -- 서브쿼리의 반환되는 집합은 단 한건이어야만 함.
  FROM FILM A
     , FILM_CATEGORY B
  WHERE A.FILM_ID = B.FILM_ID
  GROUP BY B.CATEGORY_ID
  )
;

---------- # ALL
SELECT FILM_ID
     , TITLE
     , LENGTH
   FROM FILM
  WHERE LENGTH > ALL (
   SELECT ROUND(AVG (LENGTH), 2)
    FROM FILM
   GROUP BY RATING)
   ORDER BY LENGTH
;

 

Exists - 존재 여부

• Exists 연산자는 서브쿼리 내에 집합이 존재하는지 존재 여부만을 판단
• 해당 집합이 존재하기만 하면 더이상 연산을 멈추므로 성능상 유리함

---------- # EXISTS
SELECT
       FIRST_NAME
     , LAST_NAME
  FROM
       CUSTOMER C
WHERE
EXISTS ( SELECT 1
           FROM PAYMENT P                     -- 지불내역이
          WHERE P.CUSTOMER_ID = C.CUSTOMER_ID -- 고객이 있는지 확인
            AND P.AMOUNT > 11
        )
ORDER BY FIRST_NAME, LAST_NAME
;

---------- # NOT EXISTS
SELECT
       FIRST_NAME
     , LAST_NAME
  FROM
       CUSTOMER C
WHERE
  NOT EXISTS ( SELECT 1
                 FROM PAYMENT P
                WHERE P.CUSTOMER_ID = C.CUSTOMER_ID
                  AND P.AMOUNT > 11 
              )
ORDER BY FIRST_NAME, LAST_NAME
;

 

Reference

• Fast Campus, 모두를 위한 SQL/DB 올인원 패키지 온라인 강의