SQL - scott(개념 + 실습) 서브쿼리

서브 쿼리 : 쿼리문 안에 또 다른 쿼리문 존재

select 조회할 열
from 테이블명
where 조건식(select 조회할 열 from 테이블 명 where 조건식)

실행결과가 하나로 나오는 단일행 서브쿼리 사용 연산자(>, <=, =, <, <>, ~=,  != )

실행결과가 여러개로 나오는 다중행 서브쿼리 사용 연산자 : IN, ANY(SOME), ALL, EXISTS

 

1) JONES 사원의 급여보다 높은 급여를 받는 사원 조회

▶ JONES 사원의 급여

SELECT
    sal
FROM
    emp
WHERE
    ename = 'JONES'; 

==> 결과값 : 2975

SELECT
    *
FROM
    emp
WHERE
    sal > 2975;

▶ 위 두개의 구문을 합한 것

SELECT
    *
FROM
    emp
WHERE
    sal > (
        SELECT
            sal
        FROM
            emp
        WHERE
            ename = 'JONES'
    );

여러개로 문장으로 구성되어 있는 것들을 하나로 만들어서 결과값을 출력

 

2) ALLEN 사원의 추가 수당보다 많은 추가수당을 받는 사원 정보

SELECT
    *
FROM
    emp
WHERE
    comm > (
        SELECT
            comm
        FROM
            emp
        WHERE
            ename = 'ALLEN'
    );

 

3) 'WARD' 보다 빨리 입사한 사원정보 조회

SELECT
    *
FROM
    emp
WHERE
    hiredate < (
        SELECT
            hiredate
        FROM
            emp
        WHERE
            ename = 'WARD'
    );

실행결과가 하나로 나오는 단일행 서브쿼리 사용 연산자 : >, <=, =, <, <>, ~=,  != 

 

4) 20번 부서에 속한 사원 중 전체 사원의 평균 급여보다 높은 급여를 받는 사원정보와 소속 부서 정보를 조회

SELECT
    e.empno,
    e.ename,
    e.job,
    e.sal,
    d.deptno,
    d.dname,
    d.loc
FROM
         emp e
    JOIN dept d ON e.deptno = d.deptno
WHERE
        d.deptno = 20
    AND e.sal > (
        SELECT
            AVG(sal)
        FROM
            emp
    );

조인과 서브쿼리를 같이 사용 할 수 있음

 

※ IN : 메인 쿼리의 데이터가 서브쿼리 결과 중 하나라도 일치한 데이터가 있다면 true 

5) deptno가 20번이거나 30번 출력

SELECT
    *
FROM
    emp
WHERE
    deptno IN ( 20, 30 );

 

6) 각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력

select * 
from emp
where sal = (select max(sal)
          from emp group by deptno);

오류) 단일행 서브쿼리를 사용해서 오류 "=" 사용시 값이 하나만 나와야함. 

 

6-1) 오류 수정. = 대신 IN 사용

SELECT
    *
FROM
    emp
WHERE
    sal IN (
        SELECT
            MAX(sal)
        FROM
            emp
        GROUP BY
            deptno
    );

 

※ ANY(SOME) : 서브쿼리가 반환한 여러 결과 값 중에서 메인쿼리의 조건식 중 하나라도 TRUE라면 메인 쿼리 조건식을 true로 반환

7) 각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력

SELECT
    *
FROM
    emp
WHERE
    sal = ANY (
        SELECT
            MAX(sal)
        FROM
            emp
        GROUP BY
            deptno
    );   

"IN"  == "=ANY" 같은 출력문이 나옴.

 

8) 각 부서별 최고 급여보다 작은 급여를 받는 사원 정보 출력

SELECT
    *
FROM
    emp
WHERE
    sal < ANY (
        SELECT
            MAX(sal)
        FROM
            emp
        GROUP BY
            deptno
    );

 

9) 30번 부서 사원들의 최소 급여보다 많은 급여를 받는 사원 정보 출력

① min

select min(sal)
from emp
where deptno = 30;

 

② distinct

select distinct sal 
from emp
where deptno = 30;

SELECT
    *
FROM
    emp
WHERE
    sal > ANY (
        SELECT DISTINCT
            sal
        FROM
            emp
        WHERE
            deptno = 30
    );

서브쿼리에서 결과가 여러개 나올 경우 in이나 any를 사용 할 수 있음

• IN : 같다
• ANY(SOME)
  • = ANY : 같다
  • > ANY : 작다
  • < ANY : 크다

 

 

※ ALL : 서브쿼리가 반환한 여러 결과 값을 메인 쿼리의 조건식이 모두 만족시 true

10) 30번 부서 사원들의 최소 급여보다 적은 급여를 받는 사원 정보 출력

SELECT
    *
FROM
    emp
WHERE
    sal < ALL (
        SELECT DISTINCT
            sal
        FROM
            emp
        WHERE
            deptno = 30
    );   

 

 

※ 다중열 서브쿼리

11) 자신의 부서 내에서 최고 연봉과 동일한 급여를 받는 사원 출력

① 각 부서별 최고 연봉 

SELECT
    deptno,
    MAX(sal)
FROM
    emp
GROUP BY
    deptno;

결과가 두개 뜸. 그렇기 때문에 서브쿼리가 옴. select에 두개가 왔기 때문에 where절에 두개가 와야함

정리 후)

SELECT
    *
FROM
    emp
WHERE
    ( deptno, sal ) IN (
        SELECT
            deptno, MAX(sal)
        FROM
            emp
        GROUP BY
            deptno
    );

 

12) FROM 절에 사용하는 서브쿼리(인라인뷰)

SELECT
    e10.empno,
    e10.ename,
    e10.deptno,
    d.dname,
    d.loc
FROM
    (
        SELECT
            *
        FROM
            emp
        WHERE
            deptno = 10
    ) e10,
    (
        SELECT
            *
        FROM
            dept
    ) d
WHERE
    e10.deptno = d.deptno;

실습) 0번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원 정보, 부서정보를 출력(not in), 30번 부서의 직책 : 3개 출력

SELECT
    e.empno,
    e.ename,
    e.job,
    e.deptno,
    d.dname,
    d.loc
FROM
         emp e
    JOIN dept d ON e.deptno = d.deptno
WHERE
        e.deptno = 10
    AND job NOT IN (
        SELECT
            job
        FROM
            emp
        WHERE
            deptno = 30
    );

 

실습) 직책이 salesman 인 사람들의 최고급여보다 높은 급여를 받는 사원들의 사원 정보, 급여 등급 정보를 출력. EMPNO, ENAME, SAL, GRADE출력

① MAX() 쓰는 형태

SELECT
    empno,
    ename,
    sal,
    grade
FROM
         emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE
    sal > (
        SELECT
            MAX(sal)
        FROM
            emp
        WHERE
            job = 'SALESMAN'
    );

② MAX() 안 쓰는 형태

SELECT
    empno,
    ename,
    sal,
    grade
FROM
         emp e
    JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE
    sal > ALL (
        SELECT
            sal
        FROM
            emp
        WHERE
            job = 'SALESMAN'
    );