[2과목] 2-9. 서브쿼리

■ 서브쿼리

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

 

■ 서브쿼리 사용 가능한 곳

• SELECT 절
• FROM 절
• WHERE 절
• HAVING 절
• ORDER BY 절
• 기타 DML (INSERT, DELETE, UPDATE) 절
• ** GROUP BY 절 사용 불가

 

■ 서브쿼리 종류

1. 동작하는 방식에 따라

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

 

2. 위치에 따라

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

select * | 컬럼명 | 표현식, (select * | 컬럼명 | 표현식 from 테이블명 또는 뷰명 where 조건)
from 테이블명 또는 뷰명;

 

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

select * | 컬럼명 | 표현식
from (select * | 컬럼명 | 표현식 from 테이블명 또는 뷰명 where 조건)
where 조건;

 

 

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

select * | 컬럼명 | 표현식
from 테이블명 또는 뷰명
where 조건연산자 (select * | 컬럼명 | 표현식 from 테이블명 또는 뷰명 where 조건);

 

 

 

■ WHERE절 서브쿼리 종류

 

1. 단일행 서브쿼리

• 서브쿼리 결과가 1개의 행이 리턴되는 형태
• 단일행 서브쿼리 연산자 종류

연산자	의미
=	같다
<>	같지 않다
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다

 

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

select empno, ename, sal
from emp
where sal>(select avg(sal) from emp);

 

 

2. 다중행 서브쿼리

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

연산자	의미
IN	같은 값을 찾음
> ANY	최소값을 반환함
< ANY	최대값을 반환함
< ALL	최소값을 반환함
> ALL	최대값을 반환함

 

ex) ALL과 ANY 비교

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

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

> ANY(2000,3000) : 최소값 2000보다 큰 행들 반환

< ANY(2000,3000) : 최대값 3000보다 작은 행들 반환

 

 

아래 두개의 결과값은 동일함

select empno, ename, sal
from emp
where sal > (select min(sal) from emp where deptno=10);

select empno, ename, sal
from emp
where sal > ANY (select sal from emp where deptno=10);

 

 

 

3. 다중 컬럼 서브쿼리

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

ex) emp 테이블에서 부서별 최대 급여자 확인

select empno, ename, sal, deptno 
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);

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

 

 

4. 상호연관 서브쿼리

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

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

select empno, ename, sal, deptno
from emp e1
where sal > (select avg(sal) from emp e2 where e1.deptno=e2.deptno group by deptno);

• 메인쿼리와 결과적으로 비교해야 할 컬럼은 sal과 deptno인데, 그 중 sal에 대한 대소비교 전에 먼저 비교할 부서(deptno) 정보가 확정되어야 함
• 먼저 비교할 .deptno값을 서브쿼리에 전달
• 메인쿼리에는 서브쿼리의 테이블 정보가 없으므로 (순서상 메인쿼리부터 해석) e1.deptno=e2.deptno 조건 사용불가

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

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

 

 

■ 인라인뷰 (Inline View)

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

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

select e.empno, e.ename, e.sal, i.max_sal
from emp e, (select deptno, max(sal) as max_sal from emp group by deptno) i
where e.deptno = i.deptno and e.sal=i.max_sal;

 

 

※ 뷰 사용의 장점

• 독립성 : 테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 됨
• 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작성할 수 있음. 또한 해당 형태의  SQL을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있음
• 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 컬럼을 빼고 생성하여 사용자에게 정보를 감출 수 있음

 

 

■ 스칼라 서브쿼리

• SELECT 절에서 사용하는 쿼리로, 마치 하나의 컬럼처럼 표현하기 위해 사용 (단 하나의 출력 대상만 표현 가능)
• 각 행마다 스칼라 서브쿼리 결과가 하나여야 함(단일행 서브쿼리 형)
• 조인의 대체 연산

ex) 부서번호가 10인 emp의 각 직원의 사번, 이름과 부서이름을 출력 (부서이름을 스칼라 서브쿼리로)

select empno, ename, (select dname from dpet d where d.deptno=e.deptno) as dname
from emp e
where deptno=10;

 

 

 

■ 서브쿼리 주의 사항

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