서브쿼리 예제

/* 서브쿼리 */

-- 1. SINGLE ROW : SELECT 결과가 단일행으로 이루어진다.

SELECT EMP_ID, EMP_NAME

FROM TEMP

WHERE SALARY = (SELECT MAX(SALARY) FROM TEMP);

SELECT EMP_ID, EMP_NAME, SALARY

FROM 

TEMP

WHERE 1=1

  AND SALARY >= (SELECT AVG(SALARY) FROM TEMP)

;

SELECT EMP_ID, EMP_NAME

FROM TEMP

WHERE DEPT_CODE IN (SELECT DEPT_CODE 

                    FROM TDEPT

                    WHERE AREA = '인천')

                    ;

                    

SELECT * FROM TCOM;

SELECT * FROM TEMP;

SELECT * FROM TDEPT;

SELECT DEPT_NAME, COUNT(A.EMP_ID)

FROM TEMP A,  TDEPT C

WHERE 1=1

  AND A.DEPT_CODE = C.DEPT_CODE  

  AND A.EMP_ID IN (SELECT EMP_ID FROM TCOM)

GROUP BY DEPT_NAME 

ORDER BY 1 ASC 

 ;

 

 

SELECT EMP_ID, EMP_NAME

FROM TEMP

WHERE (DEPT_CODE,EMP_ID) IN

       (SELECT DEPT_CODE, BOSS_ID FROM TDEPT)

;

-- 2.MULTI COLUMN SUBQUERY : 기본키 컬럼이 두 개 이상인 경우에 키 값을 한꺼번에 묶어서 비교하기 위해 사용

SELECT EMP_ID, EMP_NAME, SALARY

FROM TEMP

WHERE 1=1

  AND SALARY IN (SELECT MAX(SALARY) FROM TEMP GROUP BY LEV)

;

-- TEMP에서 부서별 최고 연봉금액을 읽어서, 해당부서와 최고 연봉금액이 동시에 일치하는 사원의 사번, 성명, 연봉을 읽어라.

SELECT EMP_ID, EMP_NAME, SALARY

FROM TEMP

WHERE 1=1

  AND (DEPT_CODE, SALARY) IN (SELECT DEPT_CODE, MAX(SALARY) FROM TEMP GROUP BY DEPT_CODE)

  ;

  

-- 3. ANY 와 ALL

SELECT EMP_ID, EMP_NAME, SALARY, LEV

FROM TEMP

WHERE SALARY > ANY (SELECT SALARY FROM TEMP WHERE LEV = '과장');

SELECT EMP_ID, EMP_NAME, SALARY 

FROM TEMP

WHERE 1=1

  AND SALARY > ALL(SELECT SALARY FROM TEMP WHERE LEV='과장');