SubQuery 이용하기

  • 서브쿼리(SubQuery)란, SELECT한 결과를 조건 비교에 이용하거나 UPDATE 또는 DELETE 등에 사용할 때 이용하거나, SELECT한 결과를 조건으로 이용하여 다시 SELECT를 하는 것처럼 DML 안에 다시 SELECT 구문이 포함되는 형태를 말한다.
  • 서브쿼리는 여러 절에서 사용 가능하다.

 

가이드라인

  • 서브쿼리는 괄호( )로 묶어야 한다.
  • 두 종류의 비교 연산자들이 서브쿼리에 사용된다.
    1. =, >, >=, <, <=, <>, !=
    2. IN, NOT IN, ANY
  • 서브쿼리는 연산자의 오른쪽에 나타나야 한다.
  • 서브쿼리는 많은 SQL 명령에서 사용 가능하다.
  • 서브쿼리는 ORDER BY 절을 포함할 수 없다.

 

SubQuery를 사용할 수 있는 절

  • WHERE, HAVING, UPDATE
  • INSERT 구문의 INTO
  • UPDATE 구문의 SET
  • SELECT의 FROM절

 

예제

emp 테이블에서 SCOTT의 급여보다 많은 사원의 사번, 이름, 업무, 급여를 출력하세요.

 

[SubQuery 사용 전]

SELECT SAL FROM EMP
WHERE ENAME='SCOTT'; --SCOTT의 급여: 3000 출력됨

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL > 3000;

 

[SubQuery 적용]

SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='SCOTT'); --(): 서브쿼리

 

SubQuery의 유형

  1. 단일행 서브쿼리: SELECT 문장으로부터 오직 하나의 행만을 검색하는 질의
  2. 다중행 서브쿼리: SELECT 문장으로부터 하나 이상의 행을 검색하는 질의
  3. 다중열 서브쿼리: SELECT 문장으로부터 하나 이상의 열을 검색하는 질의

1. 단일행 서브쿼리

SELECT 문장으로부터 오직 하나의 행만을 검색하는 질의

--EMP 테이블에서 급여의 평균보다 적은 사원의 사번, 이름, 업무, 급여, 부서번호를 출력하세요.
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL < (SELECT AVG(SAL) FROM EMP) --AVG: 2073.2
ORDER BY SAL DESC;

--EMP 테이블에서 사원의 급여가 20번 부서의 최소급여보다 많은 사원정보를 출력하세요.
SELECT *
FROM EMP
WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20) --MIN: 800
ORDER BY SAL;

 

 

2. 다중행 서브쿼리

  • 한 개 이상의 행을 반환하는 서브쿼리
  • 다중행 서브쿼리 연산자가 별도로 있다.
    1. IN
    2. ANY
    3. ALL
    4. EXISTS

1) IN 연산자

  • Multiple OR 조건
  • 서브쿼리의 모든 결과값에 대해 각각 일치하는 값이 있는지 체크한다.
SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB;

--서브쿼리: <IN 연산자>
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE (JOB, SAL) IN (SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB);

 

2) ANY 연산자

서브쿼리 결과값 중 어느 하나라도 만족하면 결과를 반환한다.

SELECT SAL FROM EMP WHERE JOB='SALESMAN'

--서브쿼리: <ANY 연산자>
SELECT ENAME, SAL, JOB
FROM EMP
WHERE DEPTNO <> 20 AND SAL > ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN');

SAL이 1600보다 크거나, 1250보다 크거나, 1500보다 큰 값을 반환한다. 결국에는 최솟값인 1250보다 큰 값들을 출력하게 된다.

 

3) ALL 연산자

서브쿼리의 결과에 존재하는 모든 값을 만족하는 결과를 반환한다.

--서브쿼리: <ALL 연산자>
SELECT ENAME, SAL, JOB
FROM EMP
WHERE DEPTNO <> 20 AND SAL > ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN');

SAL이 1600보다 크고, 1250보다 크고, 1500보다 큼 => 3가지 조건을 모두 충족하는 값을 반환한다. 결국에는 최댓값인 1600보다 큰 값들을 출력하게 된다.

 

4) EXISTS 연산자

서브쿼리 결과 데이터가 존재하는지의 여부를 따져서 존재하는 값들만 결과로 반환한다.

--예제) 사원을 관리할 수 있는 사원의 정보를 보여주세요.
SELECT EMPNO, ENAME, JOB
FROM EMP E
WHERE EXISTS (SELECT EMPNO FROM EMP WHERE E.EMPNO = MGR);

 

3. 다중열 서브쿼리

  • 다중열 서브쿼리란 결과값이 두 개 이상의 컬럼을 반환하는 서브쿼리를 의미한다.
  • 서브쿼리가 한 번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 준다.
SELECT JOB, MIN(SAL)
FROM EMP
GROUP BY JOB;

 

--다중열 서브쿼리
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE (JOB, SAL) IN (SELECT JOB, MIN(SAL) FROM EMP GROUP BY JOB)
ORDER BY JOB;

 

UPDATE에서 서브쿼리

UPDATE 테이블명
SET 컬럼명=값1, 컬럼명2=값2
WHERE 조건절

 

