서론
이번에는 조인 만큼 중요한 서브쿼리에 대해서 알아보겠다. 서브쿼리는 SELECT절에서 사용하는 스칼라 서브쿼리, FROM절에서 사용하는 인라인뷰, WHERE절에서 사용하는 서브쿼리가 있다. 간단히 말해 쿼리 안에 또 쿼리가 있는 것들을 보고 서브쿼리라고 한다.
본론
서브쿼리?
--Sub Query (서브쿼리) --1) Sub Query 란? --Query안에 또 다른 Query가 있는 경우의 SQL 문장을 의미함 --SELECT (SELECT ....) -- 스칼라 서브쿼리, 성능상 문제가 많은 쿼리 --FROM (SELECT ....) -- 인라인뷰 --WHERE (SELECT ....) -- 서브쿼리 --2)사용목적 --SQL을 작성할 때 질문이 여러 가지가 한꺼번에 나오는 경우 여러번 SQL을 작성함으로 인해 과도하게 --발생하는 서버에 I/O를 줄이기 위함 --[사용예제] --"Emp 테이블에서 SMITH 보다 급여를 많이 받는 사람이 누구일까?" 에 대한 SQL 작성시 --SMITH의 급여를 먼저 조회 한 후 그보다 급여를 더 많이 받는 사람을 한번 더 조회해야 함 SELECT ENAME, SAL FROM EMP WHERE SAL > = (SELECT SAL FROM EMP WHERE ENAME = 'SMITH'); --보통은 서브쿼리 수행 후 결과 값을 메인쿼리로 넘기고 나서 메인쿼리가 처리 된다. --Sub Query 주의 사항 --Sub Query 부분은 Where 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶여야 합니다. --특별한 경우 (Top-n 분석 등)를 제외하고는 Sub Query 절에 Order by 절이 올 수 없습니다. --단일 행 Sub Query와 다중 행 Sub Query 에 따라 연산자를 잘 선택해야 한다.
쿼리안에 쿼리가 있는 경우를 말한다. 참고로 위의 쿼리는 셀프 조인으로도 동일한 결과를 얻을 수 있다.
SELECT E1.ENAME, E1.SAL
FROM EMP E1, EMP E2
WHERE E2.ENAME = 'SMITH'
AND E1.SAL >= E2.SAL;
서브쿼리는 괄호안의 쿼리를 실행 후 그 결과 값을 메인 쿼리 넣는다 라고 생각하면 이해하기가 쉽다.
오다이바
--Multi Row Sub Query (다중 행 서브 쿼리)
--다중 행 Sub Query 란 Sub Query의 결과가 2건 이상 출력되는 경우를 의미하며 여러 건이 출력되기 때문에 단일 행 연산자를 사용할 수 없음
--IN 같은 값을 찾음
-->=ANY 최소값을 반환함
--<=ANY 최대값을 반환함
-->=ALL 최대값을 반환함
--<=ALL 최대값을 반환함
--EMP 테이블에서 이름이 S로 시작하는 사원의 연봉보다 큰 연봉을 갖는 사원의 이름, 직업, 연봉을 출력
SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE ENAME LIKE 'S%');
SELECT * FROM EMP WHERE SAL > ALL(SELECT SAL FROM EMP WHERE ENAME LIKE 'S%');
--다중컬럼 서브쿼리 : 동시에 두개 이상의 컬럼을 비교하고자 할때 사용
--(특히 group by에 의해 표현되지 못하는 추가적 정보를 얻고자 할때 주로 사용)
--professor 테이블에서 각 부서별 최소연봉을 갖는 교수의 이름, 교수번호, pay를 출력하세요
SELECT DEPTNO, MIN(PAY) FROM PROFESSOR GROUP BY DEPTNO;
--위의 쿼리를 이용하여 각 그룹에 대한 DEPTNO 정보 이외의 최소연봉을 갖는 교수가 누군지 알고자 할때
--어떻게 작성?
SELECT *
FROM PROFESSOR
WHERE PAY IN (SELECT MIN(PAY)
FROM PROFESSOR
GROUP BY DEPTNO)
AND DEPTNO IN(SELECT DEPTNO
FROM PROFESSOR
GROUP BY DEPTNO);
--각 그룹별 비교 없이 단순히 전체 교수중 학과별 MIN(PAY)값과 같은 PAY를 갖는 교수가 출력됨
SELECT *
FROM PROFESSOR
WHERE (DEPTNO,
PAY) IN (SELECT DEPTNO,
MIN(PAY)
FROM PROFESSOR
GROUP BY DEPTNO);
--EMP 테이블에서 직업별 최대연봉을 구하고, 직업별 최대연봉을 갖는 직원의 이름, 직업, 연봉을 구하세요.
SELECT * FROM EMP;
SELECT *
FROM EMP
WHERE (JOB,
SAL) IN (SELECT JOB,
MAX(SAL)
FROM EMP
GROUP BY JOB);
다중 행 서브쿼리는 서브쿼리 결과값의 행이 복수개라 비교가 안될 때 사용되는 쿼리를 말한다. 서브쿼리 결과값이 10, 20이라고 가정할 때 IN을 사용한다면 포함 되는 것이니 정상적으로 되겠지만 >나 <와 같은 연산에는 단일값만 비교할 수 있으므로 정상적으로 될리가 없다.
따라서 앞에 ANY나 ALL을 붙여 사용이 가능한데, ANY는 복수 값들 중에 어떠한 것이든 하나만 참일 경우, ALL은 모든 값과 비교할 때 참일 경우를 의미한다. 따라서 위에 ANY와 ALL에 대해서 최대값, 최소값을 반환한다라는 뜻은 결국에는 ANY는 어떠한 것이든 하나만 참일경우 이므로 ANY 보다 큰 것을 찾는다는 것은 서브쿼리의 최소값만 생각하면 되고 반대로 ANY 보다 작은 것을 찾는 다는 것은 서브쿼리의 최대값보다 작으면 모든게 참이기 때문에 위와 같이 표기를 하였다.
ALL 역시 모든 값과 비교를 하는 것이기 때문에 ALL보다 큰 것을 찾는다는 의미는 서브쿼리 결과값 중 최대값보다 크면 모두 참이기 때문에 최대값이라 표기하였고 ALL보다 작은 것을 찾는다는 것 역시 최대값 보다 작으면 모두 참이기 때문에 최대값이라 표기하였다.
서브쿼리의 결과값이 10, 20, 30 이라고 볼 때 ANY는 이 중 하나만 비교를 해서 참이면 참이고, ALL은 모두 맞을 경우에 참이다.
>=ANY(서브쿼리 결과 값 10, 20, 30)
결과값 중에 하나라도 크거나 같은 것 이기 때문에 10보다 크거나 같으면 모두 참이다.
<=ANY(서브쿼리 결과 값 10, 20, 30)
결과값 중에 하나라도 보다 작거나 같은 것 이기 때문에 30보다 작거나 같으면 모두 참이다.
>=ALL(서브쿼리 결과 값 10, 20, 30)
모든 결과값 보다 크거나 같은 것 이기 때문에 30보다 크거나 같다면 모두 참이다.
<=ALL(서브쿼리 결과 값 10, 20, 30)
모든 결과값 보다 작거나 같은 것 이기 때문에 30보다 작거나 같다면 모두 참이다.
혹은 IN을 사용하여 복수행, 복수컬럼이 포함 된 값들만 가져 올 수도 있다.
실습문제
--1. STUDENT 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요.
SELECT *
FROM STUDENT
WHERE (GRADE,
HEIGHT) IN (SELECT GRADE,
MAX(HEIGHT)
FROM STUDENT
GROUP BY GRADE);
--2. STUDENT 테이블과 DEPARTMENT 테이블을 이용하여 성별 별로 몸무게가 가장 많이 나가는 학생의
--이름과 성별, 몸무게, 학과명을 출력하세요.
SELECT * FROM STUDENT;
SELECT * FROM DEPARTMENT;
SELECT S.*,
D.DNAME
FROM STUDENT S,
DEPARTMENT D
WHERE (SUBSTR(S.JUMIN, 7, 1),
S.WEIGHT) IN (SELECT SUBSTR(JUMIN, 7, 1),
MAX(WEIGHT)
FROM STUDENT
GROUP BY SUBSTR(JUMIN, 7, 1))
AND S.DEPTNO1 = D.DEPTNO;
결론
WHERE 절에서 사용하는 서브쿼리에 대해 알아보았다. WHERE절에서 사용되는 서브쿼리는 보통 다중 행보단 단일 행의 한 컬럼 값에 대해서 사용이 되는 것 같다. 다음에 다루어볼 인라인뷰를 사용하면 더 쉽게 사용 할 수 있으니 참고하자.
