서브쿼리
1. 서브쿼리의 개념
서브쿼리는 하나의 SQL 문 안에 포함되어 있는 또 다른 SQL 문을 의미합니다.
SQL 에 포함된 또 다른 SQL을 서브쿼리라고 합니다. 서브쿼리는 SELECT절, FROM절, WHERE절, HAVING절에 사용될 수 있습니다.
서브쿼리는 메인 쿼리가 실행되기 전에 먼저 실행되며 서브쿼리에서는 메인 쿼리의 모든 컬럼을 참조할 수 있지만 메인 쿼리에서는 서브쿼리의 컬럼을 참조할 수 없습니다.
2. 서브쿼리가 가능한 곳은 다음과 같다.
SELECT CLAUSE
FROM CLAUSE
WHERE CLAUSE
HAVING CLAUSE
ORDER BY CLAUSE
INSERT VALUES CLAUSE
UPDATE SET CLAUSE
3. 서브쿼리의 유형
(1) 단일 행(Sing-Row) 서브쿼리 : SELECT 문장으로 부터 오직 하나의 행 만을 검색하는 질의이다.
서브쿼리에서 한 행에 대한 결과 값만 반환하는 것을 단일행 서브쿼리라고 합니다 (=, >, >=, <, <=, <>, !=)
- WHERE 절에서 사용된 서브쿼리에서 한 개의 결과 값만 반환할 경우 사용하는 연산자는 다음과 같습니다. 이 외의 연산자를 사용할 경우 에러가 발생할 수 있습니다.
| 연산자 | 의미 |
|---|---|
| = | 같음 |
| > | 보다 큼 |
| >= | 크가나 같음 |
| < | 보다 작음 |
| <= | 작거나 같음 |
| , != | 같지 않음 |
SELECT select_list
FROM table_name
WHERE 표현식 연산자 (SELECT select_list
FROM table_name
WHERE 조건식);
표현식 연산자 : subquery는 비교 연산자 우측에 () 안에 작성한다.
▶ 예제
employee 테이블에서 gildong보다 급여를 많이 받는 사원을 출력하시오.
SELECT ename, salary
FROM employee
WHERE salary > (SELECT salary
FROM employee
WHERE ename = 'gildong') ;
20번 사원과 같은 부서에서 일하는 사원을 출력하시오.
SELECT ename, dno
FROM employee
WHERE dno = (SELECT dno
FROM employee
WHERE eno = 20) ;
(2) 다중 행(Multiple-Row) 서브쿼리 : SELECT 문장으로부터 하나 이상의 행을 검색하는 질의이다.
서브쿼리에서 여러 행에 대한 결과 값을 반환하는 것을 다중행 서브쿼리라고 합니다. (IN, NOT IN, ANY, ALL, EXISTS)
- 다중 행 subquery를 이용하여 데이터를 비교할 경우에는 다중 행 연산자를 사용합니다. 만약 다중 행 subquery에 단일 행 연산자를 사용한다면, 다음과 같은 오류가 발생합니다.
| 다중 행 연산자 | 설명 |
|---|---|
| IN(서브쿼리) | 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다. (Multiple OR 조건) |
| 비교연산자 ALL(서브쿼리) | 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. 비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 모든 결과 값을 만족해야 하므로, 서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족한다. ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다. |
| 비교연산자 ANY(서브쿼리) | 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. 비교 연산자로 ">"를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면되므로, 서브쿼리의 결과의 최소값보다 큰 모든 건이 조건을 만족한다. ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다. (SOME은 ANY와 동일함.) |
| EXISTS(서브쿼리) | 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않는다. |
▶ 예제1 (IN)
▶ 예제2 (ANY)
SALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ANY (SELECT sal
FROM emp
WHERE job='SALESMAN');
ENAME SAL
---------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
▶ 예제3 (ALL)
모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ALL (SELECT sal
FROM emp
WHERE job='SALESMAN');
ENAME SAL
---------- --------
CLARK 2450
BLAKE 2850
KING 5000
▶ 예제4 (EXISTS)
-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우
-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를
-- 체크해야 한다.
-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고
-- DISTINCT로 중복 제거를 한다.
SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE d.deptno = e.deptno;
-- EXISTS를 사용하는 Subquery로 변경
-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해
-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS
(SELECT 1
FROM emp e
WHERE e.deptno = d.deptno);
(3) 다중 열(Multiple-Column)[다중 컬럼] 서브쿼리 : SELECT 문장으로부터 하나 이상의 컬럼을 검색하는 질의이다.
서브쿼리에서 여러 열에 대한 결과 값을 반환하는 것을 다중열 서브쿼리라고 합니다. 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼 개수와 컬럼의 위치가 동일해야 한다.
▶ 예제
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)
FROM PLAYER
GROUP BY TEAM_ID)
ORDER BY TEAM_ID, PLAYER_NAME;
다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.
(4) FROM절상의 서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용 한다.
SELECT 의 FROM 절에서 사용하는 서브쿼리를 inline view라고 합니다. view와는 달리 inline view에서 사용하는 SELECT문을 저장하지 않기 때문에 dynamic view 라고도 부릅니다.
다른 서브쿼리와는 다르게 inline view에서 질의하는 칼럼들을 메인 서브쿼리에서 사용할 수 있습니다.
▶ 예제1
사원 정보와 각 사원이 속한 부서의 평균 급여를 함께 출력하라.
SELECT e.ename, e.salary, a.avg_sal
FROM employee e, (SELECT dno, AVG(salary) avg_sal FROM employee GROUP BY dno) a
WHERE e.dno = a.dno;
ENAME SALARY AVG_SAL
-----------------------------------------
HSCHOI 2000000 2150000
JHCHOI 2300000 2150000
HYCHOI 1700000 1340000
KWKIM 980000 1340000
YHBAE 4000000 2438250
…
▶ 예제2
부서번호 20의 평균 급여보다 크고, 부서번호 20에 속하지 않은 관리자를 조회하는 예제이다.
SELECT b.empno, b.ename, b.job, b.sal, b.deptno
FROM (SELECT empno
FROM emp
WHERE sal >(SELECT AVG(sal)
FROM emp
WHERE deptno = 20)) a, emp b
WHERE a.empno = b.empno
AND b.mgr is NOT NULL
AND b.deptno != 20;
EMPNO ENAME JOB SAL DEPTNO
----- --------- --------- ------- ---------
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
(5) SCALAR SUBQUERY
SELECT 절에서 사용하는 서브쿼리를 스칼라 서브쿼리(Scalar Subquery)라 한다.
스칼라 서브쿼리는 한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리를 말한다.
스칼라 서브쿼리는 메인 서브쿼리의 row수 만큼 반복되어 실행이 되고, 만약 조건에 만족하는 스칼라 서브쿼리의 결과가 없다면 NULL 을 리턴하게 된다.
▶ 예제
사원 정보와 각 사원이 속한 부서의 평균 급여를 함께 출력하시오.
SELECT ename, salary, (SELECT AVG(salary) FROM employee WHERE dno = e.dno) avg_sal
FROM employee e;
ENAME SALARY AVG_SAL
-------------------------------------------
HSCHOI 2000000 2150000
KSKIM 1800000 1800000
cp.) HAVING 절에서 서브쿼리 사용하기
HAVING 절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.
SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID
GROUP BY P.TEAM_ID, T.TEAM_NAME
HAVING AVG(P.HEIGHT) < (SELECT AVG(HEIGHT)
FROM PLAYER
WHERE TEAM_ID ='K02');
cp.) UPDATE문의 SET 절에서 사용하기
UPDATE TEAM A
SET A.STADIUM_NAME = (SELECT X.STADIUM_NAME
FROM STADIUM X
WHERE X.STADIUM_ID = A.STADIUM_ID);
cp.) INSERT문의 VALUES절에서 사용하기
- INSERT INTO 다음에 VALUES 절을 사용하는 대신에 서브 쿼리를 사용할 수 있습니다.
- 이렇게 하면 기존의 테이블에 있던 여러 행을 복사해서 다른 테이블에 삽입할 수 있습니다.
- 이 때 주의할 점은 INSERT 명령문에서 지정한 컬럼의 개수나 데이터 타입이 서브 쿼리를 수행한 결과와 동일해야 한다는 점입니다.
INSERT INTO PLAYER(PLAYER_ID, PLAYER_NAME, TEAM_ID)
VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1) FROM PLAYER), '홍길동', 'K06');
'Server > DBMS' 카테고리의 다른 글
| [DBMS] HAVING 조건 (0) | 2014.12.02 |
|---|---|
| [DBMS] Group By (0) | 2014.12.02 |
| [DBMS] NULL도 데이터이다 (0) | 2014.12.02 |
| [DBMS] 집합연산자 (0) | 2014.11.30 |
| [DBMS] 조인 (0) | 2014.11.30 |