Subquery

서브쿼리: 쿼리 안에 쿼리 있는 형태. 주로 WHERE 안에서 쓰임. SELECT문이 또 안에 있는 형태

 

예시)

 

 

IN 연산자 씀 (메인 데이터가 서브 결과에 하나라도 참이 있으면 TRUE) 

 ANY, SOME, ALL, EXIST <- 이거 실무에서 안 씀(잘못 뽑는 경우 多)

 

 

예제)

 

서브 쿼리에 여러 칼럼 지정. (메인쿼리와 비교하는 칼럼은 동일 개수, 자료형을 괄호로 묶어 지정)

 

IN-LINE VIEW: FROM 절에 별칭을 부여해 테이블처럼 사용하는 서브쿼리

 

 

Scalar subquery라 불림. 하나의 칼럼 영역으로 사용되어 1개 결과 반환한다.

 

 

 

문제를 풀어보자

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO 
   AND JOB = 'SALESMAN'
 order by E.SAL desc, E.ENAME;
 
 -- 기존 조인과 and를 이용하고 서브쿼리를 사용하지 않은 방법이다.

SELECT E.JOB, E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND JOB = (SELECT JOB                    -- <- 서브쿼리가 들어가 달라진 부분이다.
                FROM EMP
               WHERE ENAME = 'ALLEN')
order by E.SAL desc, E.ENAME;

-- 서브쿼리를 이용한 방식

서브쿼리가 컴퓨터 메모리 등 사양을 먹기 때문에, 가능하면 서브쿼리를 안쓰고 짜는게 가장 좋다.

 

 

 

 

 

 

-- 문제 해결을 위해 먼저 평균 급여를 알아보자

SELECT TRUNC(AVG(SAL)) FROM EMP E;   -- TRUNC로 깔끔한 출력을 유도하자

--결과값은 2073으로 나온다.
--이걸 통째로 쓰면 서브쿼리, 2073만 따로 쓰면 서브쿼리를 쓰지 않는 코드짜기가 된다.

SELECT E.EMPNO, E.ENAME, D.DNAME,
TO_CHAR(E.HIREDATE, 'YYYY-MM-DD') as HIREDATE,
D.LOC, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL and S.HISAL   -- GRADE 조건을 제대로 출력하기위한 필수 조건. 비등가조인
AND E.SAL > 2073         -- 따로 값을 구해서 서브쿼리 생략.
ORDER BY E.SAL desc, E.EMPNO;

SELECT E.EMPNO, E.ENAME, D.DNAME,
TO_CHAR(E.HIREDATE, 'YYYY-MM-DD') as HIREDATE,
D.LOC, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND SAL > (SELECT AVG(SAL)    -- 서브쿼리 구간
                FROM EMP)
ORDER BY E.SAL DESC, E.EMPNO;

 

 

 

 

 

서브쿼리 없이 푸는 답지는 아래와 같다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D
where E.DEPTNO = D.DEPTNO -- 조인으로 인한 중복을 제거하기위한 필수코드
AND E.DEPTNO = 30; 
-- 먼저 30번 부서에 직책이 무엇이 있는지를 확인한다 -> SALESMAN, CLERK, MGR이 있음을 확인

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC FROM EMP E, DEPT D
where E.DEPTNO = D.DEPTNO
AND E.DEPTNO = 10 -- 문제 조건에 맞춰 10번으로 제한
AND E.JOB NOT in ('SALESMAN', 'MGR', 'CLERK'); -- NOT IN으로 조건을 맞춘다.

서브쿼리를 의도한 답안은 아래와 같다.

SELECT E.EMPNO, E.ENAME, E.JOB, E.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    -- 30에 없는 비중복 JOB 선택 서브쿼리
                     FROM EMP
                    WHERE DEPTNO = 30);   -- 서브쿼리를 의도한 답안

 

 

 

 

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