--고객 테이블에 있는 고객 정보 중 마일리지가 가장 높은 금액을 가지는 고객에게
--보너스 마일리지 5000점을 더 주는 SQL을 작성하세요.
UPDATE MEMBER SET MILEAGE=MILEAGE+5000
WHERE MILEAGE = (SELECT MAX(MILEAGE) FROM MEMBER);

--고객 테이블에서 마일리지가 없는 고객의 등록일자를 
--고객 테이블의 등록일자 중 가장 뒤에 등록한 날짜에 속하는 값으로 수정하세요.
UPDATE MEMBER SET REG_DATE = (SELECT MAX(REG_DATE) FROM MEMBER)
WHERE MILEAGE = 0;

 

DELETE에서 서브쿼리

DELETE FROM 테이블명
WHERE 조건절

 

[예제1]

--상품 테이블에 있는 상품 정보 중 공급가가 가장 큰 상품은 삭제시키는 SQL문을 작성하세요.
DELETE FROM PRODUCTS
WHERE INPUT_PRICE = (SELECT MAX(INPUT_PRICE) FROM PRODUCTS);

 

 

[예제2]

SELECT EP_CODE_FK, MIN(OUTPUT_PRICE)
FROM PRODUCTS
GROUP BY EP_CODE_FK;

 

--상품 테이블에서 상품 목록을 공급 업체별로 정리한 뒤,
--각 공급업체별로 최소 판매 가격을 가진 상품을 삭제하세요.
DELETE FROM PRODUCTS
WHERE (EP_CODE_FK, OUTPUT_PRICE) IN (
SELECT EP_CODE_FK, MIN(OUTPUT_PRICE) FROM PRODUCTS
GROUP BY EP_CODE_FK
);

 

 

FROM절에서 서브쿼리 (Inline View)

  • 서브쿼리는 FROM절에서도 사용 가능하다.
  • 하나의 테이블에서 자료의 양이 많을 경우 FROM절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수 있다. 이런 경우 필요한 행과 열만 선택하여 FROM절에 기술하면 오라클 서버가 최적화 단계에서 효율적인 검색을 할 수 있다.
  • FROM절에 기술한 서브쿼리는 마치 View와 같은 역할을 한다. 이런 것을 인라인 뷰(Inline View)라고 한다.
  • 뷰(View): 저장장치 내에 물리적으로 존재하지는 않지만, 사용자에게 있는 것처럼 보여지는 가상의 테이블

 

[예제]

--EMP와 DEPT 테이블에서 업무가 MANAGER인 사원의 이름, 업무, 부서명, 근무지를 출력하세요.
--<JOIN문을 사용할 경우>
SELECT ENAME, JOB, DNAME, LOC
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE JOB = 'MANAGER';

--<서브쿼리를 이용할 경우>: 데이터가 많을 경우 효율적임
SELECT ENAME, JOB, DNAME, LOC FROM
(SELECT * FROM EMP WHERE JOB='MANAGER') A JOIN DEPT D
ON A.DEPTNO = D.DEPTNO;

 

 

기타 함수

RANK OVER

RANK() OVER (분석절): 분석절을 기준으로 랭킹(순위)을 매기는 함수

--사원의 급여가 많은 순서대로 랭킹을 매겨보자.
SELECT RANK() OVER(ORDER BY SAL DESC) AS RNK, EMP.*
FROM EMP;

 

--급여가 많은 사원 TOP5를 뽑아 출력
SELECT * FROM(
SELECT RANK() OVER(ORDER BY SAL DESC) AS RNK, EMP.*
FROM EMP) A
WHERE A.RNK <=5;

 

 

ROWNUM

  • 오라클에서 제공하는 가상의 컬럼
  • SELECT 해온 데이터에 일련번호(행번호)를 붙인다.
SELECT ROWNUM RN, PRODUCTS.* FROM PRODUCTS ORDER BY PNUM DESC;
--=> 행번호를 먼저 매긴 뒤 ORDER BY를 함

 

SELECT ROWNUM RN, A.* FROM
(SELECT * FROM PRODUCTS ORDER BY PNUM DESC) A
WHERE RN BETWEEN 1 AND 5; --WHERE절에서 RN(별칭) 사용 불가
--FROM절에서 사용한 별칭은 어디서나 사용 가능하지만, SELECT절에서 사용한 별칭은 바깥에서 사용 불가

오류 발생

 

--=> 정렬을 먼저 한 뒤, 행번호를 매기려면 아래와 같이 서브쿼리 사용
SELECT * FROM(
SELECT ROWNUM RN, A.* FROM
(SELECT * FROM PRODUCTS ORDER BY PNUM DESC) A
)
WHERE RN BETWEEN 6 AND 10;
--FROM절에서 사용한 별칭 RN은 WHERE절에서도 사용 가능

 

 

ROW_NUMBER OVER

ROW_NUMBER () OVER (분석절): 분석절을 기준으로 행번호를 매기는 함수

SELECT * FROM( --RN을 사용하기 위해 FROM절 추가
SELECT ROW_NUMBER() OVER(ORDER BY PNUM DESC) RN, PRODUCTS.*
FROM PRODUCTS)
WHERE RN BETWEEN 1 AND 5;

저작자표시 비영리 변경금지

+ Recent posts

티스토리툴바