MySQL 데이터베이스 이해하기 / 서브 쿼리

SubQuery

Query문 내에 작성하는 Query를 SubQuery라고한다.
기본적으로 외부 Query가 수행되기 전에 SubQuery가 먼저 수행되고 그결과를 외부 Query에서 사용한다. 

 

* 단 상호 연관 쿼리는 외부 Query한행에 대해 SubQuery가 수행되므로 SubQuery는 외부 Query의 행 수만큼 수행된다.

 

  - 종류

- where절에서 사용하는 단일행, 다중행, 다중열,상호 연관 subquery 
 => 상호 연관 서브쿼리는 외부 커리 개수 만큼 수행 //성능 나빠서 실전에서 사용 안함
- from절에서 사용하는 inline view
- 스칼라 서브쿼리(단일행, 단일열)로 조회되는 sub query

 

- sub query가 사용되는 위치 

select절, from절, where절, having절, order절       => group by절 빼고 
create table문, insert문, update문, delete문

- 규칙

 sub query를 작성시 ()내에 작성해야 한다. 

 

 

-  select절의 sub query(스칼라 서브쿼리)
- outer join한것과 같은 효과 

 

-- 사원번호, 이름, 급여, 부서번호, 부서명을 조회 

select EMPNO,ENAME,SAL,DEPTNO,DNAME , loc
from emp
left join dept
using (DEPTNO);

SELECT 
    EMPNO, ENAME, SAL, DEPTNO,
    (SELECT DNAME FROM dept d WHERE e.deptno = d.deptno) AS dnames,
    (SELECT loc FROM dept d WHERE e.deptno = d.deptno) AS loc
FROM emp e;

 -- 행마다 서브쿼리가 사용되어서 성능이 좋지 않음 

 

 where절 sub Query

sub query를 수행한 결과를 where절에서 비교 데이타로 사용 

- 종류

- 단일 행 서브쿼리  // 단일열 // 기본 스칼라
서브 쿼리로 부터 수행된 결과 행이 하나일 때
 비교 조건 =, >, >=, <, <=, != 

- 다중 행 서브쿼리 
서브 쿼리로 부터 수행된 결과 행이 둘 이상일 때
비교 조건 in, any, all

- 다중 열 서브쿼리 
서브 쿼리로 부터 수행된 결과 열이 둘 이상일 때 

-  상호 연관 서브쿼리 
외부 질의에서 사용 하는 컬럼을 subquery의 조건으로 사용 

 

단일 행 서브쿼리 

-- 사원번호가 7369인 사원의 업무와 같은 업무를 하는 사원의 모든 조회

 select *
 from emp
 where job = (select job from emp where empno =7369);

 

다중행 sub qeury

 - in 
     sub query를 통해 조회된 여러 데이타 중 하나와 일치하면 조회된다. 
 
  - all 
 >(>=) all  sub query를 통해 조회된 모든 데이타 보다 커야 한다. 
=> 가장 큰 값 보다 커야한다.
     <(<=) all  sub query를 통해 조회된 모든 데이타 보다 작아야 한다.
=> 가장 작은 값보다 작아야한다.

 - any
     >(>=) any  sub query를 통해 조회된 데이타 중 하나라도 커야 한다.  
=> 가장 작은 값보다 커야한다. 
     <(<=) any  sub query를 통해 조회된 데이타 중 하나라도 작아야 한다
=> 가장 큰값 보다 작아야 한다. 
     = any => in과 같으므로 잘 안씀.

 

-- 다음 관리자들이 관리하는 사원들의 모든 정보를 조회.
--  SCOTT, CLARK 

select *
from emp
where mgr in (select empno from emp where ENAME in('SCOTT','CLARK '));

 

-- 각 부서의 평균 급여들 보다 많이 받는 사원의 정보를 조회

select *
from emp
where sal >= all(select avg(sal) from emp group by EMPNO);

 

-- 급여 평균 중 가장 낮은 업무의 평균 급여보다 많이 받는 사원의 정보를 추출

select *
from emp
where sal > any(select avg(sal) from emp group by job);

 

다중 열 서브쿼리 (pairwise)

서브 쿼리를 통해 조회된 열이 두개 이상인 경우 
조회된 모든열과 일치해야 됨. 
형식]  where  (컬럼, 컬럼,..) 비교 조건(select 컬럼, 컬럼,.. from ~)

 

-- ADAMS 사원의 업무와 부서가 같은 사원의 정보를 조회하시오

: 스칼라 단일행으로 표현시 두번 작성 두개의 조건이 and로 묶였을 때 다중 열 사용 가능

select *
from emp
where job = (select job from emp where ename = 'ADAMS' )
	 and deptno =(select deptno from emp where ename = 'ADAMS');

 

: 다중 열 서브 쿼리

select *
from emp
where (job,deptno) = (select job,deptno from emp where ename = 'ADAMS' );

 

 from 절의 sub query(Inline View)

 sub query의 수행 결과가 임시 테이블로 사용이 됨. 

 

-- 사원이 소속된 부서의 평균 급여보다 많이 받는 사원의 정보를 조회

=> 속한 정보가 넘어가야 할 때 상호 연관 사용 가능 // 잘 쓰지 않음

-- 외부 지리의 서브쿼리가 하나 나와야 상호 연관 쿼리가 가능 // 한 행 씩 수행 매우 느림

SELECT *
FROM emp e
WHERE sal > (
    SELECT AVG(sal)        -- 해당 부서의 평균 급여
    FROM emp a
    WHERE e.deptno = a.deptno  -- 동일 부서로 비교
);

 

from 절의 sub query(Inline View)

 

SELECT empno, e.deptno, sal
FROM emp e,
     (SELECT deptno, AVG(sal) AS deptsal
      FROM emp
      GROUP BY deptno) d
WHERE sal > deptsal
  AND e.deptno = d.deptno;

 

-- 부서별 최저 임금을 받는 사원의 모든 정보를 조회 

select *
from emp e, (select deptno , min(sal) as minsal from emp a group by deptno) d
where e. deptno = d.deptno and sal = minsal;

 

 

N-top 

mysql : limit  시작숫자,개수
            시작 숫자는 0부터 
기타   : rownum
            시작 숫자는 1부터  // 

 

-- 급여가 높은 사원 5명 조회 

select *
from emp
order by sal desc
limit 0,5;

 

-- 급여가 높은 사원 6~10위까지 

-- oralce , db, ms-sql  

SELECT * 
FROM (
    SELECT rownum ro, a.*
    FROM (
        SELECT empno, ename, sal
        FROM emp
        ORDER BY sal DESC
    ) a
)
WHERE ro BETWEEN 6 AND 10;