서브쿼리

서브 쿼리

 

서브 쿼리란 SQL 문장 안에서 보조로 사용되는 또 다른 SELECT 문입니다.

 

최종 결과 출력 쿼리를 메인 쿼리이면 이를 보조하는 SELECT문이 바로 서브 쿼리 입니다.

 

서브 쿼리는 SELECT , FROM , WHERE절 모두 사용 가능하고

 

또한 INSERT UPDATE MERGE DELETE 문에서 사용 가능합니다.

메인 쿼리와의 연관성에 따라

-연관성 없는 서브 쿼리

-연관성 있는 서브 쿼리

 

형태에 따라

-일반 서브 쿼리(SELECT 절)

-인라인 뷰(FROM 절)

-중첩 쿼리(WHERE 절)

 

우선 연관성 없는 서브 쿼리에 대해 알아 봅시다.

 

연관성 없는 서브 쿼리는 메인 쿼리와의 연관성이 없는 서브 쿼리입니다.

 

즉 메인 테이블과 조인 조건이 걸리지 않습니다.

 

SELECT count(*)

FROM employees

WHERE salary>=(SELECT AVG(salary)

FROM employees);

 

이 쿼리는 전 사원 평균 급여 이상 받는 사원 수를 조회합니다.

 

메인 쿼리와 연관성 자체가 없습니다. 단일행 을 반환

 

SELECT count(*)

FROM employees

WHERE department_id IN ( SELECT department_id

FROM departments

WHERE parent_id IS NULL);

 

 

이 쿼리는 부서 테이블에 parent_id 가 널인 부서번호를 가진 사원 총 건수를 반환합니다.

 

마찬가지로 메인 테이블과 서브 쿼리는 연관성이 없습니다. 여러 행을 반환

 

SELECT employee_id, emp_name, job_id

FROM employees

WHERE (employee_id , job_id) IN (SELECT employee_id , job_id

FROM job_history);

 

job_history 테이블에 있는 employee_id job_id 와 같은 건을 사원 테이블에서 찾는 쿼리입니다.

 

마찬가지로 연관성 또한 없습니다. 동시에 2개 이상 컬럼 값이 같은 건을 찾고 있다는 점이 특징입니다.

 

또한 UPDATE, DELETE에서도 쓰일 수 있습니다.

 

UPDATE employees

SET salary=( SELECT AVG(salary)

FROM employees);

 

위 쿼리는 전 사원 급여를 평균 금액으로 업데이트 하는 겁니다.

 

DELETE employees

WHERE salary >= (SELECT AVG(salary)

FROM employees);

 

 

위 쿼리는 평균 급여보다 많이 받는 사원을 제거합니다.

 

 

이렇게 연관성이 없는 서브 쿼리에 대해 봤습니다.

 

두 번째로 연관성이 있는 서브 쿼리에 대해서 알아봅시다.

 

 

연관성이 있는 서브 쿼리란 메인 테이블과 조인 조건이 걸린 서브 쿼리를 말합니다.

 

SELECT a.department_id , a.department_name

FROM departments a

WHERE EXISTS( SELECT 1

FROM job_history b

WHERE a.department_id =b.department_id);

 

서브 쿼리 안에서 메인 쿼리에서 사용 된 테이블의 부서번호와 job_history의 부서번호 가 같은 건을 조회합니다.

 

EXISTS연산과 함께 서브쿼리 내 조인 조건이 포함돼있음을 나타냅니다.

 

따라서 이 쿼리는 job_history에 있는 부서만 조회가 되겠죠 ???

 

왜냐하면 해당 서브쿼리에서 테이블 기준으로 같은 것을 조회하여 출력하기 때문입니다.

 

SELECT a.employee_id,

(SELECT b.emp_name

FROM employees b

WHERE a.employee_id=b.employee_id) AS emp_name,

a.department_id,

(SELECT b.department_name

FROM department b

WHERE a.department_id=b.department_id) AS dep_name

FROM job_history a;

 

 

이 쿼리는 job_history를 조회하고 있습니다.

 

