2-9) 서브쿼리

서브쿼리

• 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문
• 반드시 괄호로 묶어야 함
  • 예) SELECT 안에 SELECT 문 / INSERT, UPDATE, DELETE 안의 SELECT 문

 

서브쿼리 사용 가능한 곳

1. SELECT 절
2. FROM 절
3. WHERE 절
4. HAVING 절
5. ORDER BY 절
6. 기타 DML (INSERT, DELETE, UPDATE) 절

** GROUP BY 절은 사용 불가

 

서브 쿼리 종류

 

1. 동작하는 방식에 따라

• UN-CORRELATED (비연관) 서브쿼리
  • 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태
  • 메인쿼리에 서브쿼리가 실행한 결과 값을 제공하기 위한 목적으로 사용
• CORRELATED (연관) 서브쿼리
  • 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브 쿼리
  • 일반적으로 메인쿼리가 먼저 수행된 후에 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용

 

2. 위치에 따라

• 스칼라 서브쿼리
  • SELECT 에 사용하는 서브쿼리
  • 서브쿼리 결과를 마치 하나의 컬럼처럼 사용하기 위해 주로 사용
  • 단일 행 반환!

** 문법

 

• 인라인뷰
  • FROM 절에 사용하는 서브쿼리
  • 서브쿼리 결과를 테이블처럼 사용하기 위해 주로 사용

** 문법

 

• WHERE 절 서브쿼리
  • 가장 일반적인 서브쿼리
  • 비교 상수 자리에 값을 전달하기 위한 목적으로 주로 사용 (상수항의 대체)
  • 리턴 데이터의 형태에 따라 단일행 서브쿼리 / 다중행 서브쿼리 / 다중컬럼 서브쿼리 / 상호연관 서브쿼리로 나뉨

** 문법

 

 

WHERE 절 서브쿼리 종류

 

1. 단일행 서브쿼리

• 서브쿼리 결과가 1개의 행이 리턴되는 형태
• 아래의 연산자 사용하여 값 비교

 

 

예) EMP 테이블에서 전체 직원의 급여 평균보다 높은 평균을 받는 직원의 정보 출력

 

STEP 1) 비교대상 (전체 직원의 급여 평균) 확인

 

STEP 2) 메인쿼리의 비교 상수 자리에 서브쿼리 결과 전달

 

 

2. 다중행 서브쿼리

• 서브쿼리 결과가 여러 행이 리턴되는 형태
• '=', '>', '<' 와 같은 비교 연산자 사용 불가
  • 여러 값이랑 비교 불가한 연산자들
• 서브쿼리 결과를 하나로 요약하거나 다중행 서브쿼리 연산자를 사용

 

 

예1) ALL과 ANY 비교

> ALL(2000, 3000) : 2000, 3000 보다 커야 함 → 최대값 (3000) 보다 큰 행들 반환

< ALL(2000, 3000) : 2000, 3000 보다 작아야 함 → 최소값 (2000) 보다 작은 행들 반환

> ANY (2000, 3000) : 2000 또는 3000 보다 커야 함 → 최소값 (2000) 보다 큰 행들 반환

< ANY (2000, 3000) : 2000 또는 3000 보다 작아야 함 → 최대값 (3000) 보다 작은 행들 반환

 

예2) 다중행 서브쿼리 연산자 오류 (일반 비교 연산자 사용 불가)

 

해결 1) 서브쿼리 결과를 하나의 행의 결과가 되도록 변경

 

해결 2) 다중행 서브쿼리 연산자로 변경

 

 

3. 다중컬럼 서브쿼리

• 서브쿼리 결과가 여러 컬럼이 리턴되는 형태
• 메인쿼리와의 비교 컬럼이 2개 이상
• 대소 비교 전달 불가 (두 값을 동시에 묶어 대소비교 불가)

 

예) EMP 테이블에서 부서별 최대 급여자 확인

 

STEP 1) 부서별 최대 급여 확인

 

STEP 2) 메인쿼리에 비교 대상으로 서브쿼리 결과 전달

• 부서별 최대 급여가 여러 값이 나오므로 비교 시 다중행 연산자인 IN 사용 (= 사용 시 에러)

 

4. 상호연관 서브쿼리

• 메인쿼리와 서브쿼리의 비교를 수행하는 형태
• 비교할 집단이나 조건은 서브쿼리에 명시 (메인쿼리 절에는 서브쿼리 컬럼이 정의되지 않았기 때문에 에러 발생)

 

