서브쿼리: 다중행 서브쿼리와 IN, ANY, ALL 연산자

서브 쿼리

서브 쿼리 : 쿼리 안에 쿼리를 넣어서 원하는 결과를 조회한다.

SCOTT의 부서명을 알려면 다음과 같다.

1) JOIN을 사용한 경우

SELECT ENAME, DNAME 

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO

WHERE ENAME = 'SCOTT';

-- 사원테이블과 부서테이블을 조인하여 사원명과 부서명을 출력한다.

조인 조건은 두 테이블의 부서번호가 같으며, 셀렉트 조건은 사원명이 SCOTT인 경우.

결과) SCOTT과 그에 해당하는 부서명 RESERCH가 출력된다.

2) 서브쿼리를 사용한 경우

SELECT DEPTNO FROM EMP 

WHERE ENAME = 'SCOTT';

결과) 사원테이블에서 SCOTT이 소속된 부서번호 20이 출력된다.

SELECT DNAME FROM DEPT 

WHERE DEPTNO = 20;

결과) 부서테이블에서 위의 부서번호 20에 해당하는 부서명 RESEARCH가 출력된다.

위 2개의 쿼리를 하나로 만든다. 

SELECT DNAME FROM DEPT 

WHERE DEPTNO = (

SELECT DEPTNO FROM EMP 

WHERE ENAME = 'SCOTT'

)

결과) 부서테이블에서 부서번호를 사원테이블의 SCOTT의 부서번호인 20을 사용하여 부서명 RESEARCH가 출력된다.

1. 단일행 서브쿼리

수행 결과가 오직 하나의 로우만을 반환하는 서브쿼리

SELECT DNAME FROM DEPT 

WHERE DEPTNO = (

SELECT DEPTNO FROM EMP 

WHERE ENAME = 'SCOTT'

)

- 서브쿼리로 사용된 쿼리문의 결과가 20이라는 하나로 로우이므로 단일행 서브쿼리이다.

- 메인쿼리의 WHERE절에서는 단일행 비교연산자인 =, >, >=, <=, <>만 사용할 수 있다.

2. 다중행 서브쿼리

서브쿼리에서 반환되는 결과가 하나 이상인 경우. 다중행 서브쿼리는 반드시 다중행 연산자와 함께 사용해야 한다.

다중행 연산자

IN : 메인쿼리의 비교조건이 서브쿼리 결과 중에서 하나라도 일치하면 참

ANY, SOME : 메인쿼리의 비교조건이 서브쿼리의 검색결과와 하나 이상 일치하면 참

ALL : 메인쿼리의 비교조건이 서브쿼리의 검색결과와 모두 일치하면 참

EXIST : 메인쿼리의 비교조건이 서브쿼리의 결과 중 만족하는 값이 하나라도 존재하면 참

(1) IN 연산자 : 서브쿼리 출력결과와 하나라도 일치하면 참이 된다.

SELECT DISTINCT DEPTNO FROM EMP -- 부서번호를 중복없이(DISTINCT) 출력

WHERE SAL >= 3000;

사원테이블 EMP에서 연봉 3000 이상 받는 사원들이 있는 부서의 부서번호(DEPTNO)를 중복없이(DISTINCT) 출력하면 20번과 10번인 것을 알 수 있다. 실행결과는 2개의 행(10번, 20번)이 된다.

위의 쿼리를 서브쿼리로 해서, 연봉이 3000 이상인 사원명, 급여, 부서명을 출력해보자. 

SELECT ENAME, SAL, DEPTNO FROM EMP

WHERE DEPTNO = (

SELECT DISTINCT DEPTNO FROM EMP

WHERE SAL >= 3000;

)

하지만 실행 결과가 2개 행이라 에러가 난다. 

위와 같이 서브쿼리의 결과가 단일행이 아니라 다중행인 경우에는 반드시 다중행 연산자를 사용해야만 한다.

연봉을 3000 이상 받는 사원이 소속된 부서(10번, 20번)와 동일한 부서 근무하는 사원을 출력해야 하는데, 서브쿼리의 결과인 10이나 20 둘 중 어느 하나와 일치해도 상관없으므로 이퀄(=) 대신 서브쿼리 결과와 하나라도 일치하면 참인 IN 연산자를 써야 한다.