뽑는 값을 SELECT 서브쿼리로 구하게 됩니다. 왜냐하면 job_history로는 사원 이름 부서 이

 

름을 뽑지 못합니다.

 

첫 번째 서브 쿼리에서 사원 이름을 뽑는데 이는 사원 테이블과 job_hisotry 테이블의 id를

 

조인하여 사원 테이블에 있는 사원 이름을 출력 해주게 됩니다. 두 번째 서브 쿼리도 마찬 가지 입니다.

 

이렇게 SELECT 절에도 서브 쿼리를 넣을 수 있습니다.

 

또다른 쿼리를 봅시다.

 

SELECT a.department_id, a.department_name

FROM department a

WHERE EXISTS ( SELECT 1

FROM employees b

WHERE a.department_id=b.department_id

AND b.salary > (SELECT AVG(salary)

FROM employees)

  );

 

이 쿼리는 우선 사원 테이블에서 평균 급여를초과하는 사원을 걸러내고(연관성이 없는 서브쿼

 

리) 평균 급여초과를 받는 사원이 속한 부서가 존재하는(연관성이 있는 서브쿼리) 부서의 이

 

름과 id를 추출합니다.

 

연관성 있는 서브 쿼리 역시 UPDATE DELETE에서도 사용 가능합니다.

 

여러 응용을 살펴봅시다.

 

우선 부서 테이블 상위 부서 번호가 90에 속하는 사원들의 부서별 평균 급여를 조회 해봅시다.

 

SELECT department_id, AVG(salary)

FROM employees a

WHERE department_id IN ( SELECT department_id

FROM departments

 WHERE parent_id = 90)

GROUP BY department_id;

 

연관성 없는 쿼리로 우선 부서테이블에서 상위 부서 번호가 90인 부서 번호를 뽑아서 사원 테

 

이블에 있는 부서별 사원 급여 평균(AVG(salary)를 구합니다.

 

이 결과를 이용하여 상위 부서 90에 속하는 모든 사원 급여를 자신의 부서별 평균급여로 갱신

 

해 봅시다.

 

UPDATE employees a

SET a.salary = ( SELECT sal

FROM ( SELECT b.department_id, AVG(c.salary) as sal

FROM departments b

employees c

WHERE b.parent_id=90

AND b.department_id=c.department_id

GROUP BY b.department_id) d

WHERE a.department_id = d.department_id)

WHERE a.department_id IN (SELECT department_id

FROM departments

WHERE parent_id =90 ) ;

 

많이 어려워 보일 수도 있습니다. 하지만 각각 조각으로 나눠서 본다면 쉽습니다.

 

우선 큰 틀(빨간 부분 ) 을 보면 employees 테이블의 급여를 상위 부서 90인 사원들을 조건

 

으로 하여 갱신 합니다.

 

갱신할 값인 a.salary는 무엇이냐면 sal 값인데 이 값은 SELECT를 한 테이블(파란색 코드) 로부터의 평균 급여 입니다.

 

즉 이 SELECT 테이블은 우선 부서와 사원 테이블로 부터 상위 부서 90번이고 해당 부서에 속

 

하는 사원의 부서를 조건으로 하는 테이블 d입니다.

 

이 d에서 AVG(c.salary) 값을 통해 갱신 한다는 거죠.

 

이 테이블 d의 SELECT 조건(초록 부분 WHERE 절)으로는 마찬가지로 업데이트 시킬 사원 테

 

이블의 부서번호와 SELECT한 테이블의 부서번호(즉 갱신 할 부서) 같다는 조건이 들어갑니

 

다. 즉 상위 부서 90

 

이고 자기 부서의 각 사원들에 대해 평균 급여를 구하여 이를 sal을 통해 a.salary에 넣는

 

다는 것입니다.

 

이렇게 굉장히 복잡해 보이지만 나중가면 더욱 복잡한 쿼리문들이 있을겁니다.

 

빙산의 일각이라 생각하고 하나하나 차근차근 공부해보는 것도 괜찮은 것 같습니다.