7.26 서브쿼리

-- 서브쿼리
-- SQL문을 실행할 때 SQL 문 내부에서 사용하는 SELECT문

-- WHERE 조건절에서 사용할 경우

-- 메인쿼리
--SELECT 컬럼
--FROM 테이블
--WHERE 조건식 ( SELECT 컬럼
--                FROM 테이블
--                WHERE 조건식 )
                -- 서브쿼리

-- 존스씨의 급여를 확인                
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES';

-- 존스씨보다 급여를 많이 받는 사람을 조회
SELECT *
FROM EMP
WHERE SAL > 2975;

-- 서브쿼리로 SQL문 합치기
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
             FROM EMP
             WHERE ENAME = 'JONES');

desc emp;
-- 서브쿼리의 특징
-- 1. 서브쿼리는 같은 비교 조회 대상의 오른쪽에 놓이며 괄호로 묶어 사용
-- 2. 서브쿼리의 select 절에 명시한 컬럼은 비교 대상과 같은 자료형(data type)이어야 합니다.
--    같은 개수로 지정해야 합니다.
-- 3. 서브쿼리문의 결과 행 수(ROW COUNT)는 메인쿼리의 연산자 종류와 호환되어야 합니다.


-- EMP 테이블의 사원 이름이 ALLEN인 사원의 추가수당(COMM)보다 추가수당 많이 받는 직원을
-- 출력하는 SQL문을 작성해 보세요.
SELECT *
FROM EMP
WHERE COMM > (SELECT COMM
              FROM EMP
              WHERE ENAME = 'ALLEN');
              
-- 다중 행 서브쿼리 MULTIPLE ROW SUBQUERY

-- 다중행 연산자 : IN, ANY(SOME), ALL, EXIST

-- IN : 하나라도 일치한 데이터가 있으면 TRUE
-- ANY : 하나 이상이면 TRUE
-- ALL : 모두 만족하면 TRUE
-- EXIST : 결과가 존재하면 TRUE

SELECT * FROM EMP
WHERE DEPTNO IN (20, 30);

-- 각 부서별 최고 급여와 동일한 급여 받는 사원 정보
SELECT * FROM EMP
WHERE SAL IN ( SELECT MAX(SAL)
               FROM EMP
               GROUP BY DEPTNO );  -- IN ( 3000, 2850, 5000 )
               
-- ANY 연산자
SELECT * FROM EMP
WHERE SAL = ANY ( SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO );  -- = 연산자는 IN과 같은 효과

-- 30번 부서들의 최대 급여보다 적은 급여받는 사원 정보 출력
SELECT * FROM EMP
WHERE SAL < ANY ( SELECT SAL FROM EMP WHERE DEPTNO = 30 );  -- 기타 비교 연산 가능

SELECT * FROM EMP
WHERE SAL < ANY ( 1600, 1250, 1250, 2850, 1500, 950 );

SELECT * FROM EMP
WHERE SAL < ( SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 );

-- ALL 연산자 : 모든 결과가 조건식에 맞아 떨어져야 TRUE
SELECT * FROM EMP
WHERE SAL < ALL ( SELECT SAL FROM EMP WHERE DEPTNO = 30 );
             -- ( 1600, 1250, 1250, 2850, 1500, 950 )

SELECT SAL FROM EMP WHERE DEPTNO = 30;

-- EXISTS 연산자 : 존재하면 TRUE
SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 10);
-- 존재하지 않으면 아무 행도 출력되지 않음.
SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 50);


-- MULTIPLE-COLUMN SUBQUERY 다중열 서브쿼리
SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;
-- 서브 쿼리의 조회 결과 컬럼이 2개 이상인 경우, 각각 메인 쿼리 조건에 괄호로 묶어
-- 타입을 일치시킨 컬럼을 조회한다.

-- FROM 절에 사용하는 서브쿼리
-- VIEW랑 유사한 효과 : INLINE VIEW
SELECT *
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10,
     (SELECT * FROM DEPT) D
WHERE E10.DEPTNO = D.DEPTNO;

SELECT * FROM EMP WHERE DEPTNO = 10;  -- 10번 부서 직원만 나타내는 VIEW
SELECT * FROM DEPT;                   -- 부서 테이블을 가져오는 VIEW

-- WITH절을 사용하는 방법
WITH
    E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
    D AS (SELECT * FROM DEPT)
SELECT *
FROM E10, D
WHERE E10.DEPTNO = D.DEPTNO;

-- SELECT절에 사용하는 서브쿼리
-- SCALAR SUBQUERY, SELECT절의 하나의 컬럼으로서 결과를 출력 
-- JOIN 문을 사용하지 않고, 컬럼을 추가시키고 싶을 경우
SELECT EMPNO, ENAME, JOB, SAL,
    (SELECT GRADE
     FROM SALGRADE
     WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
     DEPTNO,
     (SELECT DNAME
      FROM DEPT
      WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
FROM EMP E;

-- EX3. 10번 부서에 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 사원정보
--      사원정보(EMP), 부서정보(DEPT)를 출력하는 SQL문

SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
    AND E.DEPTNO = 10
    AND JOB NOT IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);

-- EX4. 직책이 SALESMAN인 사람들의 최고급여(SAL)보다 높은 급여를 받는 사원 정보(EMP)
--      급여등급정보(SALGRADE)를 출력하는 SQL문
SELECT E.EMPNO, E.ename, E.sal,
    (SELECT GRADE FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL) AS GRADE
FROM emp E
WHERE SAL > ( SELECT MAX(SAL) FROM EMP WHERE job = 'SALESMAN' ); 

--단일행 서브쿼리 사용시
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
    AND SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;

SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
    AND SAL > ALL(1600,1250,1500)
ORDER BY E.EMPNO;

--다중행 서브쿼리 사용시
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
    AND SAL > ALL (SELECT DISTINCT SAL FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;

--EX5. SCALAR SUBQUERY를 이용해서 부서별 번호와 부서이름, 최고급여를 출력해보세요.
SELECT D.DEPTNO, D.DNAME, 
    (SELECT MAX(SAL)FROM EMP WHERE DEPTNO = D.DEPTNO) AS SAL 
FROM DEPT D;

--EX6.WITH절을 사용해서 직원명과 직원이 속한 부서의 인원수를 출력하세요.

WITH DEPT_COUNT AS(
SELECT DEPTNO, COUNT(*) AS DEPT_COUNT
    FROM EMP
    GROUP BY DEPTNO
)
SELECT ENAME, DEPT_COUNT
FROM EMP E, DEPT_COUNT DC
WHERE E.DEPTNO = DC.DEPTNO;