[MSSQL] 서브 쿼리 SubQuery

728x90

서브 쿼리(SubQuery)란 하나의 SQL 문안에 포함돼 있는 또 다른 SQL 문을 말한다.

조인과 서브쿼리의 차이점.

조인은 집합간의 곱(Product)의 관계이다. 즉 1:1 관계의 테이블이 조인하면 1(= 1 * 1) 레벨의 집합이 생성되고, 1:M 관계의 테이블을 조인하면 M(= 1 * M) 레벨의 집합이 생성된다.

예를 들어 조직(1)과 사원(M)의 테이블을 조인하면 결과는 사원 레벨(M)의 집합이 생성된다.

그러나 서브 쿼리는 서브 쿼리 레벨과는 상관없이 항상 메인 쿼리 레벨로 결과 집합이 생성된다. 위의 예를 들어 메인 쿼리로 조직(1), 서브 쿼리로 사원(M) 테이블을 사용하면 결과 집합은 조직(1) 레벨이 된다.

서브 쿼리를 사용할 때 다음 사항에 주의해야 한다.

1. 서브 쿼리는 괄호로 감싸서 기술한다.

2. 서브 쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.

3. 중첩 서브 쿼리 및 스칼라 서브 쿼리(SELECT 절)에서는 ORDER BY를 사용하지 못한다.

1. 단일 행 서브 쿼리

단일 행 서브 쿼리는 서브 쿼리의 결과가 무조건 단일 행 결과가 나온다는 것이다. 만약 서브 쿼리의 결과 건수가 2건 이상을 반환하면 SQL은 Run Time Exception 실행 오류가 발생한다.

USERS 테이블에 'REDJUN'이 소속된 팀의 유저 정보를 표시하는 쿼리를 아래와 같이 작성할 수 있다.

SELECT USER_ID,
       USER_NAME,
       USER_AGE
  FROM USERS
 WHERE TEAM_ID = ( SELECT TEAM_ID  -- '=' 연산자로 단일 행 서브 쿼리를 사용함.
                     FROM USERS
                    WHERE USER_NAME = 'REDJUN'
                 )

위의 쿼리는 'REDJUN'의 소속팀을 알아내는 서브 쿼리가 먼저 1번 수행돼 소속 'TEAM_ID'가 반환된다. 메인 쿼리는 서브 쿼리에서 반환된 'TEAM_ID'를 이용해서 조건을 만족하는 유저들의 정보를 출력한다.

단일 행 서브 쿼리를 아래와 같이 사용이 가능하다.

유저들의 평균키를 알아내는 서브 쿼리로 평균 키 이하의 유저들을 조회하는 쿼리이다.

SELECT USER_ID,
       USER_NAME,
       USER_AGE
  FROM USERS
 WHERE HEIGHT <= ( SELECT AVG(HEIGHT)
                     FROM USERS
                 )

2. 다중 행 서브 쿼리

서브 쿼리의 결과가 2건 이상 반환될 수 있다면, 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다. 그렇지 않으면 SQL 문은 오류를 반환한다.

아래는 'REDJUN'이라는 유저가 소속된 팀들의 정보를 모두 출력하는 쿼리이다.

SELECT TEAM_ID,
       TEAM_NAME,
       TEAM_LOGO_URL
  FROM TEAM
 WHERE TEAM_ID IN ( SELECT TEAM_ID -- 'IN' 다중 행 비교 연산자로 다중 행 처리함.
                      FROM USERS
                     WHERE USER_NAME = 'REDJUN'
                  )

서브 쿼리에서는 'REDJUN'이 소속된 TEAM_ID가 모두 조회되고, 그 결과로 메인 쿼리는 팀들의 정보를 조회한다.

3. 다중 컬럼 서브 쿼리 ( SQL Server 지원 X )

다중 컬럼칼럼 서브 쿼리는 서브 쿼리의 결과로 여러 개의 칼럼이 반환돼 메인 쿼리의 조건과 동시에 비교되는 것을 의미한다.

SQL Server에서는 지원하지 않는다.!!!!........

간단하게 예제 쿼리만 보고 지나간다.

SELECT TEAM_ID,
       USER_NAME
  FROM USERS
 WHERE ( TEAM_ID, HEIGHT ) IN ( SELECT TEAM_ID,
                                       MIN(HEIGHT)
                                  FROM USERS
                                 GROUP BY TEAM_ID
                              )
ORDER BY TEAM_ID, USER_NAME

4. 연관 서브 쿼리

연관 서브 쿼리는 서브 쿼리 내에 메인 쿼리 컬럼이 사용된 서브 쿼리이다.

아래의 쿼리는 연관 서브 쿼리이며, 유저 자신이 속한 팀의 정보를 출력하는 SQL 문이다.

SELECT B.TEAM_NAME,
       A.USER_NAME,
       A.HEIGHT
  FROM USERS A, TEAM B
 WHERE A.HEIGHT < ( SELECT AVG (X.HEIGHT)
                      FROM USERS
                     WHERE X.TEAM_ID = A.TEAM_ID  -- 서브 쿼리에서 메인 쿼리 컬럼 사용
                     GROUP BY X.TEAM_ID
                  )
   AND B.TEAM_ID = A.TEAM_ID
ORDER BY USER_NAME

위의 서브 쿼리는 메인 쿼리에 존재하는 모든 행에 대해서 반복 수행한다.

