[SQL] 서브쿼리 (Sub Query)

서브쿼리

서브쿼리는 SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미한다. 서브쿼리의 결과 값을 사용하여 기능을 수행하는 영역은 메인쿼리main query이다. 서브쿼리는 반드시 괄호 () 내에 작성한다.

-- Whalen 사원의 월급보다 많이 받는 사원을 조회
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT salary
 FROM employees
 WHERE last_name='Whalen');

💡 서브쿼리는 메인쿼리의 컬럼을 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.

서브쿼리 종류

종류	설명	사용 가능한 연산자
단일행 서브쿼리	서브쿼리 실행 결과가 한 개의 행 반환	= > >= < <= != 와 같은 비교 연산자
복수행 서브쿼리	서브쿼리 실행 결과가 복수 개의 행 반환	IN ANY ALL EXIST 연산자

 

단일행 서브쿼리

단일행 서브쿼리(single-row subquery)는 실행 결과가 단 하나의 행으로 나온다. 대표적으로 기본키 primary key를 이용하거나 MAX MIN SUM과 같은 그룹함수를 사용하여 검색하는 경우이다.

-- 사원들의 평균 월급보다 더 많은 월급을 받는 사원 조회
SELECT last_name,salary
FROM employees
WHERE salary >= (SELECT AVG(salary)
                 FROM employees);

-- 부서번호가 100인 사원들 중에서 최대 월급을 받는 사원과 동일한 월급을 받는 사원
SELECT last_name,salary
FROM employees
WHERE salary = (SELECT MAX(salary)
                FROM employees
                WHERE department_id=100);

 -- 부서번호가 100인 사원들의 최대 월급보다 많은 모든 부서 정보
 SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id -- 부서 번호로 그룹핑
HAVING MAX(salary) > (SELECT MAX(salary) -- 그룹 조건 지정
                      FROM employees
                      WHERE department_id=100 );

 

복수행 서브쿼리

종류	설명
IN	메인쿼리와 서브쿼리를 IN연산자로 비교
ANY	복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용되며 검색 조건이 하나라도 일치하면 참
ALL	복수행 서브쿼리에서 > 또는 < 같은 비교 연산자를 사용하고자 할 때 사용되며 검색 조건의 모든 값이 일치하면 참
EXIST	서브쿼리의 반환값이 존재하면 메인쿼리를 실행하고 없으면 실행하지 않는다.

 

IN 연산자

서브쿼리 반환값이 복수이며 메인쿼리와 동등 연산자 = 방식으로 비교할 때 사용

-- 이름이 'Whalen' 이나 'Fay' 사원과 같은 월급을 받는 모든 사원 
SELECT last_name, salary
FROM employees
WHERE salary IN ( SELECT salary
                FROM employees
                WHERE last_name IN ('Whalen','Fay') );

-- 월급이 13000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 모든 사원
SELECT last_name, department_id, salary
FROM employees
WHERE department_id IN ( SELECT department_id
                         FROM employees
                         WHERE salary > 13000 )

 

ALL 연산자

복수행 서브쿼리에서 >또는<같은 비교 연산자를 사용하고자 할 때 사용

•  > ALL(서브쿼리) : 서브쿼리에서 반환된 최대값 보다 큰 데이터 조회
• < ALL(서브쿼리) : 서브쿼리에서 반환된 최소값 보다 작은 데이터 조회

--  직업이 IT_PROG인 사원의 월급보다 많은 월급을 받는 사원 조회
SELECT last_name, department_id, salary
FROM employees
WHERE salary > ALL (SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG');

 

ANY 연산자

ALL 연산자와 비슷하지만 ANY 연산자는 서브쿼리에서 반환되는 행들 전체에 대해 조건이 하나 이상만 만족하면 된다.

• > ANY(서브쿼리) : 서브쿼리에서 반환된 최소값 보다 큰 데이터 조회
• < ANY(서브쿼리) : 서브쿼리에서 반환된 최대값 보다 작은 데이터 조회

-- 직업이 IT_PROG 인 사원의 최소 월급보다 많은 월급을 받는 사원 조회
SELECT last_name, department_id, salary
FROM employees
WHERE salary > ANY (SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG');

 

EXISTS 연산자

서브쿼리에서 실행된 결과가 하나라도 존재 하는지 여부를 확인할 때 사용한다. 서브쿼리에서 검색된 결과가 하나라도 있으면 메인쿼리에 전달된 값이 true이기 때문에 메인쿼리가 실행된다.

--  커미션을 받는 사원이 한 명이라도 있으면 모든 사원 정보를 출력
SELECT last_name, department_id, salary
FROM employees
WHERE EXISTS (SELECT employee_id
             FROM employees
             WHERE commission_pct IS NOT NULL)

---

다중컬럼 서브쿼리

다중 컬럼 서브쿼리는 서브쿼리에서 여러 개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리이다. 메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이 되어야 한다.

-- 부서별로 가장 많은 월급을 받는 사원정보를 출력
SELECT last_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN ( SELECT department_id, MAX(salary)
                                    FROM employees
                                    GROUP BY department_id )
ORDER BY 2;

 

연관 서브쿼리

연관 서브커리(Correlated)는 서브쿼리 내에 메인쿼리 컬럼이 사용된 서브쿼리이다. 

 

인라인 뷰 (in-line view)

인라인 뷰는FROM절에서 사용된 서브쿼리이다. 일반적으로 FROM절에는 테이블명이 와야 하지만 서브쿼리가 하나의 가상 테이블 형태로 사용될 수 있다. 동적 뷰(Dynamic View)라고도 한다.

-- 사원 테이블과 부서 테이블에서 부서별 월급 총합과 평균 그리고 부서별 인원수
SELECT e.department_id, 총합, 평균, 인원수
FROM ( SELECT department_id, SUM(salary) 합계, AVG(salary) 평균 ,COUNT(*) 인원수 
       FROM employees
       GROUP BY department_id ) e, departments d
WHERE e.department_id = d.department_id
ORDER By 1;

서브쿼리에서 부서번호(department_id)를 사용하여 그룹핑을 하고 월급이 총합, 평균, 부서별 인원수를 구한다. 서브쿼리를 사용하지 않고도 출력할 수 있지만 인라인 뷰를 이용하면 훨씬 더 적은 수의 데이터로 조인에 참여하기 때문에 매우 효율적으로 처리할 수 있다.

 

스칼라 서브쿼리 (Scalar subquery)

스칼라 서브쿼리는 SELECT 절에서 사용하는 서브쿼리이다. 단일행 서브쿼리라서 한 행만을 반환한다. 

💡 스칼라 서브쿼리의 결과가 2건 이상이면 오류

 

UPDATE문 SET절의 서브쿼리

서브쿼리를 사용한 변경 작업을 할 때, 서브쿼리 결과가 NULL을 반환하면 해당 컬럼의 결과가 NULL이 된다.