16.서브쿼리 ② (스칼라·인라인뷰·상관 서브쿼리)

-- ============================================================ -- 【이해용 예제】 사원마다 자기 부서명을 붙이기 ★ -- ============================================================ SELECT E.ENAME AS 사원명, E.DEPTNO AS 부서번호, ( SELECT D.DNAME -- 스칼라 서브쿼리 (값 하나) FROM DEPT D WHERE D.DEPTNO = E.DEPTNO ) -- 바깥 E.DEPTNO 를 참조 (= 상관 서브쿼리) AS 부서명 FROM EMP E; -- 사원명 부서번호 부서명 -- ------ -------- ---------- -- SMITH 20 RESEARCH -- ALLEN 30 SALES -- CLARK 10 ACCOUNTING -- ... → 14건 -- -- ⭐ 각 사원 행마다 서브쿼리가 실행되어 그 사원의 부서명을 가져온다.

-- ============================================================ -- 【시험용 예제】 같은 결과를 '조인' 과 '스칼라 서브쿼리' 두 가지로 ★★ -- ============================================================ -- 방법 ① 조인 (13강) SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; -- 방법 ② 스칼라 서브쿼리 (같은 결과) SELECT E.ENAME, ( SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO ) AS DNAME FROM EMP E; -- → 두 방법 모두 사원명 + 부서명. 결과 동일.

-- ============================================================ -- 【시험용 예제】 스칼라 서브쿼리가 못 찾으면 NULL 을 반환한다 ★★ -- 존재하지 않는 부서번호(99)를 가진 사원이 있다고 가정하면 -- ============================================================ SELECT E.ENAME, ( SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO = E.DEPTNO ) AS 부서명 FROM EMP E; -- → 부서를 못 찾은 사원의 '부서명' 은 NULL 이 된다. -- -- ⭐ 이것은 INNER JOIN 처럼 '행이 사라지는' 게 아니라 -- LEFT OUTER JOIN 처럼 '사원은 남고 부서명만 NULL' 이 된다. (14강 회수) -- 스칼라 서브쿼리는 메인쿼리 행을 절대 줄이지 않는다.

-- ❌ 여러 행을 반환하면 오류 SELECT E.ENAME, ( SELECT SAL FROM EMP WHERE DEPTNO = E.DEPTNO ) AS 급여 -- 부서에 여러 명! FROM EMP E; -- ORA-01427: single-row subquery returns more than one row -- → 스칼라 서브쿼리는 값 '하나' 만 반환해야 한다. 집계함수(MAX 등)로 1건을 만들거나 -- 조건을 좁혀 1건이 나오게 해야 한다.

-- ============================================================ -- 【이해용 예제】 부서별 평균급여를 먼저 구하고, 그걸 조회 ★ -- ============================================================ SELECT V.DEPTNO AS 부서번호, V.평균급여 FROM ( SELECT DEPTNO, ROUND(AVG(SAL)) AS 평균급여 -- 인라인뷰 안에서 집계 + 별칭 FROM EMP GROUP BY DEPTNO ) V -- 이 결과 집합에 V 라는 별칭 WHERE V.평균급여 >= 2000; -- 임시 테이블처럼 조건을 건다 -- 부서번호 평균급여 -- -------- -------- -- 10 2917 -- 20 2175 -- -- ⭐ 인라인뷰 안에서 만든 별칭(평균급여)을 바깥에서 컬럼처럼 쓸 수 있다. -- ⚠️ 계산식 컬럼(AVG(SAL))에는 반드시 별칭을 붙여야 바깥에서 참조할 수 있다.

-- ============================================================ -- 【시험용 예제】 WHERE 서브쿼리와 달리 인라인뷰는 ORDER BY 가능 ★★ -- ============================================================ SELECT ROWNUM AS 순위, V.ENAME, V.SAL FROM ( SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC ) V -- 인라인뷰 안에서 정렬 (가능!) WHERE ROWNUM <= 3; -- 상위 3명 (Top-N 쿼리, 21강에서 자세히) -- 순위 ENAME SAL -- ---- ----- ----- -- 1 KING 5000 -- 2 SCOTT 3000 -- 3 FORD 3000 -- -- ⭐ 15강에서 "WHERE절 서브쿼리엔 ORDER BY 불가" 라고 했지만 -- 인라인뷰(FROM절)에서는 ORDER BY 를 쓸 수 있다. 이 차이가 시험에 나온다. -- ⭐ 이렇게 '정렬 후 상위 N개' 를 뽑는 것을 Top-N 쿼리라 한다 (21강).

-- ============================================================ -- 【시험용 예제】 각 부서에서 가장 급여가 높은 사원 ★★★ -- 15강 IN 방식의 한계(다른 부서 최고급여와 겹치면 부정확)를 해결한다 -- ============================================================ SELECT ENAME, SAL, DEPTNO FROM EMP E WHERE SAL = ( SELECT MAX(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO ); -- 바깥 E.DEPTNO 참조 = 상관! -- ENAME SAL DEPTNO -- ------ ----- ------ -- BLAKE 2850 30 -- SCOTT 3000 20 -- KING 5000 10 -- FORD 3000 20 → 4건 -- -- ⭐ 동작 방식 -- BLAKE(30번) 행 → 서브쿼리는 "30번 부서의 MAX(SAL)=2850" → BLAKE.SAL=2850 → 참 -- SMITH(20번) 행 → 서브쿼리는 "20번 부서의 MAX(SAL)=3000" → SMITH.SAL=800 → 거짓 -- 각 사원마다 '자기 부서 기준' 으로 판단하므로 정확하다. -- -- ⚠️ 15강 IN 방식과의 차이 -- 15강: SAL IN (5000, 3000, 2850) → 부서 구분 없이 이 값이면 통과 (부정확 가능) -- 16강: 자기 부서의 MAX 와만 비교 → 정확

-- ============================================================ -- 【이해용 예제】 소속 사원이 있는 부서만 (EXISTS) ★ -- ============================================================ SELECT D.DEPTNO, D.DNAME FROM DEPT D WHERE EXISTS ( SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO ); -- 바깥 D.DEPTNO 참조 = 상관 -- DEPTNO DNAME -- ------ ---------- -- 10 ACCOUNTING -- 20 RESEARCH -- 30 SALES → 3건 (40번 제외) -- -- ⭐ 각 부서 행마다 "그 부서에 사원이 있나?" 를 확인해 하나라도 있으면 통과.

-- ============================================================ -- 【시험용 예제】 소속 사원이 하나도 없는 부서 ★★ -- ============================================================ SELECT D.DEPTNO, D.DNAME FROM DEPT D WHERE NOT EXISTS ( SELECT 1 FROM EMP E WHERE E.DEPTNO = D.DEPTNO ); -- DEPTNO DNAME -- ------ ---------- -- 40 OPERATIONS → 1건 -- -- ⭐ NOT EXISTS 는 '존재하지 않으면 참'. NULL 비교를 하지 않아 -- 15강의 NOT IN + NULL 함정(0건 문제)을 원천적으로 피한다. -- -- 같은 결과를 NOT IN 으로 쓰면 위험하다 SELECT DEPTNO, DNAME FROM DEPT WHERE DEPTNO NOT IN ( SELECT DEPTNO FROM EMP ); -- EMP.DEPTNO 에 NULL 없으면 OK -- → 이 경우는 EMP.DEPTNO 에 NULL 이 없어 우연히 동작하지만, -- NULL 가능성이 있으면 NOT EXISTS 가 안전하다.