[SQL 17] 서브 쿼리 - 다중 행, 다중 열 서브 쿼리

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
다중 행 서브 쿼리

서브 쿼리에서 여러 행이 검색되는 쿼리문이다. 연산자를 제외하면 단일 행 서브 쿼리와 같다.

기본 구조

SELECT [DISTINCT] 컬럼, 컬럼 …

FROM 테이블

WHERE 컬럼 <다중 행 연산자> (SELECT 컬럼

  FROM 테이블);

• 다중 행 서브 쿼리는 다중 행 연산자를 이용한다.
• 다중 행 연산자의 종류

연산자	기   능
IN	검색된 값 중에 하나만 일치하면 참이다.
ANY	검색된 값 중에 조건에 맞는 것이 하나 이상 있으면 참이다.
ALL	모든 검색된 값과 조건에 맞아야 한다.

◈ 예제

▶ IN 연산자를 이용한 다중 행 서브 쿼리

select employee_id, first_name, job_id, salary

from employees

where manager_id in (select manager_id

from employees

where department_id = 20)

and department_id <> 20;

20번 부서원들과 같은 상사로부터 관리 받는 사원을 검색한 것이다.

서브 쿼리문을 따로 실행해보면 두 개의 행이 검색된다. 단일행 연산자를 사용할 경우에는 위의 SQL문은 에러가 발생하겠지만, 다중 행 연산자인 'IN'을 사용하면 에러가 발생하지 않는다.

'IN'의 기능은 예전에 배웠던 그대로이다. 즉, 서브 쿼리에서 검색되는 값들 중 일치하는 값이 하나라도 있으면 검색한다.

▶ ANY를 이용한 다중 행 서브 쿼리

select employee_id, last_name, job_id, salary

from employees

where salary < any (select salary

    from employees

    where job_id = 'ST_MAN')

and job_id <> 'ST_MAN';

직책이 'ST_MAN'인 사원들 중 가장 많은 급여를 받는 사람보다 급여가 작은 사원을 검색했다.

서브 쿼리를 따로 검색해보면,

select salary

from employees

where job_id = 'ST_MAN';

ANY는 검색된 값 중에 조건에 맞는 것이 하나 이상 있으면 참인데, 위 예제의 경우는 검색된 급여 중 어떤 값보다도 작으면 된다. 즉, 가장 큰 값인 8000 보다 낮은 급여를 받으면 참이다. 그래서 서브 쿼리의 검색된 급여 중 5800이 있음에도 불구하고 메인 쿼리에서 5800보다 많은 급여를 받는 사원들이 검색된 것이다.

▶ ALL을 이용한 다중 행 서브 쿼리

select employee_id, last_name, job_id, salary

from employees

where salary < all (select salary

 from employees

 where job_id = 'ST_MAN')

and job_id <> 'ST_MAN';

직책이 'ST_MAN'인 사원들 보다 급여가 낮은 사원을 검색했다.

ALL은 모든 검색 값과 조건이 맞아야 한다. 위 예제의 경우는 ALL을 이용 검색된 모든 값보다 낮은 값을 검색하도록 한 것이다.

즉, 서브 쿼리문에 검색된 모든 급여보다 낮아야 하므로 결국 5800보다 낮은 급여를 받는 사원이 검색된다.

ALL과 ANY 정리

사 용	의 미	같은 표현
컬럼 > ANY	가장 작은 값보다 크다.	컬럼 > MIN
컬럼 < ANY	가장 큰 값보다 작다.	컬럼 < MAX
컬럼 > ALL	가장 큰 값보다 크다.	컬럼 > MAX
컬럼 < ALL	가장 작은 값보다 작다.	컬럼 < MIN

부등호를 이용하는 다중 행 서브 쿼리문은 MIN이나 MAX함수를 이용한 문으로 수정이 가능하다.

ALL을 이용한 다중 행 서브 쿼리의 예제문을 바꿔보면 다음과 같다.

select employee_id, last_name, job_id, salary

from employees

where salary  < (select min(salary)

     from employees

     where job_id = 'ST_MAN')

and job_id <> 'ST_MAN';

실행해보면 알겠지만, 결과값은 같다.

No. 02
다중 열 서브 쿼리

여러 개의 컬럼을 검색하는 서브 쿼리이다.

다중 열 서브 쿼리는 주로 'IN'을 사용하지만 '='도 사용 가능하다. 'IN'의 사용을 권한다.

기본 구조

SELECT [DISTINCT] 컬럼, 컬럼 …

FROM 테이블

WHERE (컬럼1, 컬럼2, …) IN (SELECT 컬럼1, 컬럼2, …

FROM 테이블);

• 서브 쿼리의 SELECT 문에 여러 개의 컬럼을 검색한다.
• 반드시 비교 대상 컬럼과 1:1 대응돼야 한다.

◈ 예제

select employee_id, first_name, job_id, salary

from employees

where (manager_id, job_id) in (select manager_id, job_id

from employees

where first_name = 'Bruce')

and first_name <> 'Bruce';

Bruce와 동일한 상사이면서 같은 직업인 사원을 검색한 것이다.

서브 쿼리만 실행해 보면,

select manager_id, job_id

from employees

where first_name = 'Bruce';

즉, manager_id가 103이면서 job_id가 'IT_PROG'인 사원들이 검색되는 것이다.

검색 조건에 기술하는 컬럼들이 1:1대응하여 모두 같아야 검색이 된다.

검색 조건이 동시에 검색된 두 개의 컬럼과 비교 될 때 다중 열 서브 쿼리를 이용한다.

위 예제 같은 경우는 다음과 같은 형태의 서브 쿼리로 바뀔 수 있다.

select employee_id, first_name, job_id, salary

from employees

where manager_id in (select manager_id from employees where first_name = 'Bruce')

and job_id in (select job_id from employees where first_name = 'Bruce')

and first_name <> 'Bruce';

다중 열 서브쿼리는 조건을 따로 검색한 것과 같은 기능을 한다.

▶ 각 부서별로 최소 급여를 받는 사원의 정보를 검색한다.

select department_id, employee_id, first_name, salary

from employees

where (department_id, salary) in (select department_id, min(salary)

   from employees

   group by department_id)

order by department_id;

조건은 각 부서의 번호와 최소 급여가 둘 다 일치하는 사원을 찾는 문제인데, 이때 부서 번호와 급여를 각각 따로 조건 검색 할 방법이 없다.

조건을 따로 검색하는 것이 불가능한 경우 그룹함수를 사용하는 전형적인 예이다.