예) EMP 테이블에서 부서별로 해당 부서의 평균 급여보다 높은 급여를 받는 사원 정보

- 에러 발생 상황 (다중 컬럼 서브쿼리는 동시에 두 컬럼에 대한 대소비교 불가)

 

해결) 대소 비교할 컬럼을 메인쿼리에, 일치 조건은 서브쿼리에 전달

• 비교해야 할 컬럼인 SAL, DEPTNO 중 SAL에 대한 대소비교 전에 먼저 비교할 부서(DEPTNO) 정보가 확정돼야 함
• 비교할 DEPTNO 값을 서브쿼리에 전달
• 메인쿼리에는 서브쿼리의 테이블 정보가 없으므로 메인쿼리에서 조건 정의 시 에러
  • E.DETPNO = D.DEPTNO 조건 사용 불가

 

상호연관 서브쿼리 연산 순서

1. 메인쿼리 테이블 READ
2. 메인쿼리 WHERE 절 확인 (SAL 확인)
3. 서브쿼리 테이블 READ
4. 서브쿼리 WHERE 절 확인 (다시 E1.DEPTNO 요구)
5. E1.DEPTNO 값을 서브쿼리의 DEPTNO 컬럼과 비교하여 조건절 완성
6. 위 조건에 성립하는 행의 그룹연산 결과 확인 (AVG(SAL))
7. 위 결과를 메인쿼리에 전달하여 해당 조건을 만족하는 행만 추출

** 상호연관 서브쿼리 사용 시 GROUP BY 생략 가능

 

 

인라인뷰 (Inline View)

• 쿼리 안의 뷰의 형태로, 테이블처럼 조회할 데이터를 정의하기 위해 사용
• 테이블명이 존재하지 않기 때문에 다른 테이블과 조인 시 반드시 ALIAS 명시
  • 단독으로 사용하는 경우 불필요
• WHERE 절 서브쿼리와 다르게 서브쿼리 결과를 메인 쿼리의 어느 절에서도 사용할 수 있음
• 인라인뷰의 결과와 메인쿼리 테이블을 조인할 목적으로 주로 사용
• 모든 연산자 사용 가능

 

예1) EMP 테이블에서 부서별 최대 급여자를 출력하되, 최대 급여와 함께 출력

• 인라인뷰에서의 함수에 의한 출력 결과 (MAX())는 컬럼 ALIAS를 통해 메인쿼리에 전달!
• 최대급여와 함께 출력이므로 조인 필요

 

예2) EMP 테이블에서 부서별로 해당 부서의 평균 급여보다 높은 급여자를 출력하되, 평균 급여와 함께 출력

 

 

스칼라 서브쿼리

• SELECT 절에 사용하는 쿼리, 마치 하나의 컬럼처럼 표현하기 위해 사용
  • 하나의 출력 대상만 표현 가능
• 각 행마다 스칼라 서브쿼리 결과가 하나여야 함 (단일행 서브쿼리 형태)
• 조인의 대체 연산
• 스칼라 서브쿼리를 사용한 조인 처리 시 OUTER JOIN이 기본 (값이 없더라도 생략되지 않고 NULL로 출력됨)

 

예1) EMP의 각 직원의 사번, 이름과 부서이름을 출력 (부서이름을 스칼라 서브쿼리로)

• SELECT 절은 FROM 이후에 수행되므로 FROM 절의 ALIAS 스칼라 서브쿼리에서 사용 가능

 

예2) EMP의 각 직원의 사번, 이름, 부서번호, 급여와 함께 급여 총 합을 출력 (총 합을 스칼라 서브쿼리로)

 

예3) 서브쿼리와 아우터 조인

• MGR 컬럼이 NULL이더라도 메인 쿼리의 출력 대상은 항상 값을 리턴해야 하므로 서브쿼리의 결과에 대한 값만 NULL 처리되어 출력 (생략 X) → OUTER JOIN

 

서브쿼리 주의 사항

• 특별한 경우 (TOP-N 분석 등)을 제외하고는 서브 쿼리 절에 ORDER BY 절 사용 불가
• 단일 행 서브쿼리와 다중 행 서브쿼리에 따라 연산자의 선택이 중요

 

예) 서브쿼리에 ORDER BY 전달 시 에러 발생