KOSA fullstack 교육(서브쿼리)
서브쿼리 (= Inner 쿼리)
반대어로 메인쿼리(=Outer 쿼리)라고 한다
- 서브 쿼리는 하나의 SQL문 안에 포함되어 있는 또다른 SQL문
- 서브쿼리는 ?에 해당하는 부분을 먼저 검색하는 데 사용된다
- 서브쿼리가 먼저 실행되고 그 결과를 사용해서 메인쿼리가 실행된다.
- 서브쿼리에서는 order by절이 의미없다
- 종류로는 WHERE/HAVING/FROM절이 있다.
예시) ADAMS의 급여보다 더 많은 급여를 받는 사원을 검색
단일행 연산 서브쿼리 예제
예제 1 ) 10번 부서에서 근무하는 사원들중에서 사원 전체의 평균급여보다 더 많은 급여를 받는 사원을 검색
SELECT ename, sal
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp) AND deptno = 10;
예제 2 ) 사원번호가 7521인 사원과 업무가 같고 급여가 7934인 사원보다 많은 사원의 사원번호, 이름, 업무, 입사일자, 급여를 검색
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);
예제 3 ) emp 테이블에서 급여를 가장 많이 받는 사원의 이름, 부서번호, 급여, 입사일 조회
SELECT ename, deptno, sal, hiredate
FROM emp
WHERE sal = (SELECT max(sal) FROM emp);
5000, 1300, 1100
SALESMAN
KING
... 와 같이
1ROW 1COLUMN 같은 값을 스칼라값 혹은 스칼라서브쿼리라고 한다.
서브쿼리 수행 결과로 스칼라값이 나오면 단일행 연산자를 사용해야 한다.
(=, !=,<>,>,<,>=,<=)
예제 4 ) emp 테이블에서 사원의 급여가 20번 부서의 최소 급여보다 많은 부서를 검색
HAVING 절 서브쿼리
SELECT deptno, min(sal)
FROM emp
GROUP BY deptno
HAVING min(sal) > (SELECT min(sal) FROM emp WHERE deptno = 20);
예제 5 ) 업무별 가장 적은 평균급여를 검색
-- HAVING 절 서브쿼리
-- STEP 1. MYSQL은 그룹함수 중첩이 안된다!!!
SELECT job, avg(sal)
FROM emp
GROUP BY job
HAVING avg(sal)=(SELECT min(avg(sal)) FROM emp GROUP BY job); -- Error
-- FROM 절 서브쿼리
-- STEP 2.
SELECT job, min(avgSal)
FROM (SELECT job, avg(sal) avgSal FROM emp GROUP BY job) e
GROUP BY job;
-- STEP 3.
SELECT job, avg(sal)
FROM emp
GROUP BY job
ORDER BY 2
LIMIT 1;
예제 6 ) SCOTT 의 급여보다 더 많은 급여를 받는 사원을 검색
-- STEP 1
SELECT ename, sal, job
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SCOTT');
-- FROM 절 서브쿼리
-- STEP 2
SELECT a.ename, a.sal, a.job
FROM emp a, (SELECT sal FROM emp WHERE ename = 'SCOTT') b
WHERE a.sal > b.sal;
예제 7 ) 직속 상관이 KING인 사원의 이름과 급여를 검색
SELECT ename, sal, mgr
FROM emp
WHERE mgr = (SELECT empno FROM emp WHERE ename = 'KING');
-- 테이블 알리아스 버전
SELECT ename, sal, mgr
FROM emp e, (SELECT empno FROM emp WHERE ename = 'KING') b
WHERE e.mgr = b.empno;
다중행 연산 서브쿼리 예제
IN을 써주지 않으면 에러남 !! 서브쿼리가 여러개가 나오기 때문에
SELECT empno, ename, sal
FROM emp
WHERE sal IN (SELECT min(sal) FROM emp GROUP BY deptno);
IN: 목록에 있는 임의의 값과 동일하면 참
ANY: 서브쿼리에서 반환된 각각의 값과 비교하여 하나라도 참이면 참
< ANY 는 최대값보다 적음을
> ANY 는 최소값보다 큼을
= ANY 는 IN 과 동일
ALL: 서브쿼리에서 반환된 모든 값과 비교하여 모두 참이어야 참
< ANY 는 최소값보다 적음을
> ANY 는 최대값보다 큼을 의미
예제 1 ) 급여가 어떤 CLERK보다도 적으면서 CLERK이 아닌 사원을 검색
SELECT sal FROM emp WHERE job = 'CLERK'; -- 800, 1100, 950, 1300
SELECT ename, sal, job
FROM emp
WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')
AND job <> 'CLERK';
예제 2 ) 급여가 10번 부서에 속한어떤 사원의 급여보다 많은 급여를 받는 사원을 검색
이때 10번 부서에 속한 사원은 제외하고 사원번호 순으로 정렬
SELECT ename, sal ,job
FROM emp
WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10
ORDER BY empno;
예제 3 ) 30번 부서에 속한 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원을 검색
사원번호 이름 급여... 정렬은 사원번호 순
-- 다중 행 함수 버전
SELECT empno, ename, sal
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30)
ORDER BY 1;
-- 그룹함수 버전 (단일 행 함수)
SELECT empno, ename, sal
FROM emp
WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
예제 4 ) 부서번호가 30번인 사원들 급여 중 가장 낮은 급여(950)보다 더 높은 급여를 받은 사원을 검색
-- 1) 그룹함수 + 단일 행 연산자 사용
SELECT empno, ename, sal, deptno
FROM emp
WHERE sal > (SELECT min(sal) FROM emp WHERE deptno = 30);
-- 2) 다중행 연산자 사용
SELECT empno, ename, sal, deptno
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);
예제 5 ) 부하 직원을 가지는 사원을 검색
SELECT empno, ename, job FROM emp
WHERE empno IN (SELECT mgr FROM emp );
-- self join ver
SELECT DISTINCT empno, ename, job
FROM emp e,(SELECT mgr FROM emp) b
WHERE e.empno = b.mgr;