07. 서브쿼리 & 계층형 질의#
2과목 · SQL 활용. ★ = 시험 빈출 / 난이도 高
1. 서브쿼리 개요#
"하나의 SQL문 안에 포함되어 있는 또 다른 SQL문"
★ 서브쿼리 규칙#
- 서브쿼리는 괄호로 감싸서 사용
- 메인쿼리는 서브쿼리 칼럼 사용 불가
- 서브쿼리는 메인쿼리 칼럼 사용 가능
- 단일행/복수행 비교 연산자와 함께 사용
ORDER BY사용 불가 (인라인뷰는 예외)
2. 서브쿼리 분류 (위치별)#
| 위치 | 명칭 | 특징 |
|---|---|---|
| SELECT 절 | 스칼라 서브쿼리 | 한 행, 한 칼럼만 반환 |
| FROM 절 | 인라인뷰 / 동적뷰 | ORDER BY 사용 가능 |
| WHERE / HAVING 절 | 중첩 서브쿼리 | 단일행/다중행 |
3. 단일행 서브쿼리#
개요#
- 결과 건수가 반드시 1건 이하
- 단일행 비교 연산자 (
=,<>,>,<,>=,<=)
sql
SELECT *
FROM PLAYER
WHERE TEAM_ID = (
SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '손흥민'
);
4. ★ 다중행 서브쿼리#
다중행 비교 연산자#
| 연산자 | 설명 |
|---|---|
IN |
결과 중 하나라도 일치 |
ALL |
모든 결과보다 (> ALL = 모두보다 큰) |
ANY / SOME |
결과 중 하나라도 (> ANY = 하나라도보다 큰) |
EXISTS |
결과가 존재하면 TRUE |
```sql -- IN SELECT REGION_NAME, TEAM_NAME FROM TEAM WHERE TEAM_ID IN ( SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = '정현수' );
-- ALL: SAL이 영업부 모든 사원보다 큰 사원 SELECT ENAME FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 10);
-- ANY: SAL이 영업부 어느 한 사원보다 큰 사원 SELECT ENAME FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 10);
-- EXISTS: 존재 여부만 확인 SELECT DNAME FROM DEPT D WHERE EXISTS (SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO); ```
5. 다중 칼럼 서브쿼리#
서브쿼리에서 여러 칼럼 반환, 메인쿼리 조건과 동시 비교
sql
-- 부서별 최고 연봉자
SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE (DEPTNO, SAL) IN (
SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO
);
6. ★ 연관 서브쿼리 (Correlated Subquery)#
서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리
EXISTS는 항상 연관 서브쿼리로 사용- 메인쿼리 행마다 서브쿼리 재실행
sql
-- 자기 부서 평균보다 급여가 높은 사원
SELECT ENAME, SAL, DEPTNO
FROM EMP E
WHERE SAL > (
SELECT AVG(SAL) FROM EMP
WHERE DEPTNO = E.DEPTNO -- ★ 메인쿼리의 E.DEPTNO 참조
);
7. 위치별 서브쿼리 활용#
★ SELECT 절 — 스칼라 서브쿼리#
한 행, 한 칼럼만 반환
sql
SELECT ENAME,
(SELECT DNAME FROM DEPT D
WHERE D.DEPTNO = E.DEPTNO) AS DNAME
FROM EMP E;
★ FROM 절 — 인라인뷰 (Inline View)#
- 동적뷰 (Dynamic View)라고도 함
- ORDER BY 사용 가능
- 인라인뷰에 정렬 후 일부 추출 → TOP-N 쿼리
sql
-- Oracle: 인라인뷰 + ROWNUM
SELECT ROWNUM, ENAME, SAL
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 3;
HAVING 절 — 그룹조건#
sql
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP);
UPDATE SET 절#
sql
UPDATE EMP
SET SAL = (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10)
WHERE EMPNO = 7369;
INSERT VALUES 절#
sql
INSERT INTO EMP_BACKUP
VALUES (
(SELECT MAX(EMPNO)+1 FROM EMP), 'NEW', 'CLERK', SYSDATE, 1000
);
8. ★ 뷰 (VIEW)#
정의#
"가상의 테이블" — 실제 데이터 없이, 쿼리만 저장
| 항목 | 테이블 | 뷰 |
|---|---|---|
| 데이터 저장 | 실제 저장 | 저장 안 함 |
| 디스크 사용 | O | X |
| 변경 시 | 데이터 변경 | 정의만 변경 |
CREATE VIEW#
```sql CREATE VIEW EMP_VIEW AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;
-- 사용 SELECT * FROM EMP_VIEW;
-- 삭제 DROP VIEW EMP_VIEW; ```
★ 뷰의 장단점#
| 장점 | 단점 |
|---|---|
| 독립성 (테이블 구조 변경에 영향 X) | 자체 인덱스 없음 |
| 편리성 (복잡한 쿼리 재사용) | 정의 변경 어려움 |
| 보안성 (특정 칼럼만 노출) | 데이터 갱신 제약 |
9. 계층형 질의 (Hierarchical Query)#
개요#
- 테이블에 계층형 데이터(상하 관계)가 있을 때 사용
- 예: 조직도, 메뉴, 사원-관리자 (자기참조 관계)
★ Oracle: CONNECT BY#
sql
SELECT 칼럼
FROM 테이블
[WHERE 조건]
START WITH 시작조건 -- 루트 행 지정
CONNECT BY [NOCYCLE] 관계조건 -- 부모-자식 관계
[ORDER SIBLINGS BY 칼럼]; -- 형제 노드 정렬
★ 순방향 vs 역방향 전개#
| 전개 방향 | 표현 |
|---|---|
| 순방향 (위→아래) | CONNECT BY PRIOR 부모ID = 자식ID |
| 역방향 (아래→위) | CONNECT BY PRIOR 자식ID = 부모ID |
PRIOR는 이전 행을 가리킴
```sql -- 순방향: 사장 → 부하 (KING의 모든 부하 직원 트리) SELECT LEVEL, ENAME, MGR FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;
-- 역방향: 사원 → 상사 (특정 사원의 상사 계통) SELECT LEVEL, ENAME, MGR FROM EMP START WITH ENAME = 'SMITH' CONNECT BY PRIOR MGR = EMPNO; ```
★ 계층형 가상 칼럼 (Pseudo Column)#
| 가상 칼럼 | 설명 |
|---|---|
LEVEL |
루트=1, 하위로 갈수록 +1 |
CONNECT_BY_ROOT 칼럼 |
루트 노드의 칼럼 값 |
CONNECT_BY_ISLEAF |
리프 노드=1, 아니면 0 |
CONNECT_BY_ISCYCLE |
순환 발생=1 (NOCYCLE 사용 시) |
SYS_CONNECT_BY_PATH(칼럼, 구분자) |
경로 표시 (예: /KING/JONES/SCOTT) |
sql
SELECT LEVEL,
LPAD(' ', (LEVEL-1)*2) || ENAME AS 조직도,
CONNECT_BY_ROOT ENAME AS 최상위,
SYS_CONNECT_BY_PATH(ENAME, '/') AS 경로,
CONNECT_BY_ISLEAF AS 리프여부
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME; -- 같은 레벨 형제 노드 정렬
★ ORDER SIBLINGS BY#
- 일반
ORDER BY는 계층 구조를 무시하고 정렬 ORDER SIBLINGS BY는 계층 구조 유지하면서 같은 레벨 노드만 정렬
★ NOCYCLE#
- 계층 구조에 순환 참조가 있을 때 무한 루프 방지
CONNECT_BY_ISCYCLE = 1로 순환 노드 식별
sql
SELECT LEVEL, ID, NAME, CONNECT_BY_ISCYCLE
FROM CIRCULAR_DATA
START WITH MANAGER_ID IS NULL
CONNECT BY NOCYCLE PRIOR ID = MANAGER_ID;
★ CONNECT BY vs WHERE 차이#
| 위치 | 동작 |
|---|---|
CONNECT BY 안에 조건 |
계층 생성 과정에서 가지치기 — 해당 노드와 그 하위 모두 제외 |
WHERE 절 조건 |
계층 완성 후 결과 행만 필터링 — 자식은 그대로 출력 |
```sql -- WHERE: 김부장만 빠지고, 그의 부하직원은 출력됨 ... CONNECT BY PRIOR EMPNO = MGR WHERE ENAME != '김부장';
-- CONNECT BY: 김부장과 그 하위 전체가 트리에서 제외 ... CONNECT BY PRIOR EMPNO = MGR AND ENAME != '김부장'; ```
★ SQL Server: CTE 재귀 (Common Table Expression)#
sql
WITH CTE_EMP (EMPNO, ENAME, MGR, LVL) AS (
-- 앵커 멤버 (시작점)
SELECT EMPNO, ENAME, MGR, 1
FROM EMP
WHERE MGR IS NULL
UNION ALL
-- 재귀 멤버
SELECT E.EMPNO, E.ENAME, E.MGR, LVL+1
FROM EMP E INNER JOIN CTE_EMP C
ON E.MGR = C.EMPNO
)
SELECT * FROM CTE_EMP;
10. 셀프 조인 (SELF JOIN)#
"동일 테이블 사이의 조인" — 계층형 질의의 대안
sql
-- 사원과 관리자 (자기 참조)
SELECT E.ENAME 사원, M.ENAME 관리자
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO;
★ 반드시 별칭(ALIAS) 사용
시험 핵심 체크리스트#
- 서브쿼리 기본 규칙 (ORDER BY 불가, 단 인라인뷰는 가능)
- 단일행 vs 다중행 서브쿼리
- 다중행 비교 연산자 (IN, ALL, ANY, EXISTS)
- 연관 서브쿼리 (Correlated Subquery)
- 스칼라 서브쿼리 (SELECT 절)
- 인라인뷰 (FROM 절, ORDER BY 가능)
- 뷰의 특징 (데이터 X)
- CONNECT BY 문법 (START WITH, PRIOR)
- 순방향 vs 역방향 전개
- LEVEL, CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH
- SQL Server CTE 재귀 구조