[MySQL] #14 서브 쿼리

/*
서브쿼리란 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말함
    서브쿼리는 ?에 해당하는 부분을 먼저 검색하는데 사용된다.
    일반적으로 서브쿼리가 먼저 실행되고 그 결과를 사용하여 메인 쿼리에 대한 질의 조건이 완성된다.
    서브쿼리에서 ORDER BY절을 사용해도 되지만 의미없다. -> 하지마
    WHERE절 / HAVING절 / FROM절에서 사용되는 서브쿼리
    
SELECT절의 서브쿼리
    스칼라 서브쿼리
    FROM절의 서브쿼리
    인라인 서브쿼리
    WHERE절의 경우 서브쿼리의 결과에 따라
    값 하나 -> 단일행 서브쿼리
    값 여러 개 -> 다중행 서브쿼리
*/

-- CLARK의 급여보다 많은 급여를 받는 사원을 조회

SELECT ename, sal
FROM emp
WHERE sal > (
SELECT sal
            FROM emp
            WHERE ename = 'CLARK'
            );

        

/*
단일행 비교 연산자 사용 :: =, >, <, !=, <>
    단일행 서브쿼리에서 사용됨
*/
-- ename이 KING인 사원과 같은 부서에서 근무하는 사원을 검색

SELECT *
FROM emp
WHERE deptno = (
SELECT deptno
                FROM emp
                WHERE ename = 'KING'
                );

-- job별 가장 적은 평균급여 검색
-- MySQL 그룹함수 중첩 안됨 / Oracle은 가능

-- 테이블 리턴하는 서브쿼리는 반드시 Alias를 지정해야 한다.

 SELECT job, min(avgSal)
 FROM (SELECT job, avg(sal) avgSal
FROM emp
        GROUP BY job) a;

-- 이 경우 서브쿼리를 사용하지 않고 LIMIT 사용해서 더 좋은 쿼리 만들 수 있음

SELECT job, AVG(sal) 평균급여
FROM emp
GROUP BY job
ORDER BY 평균급여
LIMIT 1;

-- SCOTT의 급여보다 더 많은 급여를 받는 사원을 검색 

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

-- (테이블 Alias 사용)

SELECT *
FROM emp main, (
SELECT sal
                FROM emp
                WHERE ename = 'SCOTT'
                ) sub
WHERE main.sal > sub.sal;

-- job이 사원 7369의 업무와 같고, 급여가 사원 7876보다 많은 사원을 검색

SELECT *
FROM emp
WHERE job = (
 SELECT job
             FROM emp
             WHERE empno = '7369'
             )
 AND sal > (
 SELECT sal
             FROM emp
             WHERE empno = '7876'
             );

-- 직속상관이 KING인 사원의 이름과 급여, mgr을 검색

SELECT ename, sal, mgr
FROM emp
WHERE mgr = (
 SELECT empno
             FROM emp
             WHERE ename = 'KING'
             );

 

 

/*
다중행 비교 연산자 사용 :: IN, ANY, ALL
    다중행 서브쿼리에서 사용됨
    ANL: 메인쿼리의 비교조건이 서브쿼리의 검색결과와 하나이상만 일치하면 참이 됨.
    ALL: 메인쿼리의 비교조건이 서브쿼리의 검색결과와 모든 값이 일치하면 참이 됨.
    IN: 여러 개 중에서 같은 값을 찾음
*/

-- 부서별 최소 급여를 받는 직원 정보를 조회

SELECT empno, ename
FROM emp
WHERE sal in (
 SELECT min(sal)
             FROM emp
             GROUP BY deptno);

-- 급여가 어떤 CLERK(점원) 보다도 작으면서 CLERK이 아닌 사원을 검색

SELECT empno, ename, sal, job
FROM emp
WHERE sal < ANY (
 SELECT sal
             FROM emp
             WHERE job = 'CLERK'
             )
AND job != 'CLERK';

-- 단일행으로도 작성 가능

SELECT empno, ename, sal, job
FROM emp
WHERE sal < (
 SELECT max(sal)
             FROM emp
             WHERE job = 'CLERK'
             )
AND job != 'CLERK';

-- sal < ANY: 최대값보다 작은 sal
-- sal < ALL: 최소값보다 작은 sal

-- 30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름과 급여를 검색
-- 다중행 서브쿼리 사용

SELECT ename, sal
FROM emp
WHERE sal > ALL (
SELECT sal
FROM emp
WHERE deptno = 30
);

-- 단일행 서브쿼리 사용              

SELECT ename, sal
FROM emp
WHERE sal > (
SELECT max(sal)
FROM emp
WHERE deptno = 30
);

        


-- 부서번호가 30번인 사원들의 급여 중 가장 낮은 급여(950)보다 높은 급여를 받는 사원의 이름, 급여 조회
-- 단일행 서브쿼리 사용    

SELECT ename, sal
FROM emp
WHERE sal > (
 SELECT min(sal)
             FROM emp
             WHERE deptno = 30
             );

-- 다중행 서브쿼리 사용

SELECT ename, sal
FROM emp
WHERE sal > ANY(
 SELECT sal
             FROM emp
             WHERE deptno = 30
             );

 

 

-- 부하직원을 거느린 사원을 검색

SELECT ename
FROM emp
WHERE empno IN (
		SELECT mgr
                FROM emp
                );

-- 부하직원을 거느리지 않은 사원을 검색
-- NOT IN의 경우 AND로 연결, NULL 처리 신경써야 됨

SELECT ename
FROM emp
WHERE empno NOT IN (
		SELECT mgr
                FROM emp
                WHERE mgr IS NOT NULL
                );