단일행 서브쿼리(Single Row SubQuery), 다중행 서브쿼리(Multi Row SubQuery), 다중 칼럼 서브쿼리(Multi Column SubQuery), 연관 서브쿼리(Correlated Subquery)

# 서브쿼리가 좋을지 조인이 좋을지는 옵티마이저가 판단하여 실행하므로, 서브쿼리로 인한 성능저하는 걱정하지 않아도 된다.

# 하지만, 서브쿼리로 테이블 접근횟수가 늘어나는 것은 지양해야한다.

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

 

 1. 단일행 서브쿼리

 - 리턴값이 1개 이하인 서브쿼리

 - 단일행 비교연산자와 사용

 ex) =, <, <=, >=, >, <>

 

SQL> SELECT PNAME, POSITION
     FROM PLAYER
     WHERE HEIGHT <= (SELECT AVG (HEIGHT) FROM PLAYER);
     
# 실행결과 (모든플레이어의 평균키보다 작거나 같은선수들을 출력)
PNAME        POSITION
----------------------
고종수             CB
이천수             FW
종아리             GK
     

 

 2. 다중행 서브쿼리

  - 리턴값이 여러행

  - 다중행 비교연산자와 사용

   ex) IN, ALL, ANY, SOME, EXISTS

 

# 이천수라는 선수가 2명이고 속한 팀이 다르다고 가정한다.

# IN 연산자
SQL> SELECT T_NAME
     FROM TEAM
     WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PNAME = '이천수');
     # 조인이 아니다. 단순 연산일뿐
     
# 실행결과 (서브쿼리의 리턴행은 2개이고 그 두개 행중에 속한다면 출력)
T_NAME
-----------------------
맨체스터유나이티드
바르셀로나
     

 

 3. 다중칼럼 서브쿼리

  - SQL Server 에서 지원되지 않음 (Oracle은 지원)

  - 비교칼럼 갯수가 같아야 함

  

# D_AVSAL 은 각 부서별 평균연봉이며 백의 자리까지 반올림한 INT형으로 저장되어있다.

# IN 연산자
SQL> SELECT A.E_NAME, B.D_NAME                            
     FROM EMP A JOIN DEPT B
     ON B.DEPTNO = A.DEPTNO
     # DEPT 테이블을 가져온건, 서브쿼리로 불러온 칼럼은 메인 쿼리의 SELECT 절에 사용할 수 없기 때문)
     # 반대로 서브쿼리에선 메인쿼리의 컬럼을 사용 가능하다..
     WHERE (A.SAL, A.DEPTNO) IN (SELECT D_AVSAL, DEPTNO FROM DEPT GROUP BY DEPTNO);
     
     
# 실행결과 
# (서브쿼리의 리턴칼럼은 2개이다. 각 부서별로 평균연봉이 기입된 데이터가 리턴값이다)
# (각 부서별 사원중 정확히 각 부서별 평균연봉을 가진 사원들을 모두 출력한다, 두 조건을 다 만족하는 데이터)
E_NAME             D_NAME
-----------------------
구르미                영업
바나나                영업
토마토                유통
부르릉                생산
     

 

 4. 연관 서브쿼리

  - 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

  - EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다.

# 연관서브쿼리 (서브쿼리내에 A.DEPTNO가 존재)
SQL> SELECT A.E_NAME, A.DEPTNO, A.SAL                            
     FROM EMP A
     WHERE A.SAL < (SELECT AVG (X.SAL) FROM EMP X 
                    WHERE X.DEPTNO = A.DEPTNO
                    GROUP BY X.DEPTNO);
     
     
# 실행결과 (각 부서별 평균연봉보다 작은 사원 출력)
E_NAME             DEPTNO               SAL
------------------------------------------
구르미                D01                    500
바나나                D01                    700
토마토                D02                    1000
부르릉                D03                    300


# EXISTS 서브쿼리
SQL> SELECT A.E_NAME, A.DEPTNO, A.SAL                            
     FROM EMP A
     WHERE EXISTS (SELECT 1 FROM DEPT X 
                    WHERE X.DEPTNO = A.DEPTNO
                    AND X.AVGSAL BETWEEN '1000' AND '1500');
     
     
# 실행결과 (부서의 평균연봉이 1000과 1500 사이인 부서의 모든사원 출력)
         (D05 부서에는 두명이 전부이다. 그 부서의 평균연봉은 3500/2 = 1750 이다.)
E_NAME             DEPTNO               SAL
------------------------------------------
쿵쿵쿵                D05                    3000
두두두                D05                    500
붕붕붕                D06                    1000
쿵쾅쾅                D06                    1700
슝슝슝                D06                    1500
...
244개의 행이 검색되었습니다.