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'
);