SELECT * FROM EMP

WHERE DEPTNO IN ( 

SELECT DISTINCT DEPTNO FROM EMP

WHERE SAL >= 3000

)

간략하게 생각해보면 WHERE절에서 배운 IN 연산자를 생각하면 된다.

IN 연산자는 특정 컬럼의 값이 여러 개의 값 중에서 하나인지를 물어보기 위해서 사용했다.

SELECT * FROM EMP

WHERE DEPTNO IN (10, 20)

DEPTNO가 10이거나 20인 것을 검색하는데, 둘 중 하나의 결과와 일치하기만 해도 된다.

(2) ALL 연산자 : 검색된 조건을 모두 만족

30번 부서 사원 중 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사람의 이름과 급여를 출력해보자.

1) 단일행 서브쿼리 (그룹함수 사용)

SELECT ENAME, SAL FROM EMP

WHERE SAL > (

SELECT MAX(SAL) FROM EMP

GROUP BY DEPTNO

HAVING DEPTNO = 30

);

그룹함수를 이용해 30번 부서의 최대값을 구하는 쿼리를 서브쿼리로 사용.

30번 부서의 MAX(SAL) 값보다 SAL값이 큰 ENAME과 SAL을 출력하도록 한다.

30번 부서의 급여 최대값 MAX(SAL)보다 급여가 많은 사원들의 이름과 급여가 출력되었다.

2) 다중행 서브쿼리 (ALL 연산자 사용)

SELECT ENAME, SAL FROM EMP

WHERE SAL > ALL(

SELECT SAL FROM EMP WHERE DEPTNO = 30

)

30번 부서에 소속된 사원의 급여를 서브쿼리로 잡으면 서브쿼리 결과가 4개 행이므로 단순히 > 연산자만 쓰면 에러가 난다. 이 때 30번 부서에 소속된 사원의 급여라는 서브쿼리 전체 결과를 ALL로 잡아 이 ALL보다 큰(>) SAL로 WHERE절을 완성시키면 된다.

30번 부서에 속하는 사람들 전체(ALL)보다 급여가 많은 사원들의 이름과 급여가 출력되었다.

(3) ANY 연산자 : 검색된 조건 중 하나 이상 만족. 찾아진 값에 대해 하나라도 크면 참. 즉, 검색한 값 중 가장 작은 값보다 크기만 해도 참이 된다.

부서번호 30번 사원 급여 중 가장 낮은 값(950)보다 높은 급여를 받는 사원들의 이름, 급여를 출력해보자.

1) 단일행 서브쿼리 (그룹함수 사용)

SELECT ENAME, SAL FROM EMP

WHERE SAL > (

SELECT MIN(SAL) FROM EMP

GROUP BY DEPTNO

HAVING DEPTNO = 30

);

그룹함수를 이용해 30번 부서의 최소값을 구하는 쿼리를 서브쿼리로 사용.

30번 부서의 MIN(SAL) 값보다 SAL값이 큰 ENAME과 SAL을 출력하도록 한다.

30번 부서의 급여 최소값 MIN(SAL)보다 급여가 많은 사원들의 이름과 급여가 출력되었다.

2) 다중행 서브쿼리 (ANY 연산자 사용)

SELECT ENAME, SAL FROM EMP

WHERE SAL > ANY (

SELECT SAL FROM EMP  WHERE DEPTNO = 30

)

30번 부서에 소속된 사원의 급여를 서브쿼리로 잡으면 서브쿼리 결과가 4개 행이므로 단순히 > 연산자만 쓰면 에러가 난다. 이 때 30번 부서에 소속된 사원의 급여라는 서브쿼리 전체 결과 중 하나 이상만 일치해도 참인 ANY 연산자를 쓴다. 서브쿼리의 값 중 가장 작은 값보다 커도 참이 되기 때문이다. ANY보다 큰(>) SAL로 WHERE절을 완성시키면 된다.

30번 부서에 속하는 사람들 중 최소값을 포함하여 아무나(ANY)보다 급여가 많은 사원들의 이름과 급여가 출력되었다.