[DataBase] 서브 쿼리 (Sub - Query)문

● 서브쿼리 - SQL> : Structured Query Language

   

① 서브 쿼리는 하나의 select 문장의 절 안에 포함된 또 하나의 select문 문장입니다. (메인 쿼리 / 서브 쿼리)

 

② Sub Query 부분은 WHERE 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 합니다.

 

③ 서브 쿼리는 메인 쿼리가 실행되기 이전에 한번만 실행이 됩니다.

 

④ 특별한 경우 (Top-n 분석 등)를 제외하고는 Sub Query 절에 Order by 절이 올 수 없습니다.

 

⑤ 단일 행 Sub Query 와 다중 행 Sub Query 에 따라 연산자를 잘 선택해야 합니다.

 

 

 

단일행 서브 쿼리와 다중행 서브 쿼리에 대해서 자세하게 예시를 통해서 설명하겠습니다.

 

1) 단일 행 서브쿼리 ( 결과 = 한개)

 

[예제1] scott와 동일한 직급(job)을 가진 사원을 출력하는 sql 문을 서브퀴리를 이용해서 작성해 보세요. 

select ename, job from emp where job = (select job from emp where ename = 'SCOTT');
select ename, job from emp where job -- 메인쿼리 
(select job from emp where ename = 'SCOTT') -- 서브쿼리
진행순서 서브쿼리 → 메인쿼리 → 출력 

 

[예제2]  scott의 급여와 동일하거나 더 많은 사원 이름과 급여를 출력해보세요.

select ename, sal from emp where sal > (select sal from emp where ename = 'SCOTT');
select ename, sal from emp where sal > -- 메인 쿼리 
(select sal from emp where ename = 'SCOTT'); -- 서브 쿼리 

 

[예제3] 전체 사원 평균 급여보다 더 많은 급여를 받는 사원을 출력하세요 [서브 쿼리 & 그룹 함수]

select ename, sal, job from emp where job > (select avg(sal) from emp);
select ename, sal, job from emp where job > -- 메인 쿼리
(select avg(sal) from emp); -- 서브 쿼리 / 그룹함수 avg를 활용하여 평균을 구합니다. 

* 그룹함수와 컬럼을 다이렉드로 연결 할 수 없습니다.

 

 

 

2) 다중 행 서브쿼리 ( 결과 = 2개 이상)

 

[예제1]  급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원들의 정보

select ename, sal, deptno from emp where dept = (select deptno from emp where sal >= 3000);--ERROR
A = B(10.20) - 2개의 값 
(복수의 값의 수를 가지고서는 비교 연산자가 이루어지지 않기 때문에 오류가 일어납니다.) 

 

다중 행 서브쿼리의 값을 출력하기 위해서는 추가적인 연산자가 필요합니다.

 

① in  연산자 

    : 메인 쿼리의 비교 조건 ('='연산자로 비교할 경우)이 서브쿼리의 결과 중에서 하나라도 일치(or)하면 참입니다. 

 

 in  연산자를 사용하여 위의 문제를 다시 풀어보겠습니다.

select ename, sal, deptno from emp where deptno in 
                                                (select deptno from emp where sal >= 3000);

비교 연산자를 대신하여 in 연산자를 사용하여 값을 출력하였습니다. 

 

[예제2]   in 연사자를 이용하여 부서별 가장 급여를 많이 받는 사원의 정보 (사원번호, 사원명,급여, 부서번호)를 출력하세요.

SELECT empno,ename,sal,deptno
from emp
where sal in 
(select max(sal) from emp group by deptno); 
그룹함수를 활용하여 각 부서의 최대월급을 출력합니다. 

 

 

② all 연산자

    : 메인 퀴리의 비교 조건이 서브퀴리의 검색 결과와 일치하면 참이다. 찾아진 값에 대해서 and 연산을 해서

     모두 참이면 참이 되는 셈입니다. >all 은 모든 비교 값도다 크냐 고 묻는 것이므로 최대값보다 크면 참이 됩니다. 

 

[예제1]  30번(부서번호) 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 출력하세요.

①
select enanme, sal from emp where sal > 
(select max(sal) from emp where deptno = 30); -- 단일행 서브쿼리& 그룹함수 

②
select ename, sal 
from emp 
where sal > all (select sal  
                         from emp  
                         where deptno =30); 
-- 각각의 급여를 받는 사람이 있었는데 그중에 제일 많이 받는 사람이 2850 달라였는데 
-- all 은 모든 값을 만족하는건 이기 때문에 최대값 보다 더 큰 결과를 도출 하고 싶을 때 사용합니다. 

 

[예제2]  영업사원(salesman)들보다 급여를 많이 받는 사원들의 이름과 급여를 출력하되 영업사원은 출력하지 않게 명령문을 작성해 보세요.

 

select job,ename, sal from emp where sal >all 
(select sal from emp where  job = 'SALESMAN')	-- 여기까기만 구해도 값은 같습니다. 
and job <> 'SALESMAN';  -- 하지만 조건의 가독성을 높이기 위해서 and문까지 처리해주면 좋습니다. 

 

③ any  연산자 

    : 메인 쿼리의 비교 조건이 서브쿼리의 검색 결과와 하나 이상만 일치하면 참입니다.
      > any는 찾아진 값에 대해서 하나라도 크면 참이 됩니다.

      그러므로 찾아진 값에서 가장 작은 값, 즉 최소값 보다 크면 참이 됩니다.   

 

[예제1]  부서번호가 30번인 사원들의 급여 중에서 가장 낮은 급여보다 높은 급여를 받는 사원의 이름, 급여를 출력하는 명령문을 작성해 보세요. 

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

select deptno,ename, sal from emp where sal > 
(select min(sal) from emp where deptno = 30)
order by sal desc;



다중행 서브쿼리 사용

select deptno,ename, sal, job from emp where sal > any 
(select sal from emp where deptno = 30);