[SQL] 서브 쿼리 사용하기 1 - 단일 행, 다중 행

1. 단일행 서브쿼리

특정 쿼리에서 검색한 값을 다른 쿼리에서 받아 검색하려면 서브 쿼리를 사용해야 한다.

EX ) JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하라

JONES 보다 더 많은 월급을 받는 사원의 정보를 조회하기 위해서는 JONES의 월급을 알아야 한다.

-- JONES의 월급
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES';

JONES의 월급을 검색한 결과, 2975임을 알 수 있다. 이를 바탕으로 위의 문제를 해결할 수 있다.

-- JONES 보다 더 많은 월급을 받는 사원
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 2975;

여기서 위의 쿼리에서 2975라는 값은 첫번째 검색했던 JONES의 월급을 검색하는 쿼리로부터 나온 결과이므로 2975라는 값 대신 서브쿼리를 대신 쓸 수 있다.

SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT SAL
		FROM EMP
		WHERE ENAME = 'JONES');

 

Q. SCOTT과 같은 월급을 받는 사원들의 이름과 월급을 출력하라

SCOTT과 같은 월급을 받는 사원들의 정보를 검색하려면 SCOTT의 월급을 알아야 하고, 이를 서브쿼리로 작성하여 한번에 검색할 수 있다.

SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT SAL
		FROM EMP
		WHERE ENAME = 'SCOTT');

여기서 결과를 출력할때 SCOTT의 정보도 같이 검색된다. 만약 SCOTT이 출력되지 않게 하려는 경우 메인 쿼리에 아래와 같이 조건을 주면 된다.

SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT SAL
		FROM EMP
		WHERE ENAME = 'SCOTT')
AND ENAME != 'SCOTT';

 

 

2. 다중 행 서브쿼리

특정 쿼리에서 검색한 여러 개의 값을 다른 쿼리에서 받아 검색하려면 다중 행 서브쿼리를사용해야한다. 즉 서브 쿼리에서 메인 쿼리로 하나의 값이 아니라 여러개의 값이 반환되는 경우를 다중 행 서브쿼리라고 한다.

EX ) 직업이 SALESMAN인 사원들과 같은 월급을 받는 사원들의 이름과 월급을 출력하라

위에서 봤던 단일행 서브쿼리를 사용하여 검색하면 에러가 발생한다.

SELECT ENAME, SAL
FROM EMP
WHERE SAL = (SELECT SAL
		FROM EMP
		WHERE JOB = 'SALESMAN');

ORA-01427: single-row subquery returns more than one row

단일 행 하위 질의에 2개 이상의 행이 리턴되어 발생하는 에러로, 직업이 SALESMAN인 사원들이 한 명이 아니라 여러 명이기 때문에 이퀄(=)을 사용하면 에러가 발생한다. 이럴때는 IN 연산자를 사용해야 한다.

SELECT ENAME, SAL
FROM EMP
WHERE SAL IN (SELECT SAL
		FROM EMP
		WHERE JOB = 'SALESMAN');

서브 쿼리의 종류

종류	설명
단일 행 서브 쿼리	서브 쿼리에서 메인 쿼리로 하나의 값이 반환됨
다중 행 서브 쿼리	서브 쿼리에서 메인 쿼리로 여러 개의 값이 반환됨
다중 컬럼 서브 쿼리	서브 쿼리에서 메인 쿼리로 여러개의 컬럼 값이 반환됨

 

서브 쿼리의 종류에 따라 사용되는 연산자

종류	연산자
단일 행 서브 쿼리	=, !=, <, >, >=, <=
다중 행 서브 쿼리	IN, NOT IN, >ANY, <ANY, >ALL, <ALL

 

Q. 부서 번호가 20번인 사원들 중 가장 많은 월급을 받는 사원보다 더 많은 월급을 받는사원의 이름과 부서 번호, 월급을 출력하라

부서 번호가 20번인 사원 들 중 가장 월급을 받는 사원보다 더 많은 월급을 받는 사원의 정보를 검색하려면 1) 부서 번호가 20번인 사원들의 월급 정보를 가지고 와야하며 2) 그들 중 가장 많은 월급을 받는 사원의 월급보다 더 큰 값을 찾아야 한다. 즉 부서 번호가 20인 사원들의 월급 리스트에서 가장 큰 값보다 큰 값을 찾기 때문에 >ALL 연산을 사용해야 한다.

 

1 ) 부서 번호가 20번인 사원들의 월급 정보

SELECT SAL
FROM EMP
WHERE DEPTNO = 20;

이를 통해 부서 번호가 20번인 사원들의 월급중 3000이 가장 큰 값이라는 것을 알 수있다. 이를 이용하여 위 문제의 답을 구하면 아래와 같다.

2 ) 그들 중 가장 많은 월급을 받는 사원의 월급보다 더 큰 값

SELECT ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL >ALL (SELECT SAL
		FROM EMP
		WHERE DEPTNO = 20);