아래의 내용들은 JOIN 없이 서브 쿼리 만으로 가져오게 된다.
먼저 단일행 서브쿼리의 경우 서브쿼리의 결과가 여러 행이 아니라 단 하나의 행인 경우를 말한다.
따라서 WHERE 절 이후에 특정 필드명 = 서브쿼리 의 형태로 사용하게 된다.
SCOTT을 제외한 SCOTT과 같은 부서에서 근무하는 사원의 이름과 부서 번호
SQL> SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO = ( SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT' ) AND ENAME != 'SCOTT'; ENAME DEPTNO ---------- ---------- SMITH 20 JONES 20 ADAMS 20 FORD 20
SCOTT과 동일한 직급을 가진 사원
SQL> SELECT *
FROM EMP
WHERE JOB = ( SELECT JOB FROM EMP WHERE ENAME = 'SCOTT' );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
SCOTT의 급여와 동일하거나더 많이 받는 사원명과 급여
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL >= ( SELECT SAL FROM EMP WHERE ENAME = 'SCOTT' ); ENAME SAL ---------- ---------- SCOTT 3000 KING 5000 FORD 3000
DALLAS에서 근무하는 사원의 이름, 부서 번호
SQL> SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS' ); ENAME DEPTNO ---------- ---------- SMITH 20 JONES 20 SCOTT 20 ADAMS 20 FORD 20
SALES 부서에서 근무하는 모든 사원의 이름과 급여
SQL> SELECT ENAME, SAL FROM EMP WHERE DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES' ); ENAME SAL ---------- ---------- ALLEN 1600 WARD 1250 MARTIN 1250 BLAKE 2850 TURNER 1500 JAMES 950
자신의 직속상관이 KING인 사원의 이름과 급여
SQL> SELECT ENAME, SAL FROM EMP WHERE MGR = ( SELECT EMPNO FROM EMP WHERE ENAME = 'KING' ); ENAME SAL ---------- ---------- JONES 2975 BLAKE 2850 CLARK 2450
위에 단일행 서브쿼리들이 서브쿼리의 결과가 하나의 로우에 해당해서 결과값과 = 기호로 비교한다면,
다중행 서브쿼리의 경우 IN, ALL, SOME, ANY, EXIST 등의 연산자를 활용해서 매칭시킬 수 있다.
JOB이 MANAGER인 사람이 속한 부서의 부서 번호와 부서명과 지역을 출력해보자. 먼저 SELECT 문 다음 결과로 원하는 필드명인 부서번호, 부서명, 지역을 입력하고 WHERE 문 다음 부서가 특정 사람들에게 속한 경우를 찾으므로 DEPTNO 이후 JOB이 MANAGER인 사람들의 DEPTNO를 서브쿼리로 가져와서 IN 으로 매칭시킨다.
SQL> SELECT DEPTNO, DNAME, LOC
FROM DEPT
WHERE DEPTNO IN ( SELECT DEPTNO FROM EMP WHERE JOB = 'MANAGER' );
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
이번에는 그룹 함수와 연계한 문제라 조금 까다롭다.
IN 연산자를 이용하여 부서별로 가장급여를 많이 받는 사원의 사원번호, 사원명, 급여, 부서번호를 출력해 보자.
먼저 SELECT 문으로 필요한 필드명을 가져오고,
WHERE 문으로 SAL가 그룹별로 최대 연봉들의 그룹과 일치하는 경우를 매치시키게 된다.
책에는 아래와 같이 설명하고 있지만,, 사실 우연히 SAL가 달라서 그렇지 만약 다른 부서에서 우연히 각 부서 최대 급여와 일치하는 경우가 생길 수도 있는 것 같다. 따라서 다소 문제가 있는 쿼리인 듯 싶다.
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN ( SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO );
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7698 BLAKE 2850 30
7788 SCOTT 3000 20
7839 KING 5000 10
7902 FORD 3000 20
강사님께서 다시 바로잡아주신 내용.
DEPTNO와 SAL을 가져와서 이를 NATURAL JOIN 하여 결과를 가져오는 방식.
SQL> SELECT DEPTNO, MAX(SAL) AS SAL
FROM EMP
GROUP BY DEPTNO;
DEPTNO SAL
---------- ----------
30 2850
20 3000
10 5000
경 과: 00:00:00.01
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP NATURAL JOIN ( SELECT DEPTNO, MAX(SAL) AS SAL FROM EMP GROUP BY DEPTNO );
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7698 BLAKE 2850 30
7902 FORD 3000 20
7788 SCOTT 3000 20
7839 KING 5000 10
경 과: 00:00:00.07
ALL 함수는 다중행 서브쿼리의 결과 모두와 조건문으로 비교해서 모두 참인 경우를 찾을 수 있다.
아래는 직무가 SALESMAN인 모든 사람보다 연봉이 높은 사람의 이름과 연봉을 찾는 내용이다.
이름과 연봉을 EMP 테이블에서 가져옴을 선언하고
WHERE 문 아래에 서브쿼리를 통해 가져온 SALESMAN이 직무인 모든 사람들의 연봉과 SAL을 비교해서 더 큰경우만 출력하게 된다.
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL > ALL( SELECT SAL FROM EMP WHERE JOB = 'SALESMAN' ) AND JOB != 'SALESMAN'; ENAME SAL ---------- ---------- CLARK 2450 BLAKE 2850 JONES 2975 SCOTT 3000 FORD 3000 KING 5000
ANY와 SOME 은 다중행 서브쿼리의 결과 중 하나라도 조건문으로 비교해서 참인 경우를 찾을 수 있다.
아래의 예에서 ANY와 SOME의 결과가 동일한 것을 확인할 수 있다.
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL > ANY ( SELECT SAL FROM EMP WHERE JOB = 'SALESMAN' ) AND DEPTNO != 20; ENAME SAL ---------- ---------- KING 5000 BLAKE 2850 CLARK 2450 ALLEN 1600 TURNER 1500 MILLER 1300 6 개의 행이 선택되었습니다. SQL> SELECT ENAME, SAL FROM EMP WHERE SAL > SOME ( SELECT SAL FROM EMP WHERE JOB = 'SALESMAN' ) AND DEPTNO != 20; ENAME SAL ---------- ---------- KING 5000 BLAKE 2850 CLARK 2450 ALLEN 1600 TURNER 1500 MILLER 1300 6 개의 행이 선택되었습니다.