'mysql sub query'에 해당되는 글 1건

문법 : 

SELECT   select_list

FROM   table 또는 view

WHERE  조건   연산자  ( SELECT  select_list

      FROM  table

     WHERE  조건  ) ;

sub query 는 메인 쿼리 안에 포함되어  있는 또다른 별도 Select 문이다.

여러번의 Select문을 수행해야 얻을 수 있는 결과를 하나의 중첩된 Select 문으로 쉽게 얻을 수 있다.

sub query 가 먼저 실행되고, 그 결과가 메인 쿼리에 전달되어 실행된다.

두개의 테이블을 sub query 로 조인할 때에는 반드시 일치되는 공통 칼럼을 사용해야 한다.

- sub query 는 Select문의 select 절, FROM 절, Where절, Having 절에 올 수 있다.

  . select 절에 오는 sub query 는 스칼라 서브쿼리,

  . FROM 절에 오는 서브쿼리는 inline view 서브쿼리

- sub query 는 SELECT 문만 아니라 다른 문(INSERT, DELETE, UPDATE) 에도 쓸 수 있다

- sub query 에는 반드시 괄호를 사용한다.

- operator(연산자) 는 단일행 연산자 ( > , = , >= , < , <= , != )와

                              복수행 연산자 ( IN , ANY ,ALL,EXISTS )를 사용 할 수 있다.

- 단일행 sub query : sub query 의 결과가 1개의 행만 나오며, 반드시 단일행 연산자를 사용해야 한다. 

- 복수행 sub query : sub query 가 여러 개의 행을 리턴하며, 반드시 복수행 연산자를 사용해야 한다.
- sub query 에는 order by 를 사용할 수 없다.
- sub query 에는 NULL 값이 있으면 결과가 출력되지 않는다.

예제

단일행 sub query

-- 1. EMP 테이블에서 'SCOTT' 보다 급여를 많이 받는 사람의 이름과 급여를 출력

SQL> select ename, sal
from emp
where sal > (select sal from emp where ename = 'SCOTT');

-- 2. 사원테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여 출력
SQL> select empno, ename, sal
from emp
where sal > (select sal from emp where ename ='BLAKE');

-- 3. 사원테이블에서 MILLER 보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색
SQL> select empno, ename, hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'MILLER');
select hiredate from emp where ename = 'MILLER';

-- 4. emp 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934 보다 많은 사원의 사원번호, 이름, 담당업무, 입사일자, 급여를 출력
SQL> select empno, ename, job, hiredate, sal
from emp
where job = (select job from emp where empno=7521)
and sal > (select sal from emp where empno = 7934);

-- 5. 서브쿼리에서 그룹함수 사용
select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);

-- 6. 사원테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색
SQL> select empno, ename, sal
from emp
where sal > (select avg(sal) from emp);

-- 7. emp 테이블에서 20번 부서의 최소급여보다 많은 모든 부서의 최소급여 출력

--  having 절에서의 서브쿼리

SQL> select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);


복수행 sub query
- 복수행 서브쿼리 : IN, ALL, ANY
- 복수행 서브쿼리란 sub query 의 결과가 2건 이상 출력되는 것을 말한다.

- 메인 쿼리의 비교 조건이 sub query 의 결과 중에서 하나라도 일치하면 검색 가능

-- 1. emp 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호 출력
SQL> select empno, ename, job, hiredate, sal, deptno
from emp
where sal IN (select min(sal) from emp group by job)

order by 3;

-- 2. 사원테이블에서 업무가 MANAGER인 사원의 최소급여보다 적은 급여를 받는 사원들의 이름 검색
SQL> select empno, ename, job, sal, deptno
from emp
where sal < ALL (select sal from emp where job = 'MANAGER');

-- ALL 을 사용한 것과 min(sal)을 사용한 것이 결과는 동일하다.
SQL> select empno, ename, job, sal, deptno
from emp
where sal < (select min(sal) from emp where job = 'MANAGER');

-- 3. ANY : 최소값보다 큰, ALL : 최소값보다 작은
SQL> select empno, ename, job, sal, deptno
from emp
where sal > ANY (select sal from emp where job = 'MANAGER');

SQL> select empno, ename, job, sal, deptno
from emp
where sal > (select min(sal) from emp where job = 'MANAGER');

-- 4. EXISTS 연산자 : sub query 에서 검색된 결과가 하나라도 존재하면 메인 쿼리 조건이 참이 되는 연산

-- 사원중에서 comm 을 받은 사원이 한 명이라도 있으면 모든 사원 출력
SQL> select *
from emp 
where exists (select empno from emp where comm IS NOT NULL);

다중컬럼 서브쿼리 : pairwise (컬럼을 쌍으로 묶어서 동시에 비교하는 방식)

-- sub query 에서 여러 개의 컬럼 값을 검색하여 메인 쿼리의 조건절과 비교하는 sub query

--5-1. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력

-- 부서별 : group by 를 사용해야 한다는 의미, 최대 급여 : max(sal)
SQL> select deptno, empno, ename, sal
from emp
where (deptno, sal) IN (select deptno, max(sal) from emp group by deptno);

다중컬럼 서브쿼리 : unpairwise (칼럼별로 나누어 비교하고 나중에 AND 연산 처리하는 방식)

--5-2. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력
SQL> select deptno, empno, ename, sal
from emp
where deptno in (select deptno from emp group by deptno)
and sal in (select max(sal) from emp group by deptno);

-- 6. 사원테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색

-- 오류가 생길 수 있는 쿼리문
SQL> select empno, ename, deptno, sal
from emp
where sal in (select max(sal) from emp group by deptno);

-- 정확한 쿼리문
select empno, ename, deptno, sal
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);