5. 다른 위치에서 사용하는 서브 쿼리

가. 스칼라 서브 쿼리

스칼라 서브 쿼리는 한 행, 한 컬럼만을 반환하는 서브 쿼리를 말한다. 주로 SELECT 절에서 사용된다.

아래는 스칼라 서브 쿼리의 예제이다.

SELECT A.USER_NAME AS 유저이름,
       A.HEIGHT AS 키,
       ROUND (( SELECT AVG ( X.HEIGHT)
                  FROM USERS X
                 WHERE X.TEAM_ID = A.TEAM_ID), 3) AS 팀평균키
  FROM USERS A

스칼라 서브 쿼리는 SELECT 절에서 사용하는 만큼 메인 쿼리의 결과 건수만큼 반복수행된다. 또, 스칼라 서브 쿼리 또한 단일 행 서브 쿼리이기 때문에 결과가 2건 이상 반환되면 SQL 문은 오류를 반환한다.

나. FROM 절에서 서브 쿼리

FROM 절에서 사용되는 서브 쿼리를 인라인 뷰(Inline View)라고 한다. 인라인 뷰를 사용하면 서브 쿼리의 결과를 마치 테이블처럼 사용할 수 있다. 데이터베이스에 SELECT 문을 객체로서 저장해 테이블처럼 사용하는 뷰(View)와 달리, 인라인 뷰는 쿼리 내에서 즉시 처리된다.

SELECT B.TEAM_NAME AS 팀명,
       A.USER_NAME AS 유저명
  FROM ( SELECT TEAM_ID, 
                USER_NAME
           FROM USERS
          WHERE SIGNUP_DATE >= '2022-01-01' -- 2022년 이후 회원가입한 유저
       ) A,
       TEAM B  -- JOIN
 WHERE B.TEAM_ID = A.TEAM_ID
 ORDER BY A.TEAM_ID

인라인 뷰에서는 SQL 별로 특별한 규칙을 정하고 있다.

오라클 - 인라인 뷰에서 ORDER BY 절 사용 가능.
SQL Server - 인라인 뷰에서 TOP-N 쿼리 사용 시에만 ORDER BY절 사용 가능.

이 포스트에서는 SQL Server의 TOP-N 쿼리만 간단하게 살펴보겠다.

- 인라인 뷰 ORDER BY 사용.

-- 서브 쿼리에 ORDER BY절 사용.
SELECT *
  FROM (SELECT USER_ID,
               USER_NAME
          FROM USERS
         ORDER BY SIGHUP_DATE DESC -- 최근 가입한 유저부터 정렬.
       ) A
       
-- 실행 결과.
Msg 1033, Level 15, State 1, Line 11
TOP, OFFSET 또는 FOR XML을 함께 지정하지 않으면 뷰, 인라인 함수, 파생 테이블, 하위 쿼리 및 공통 테이블 식에서 ORDER BY 절을 사용할 수 없습니다.

- 인라인 뷰 TOP-N ORDER BY 사용.

아래의 쿼리는 서브 쿼리에 TOP-N 쿼리를 사용하면서 ORDER BY를 사용했다. 결과는 성공적으로 나온다.

-- 서브 쿼리에 ORDER BY절 사용.
SELECT *
  FROM (SELECT TOP 10 USER_ID,
               USER_NAME
          FROM USERS
         ORDER BY SIGHUP_DATE DESC -- 최근 가입한 유저부터 10명만 출력.
       ) A

SQL Server에서 인라인 뷰에 Order By를 TOP-N쿼리와 함께 사용해야 하는 규칙을 정한 이유는 아마... TOP 절은 내부적으로 정렬을 사용하기 때문이 아닐까...

SQL Server 서브 쿼리 주의사항.

아래는 SQL Server에서 서브 쿼리 사용 시 주의해야 하는 사항을 말한다.

비교 연산자로 시작하는 하위 쿼리의 선택 목록에는 식이나 열 이름이 한 개만 포함될 수 있습니다(EXISTS 및 IN이 SELECT *나 목록에서 실행될 경우는 제외).
외부 쿼리의 WHERE 절에 열 이름이 포함되면 하위 쿼리 선택 목록의 열과 조인이 호환 가능해야 합니다.
ntext, text 및 image 데이터 형식은 하위 쿼리의 선택 목록에 사용할 수 없습니다.
단일 값을 반환해야 하므로 수정되지 않은 비교 연산자로 시작하는 하위 쿼리(ANY 또는 ALL 키워드가 나오지 않음)에는 GROUP BY 및 HAVING 절이 포함될 수 없습니다.
GROUP BY가 포함된 하위 쿼리에는 DISTINCT` 키워드를 사용할 수 없습니다.
COMPUTE 및 INTO 절은 지정할 수 없습니다.
ORDER BY은 TOP을 함께 지정해야만 지정할 수 있습니다.
하위 쿼리를 사용하여 만든 뷰는 업데이트할 수 없습니다.
EXISTS로 시작하는 하위 쿼리의 선택 목록은 규칙에 따라 단일 열 이름 대신 별표(*)로 구성됩니다. EXISTS로 시작하는 하위 쿼리는 존재 테스트를 만들며 데이터 대신 TRUE 또는 FALSE를 반환하므로 EXISTS로 시작하는 하위 쿼리에 대한 규칙은 표준 선택 목록의 규칙과 동일합니다.