[Oracle] 서브쿼리(Subquery)

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

서브쿼리(subquery)

SELECT *	-- 메인쿼리(main query)부분
FROM EMP
WHERE SAL > (SELECT SAL 	-- 서브쿼리(subquery)부분
	     FROM EMP
             WHERE ENAME = 'JONES');

  서브쿼리(subquery)는 SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는  SELECT 문을 의미한다. 서브쿼리의 결과 값을 사용하여 기능을 수행하는 영역은 메인쿼리(main query)라고 부른다.

  위의 SQL문은  ENAME (사원 이름)이  JONES 인 사람보다 높은 급여를 받는 사람들을 출력하는 예시이다. 보통  WHERE 절의 조건식에 들어가는  SELECT 문(기준이 되는 열)이 서브쿼리가 된다. 위의 예시에서는 중복이 일어나지 않아서 쿼리가 실행되지만(사람 이름은 중복이 나올 가능성이 있어서 주의해야 한다.) 서브쿼리의 결과로 여러 행을 반환할 때에는 다중행 서브쿼리(multiple-row subquery)를 사용해야 한다.

 

 

     서브 쿼리의 종류  

1. 단일행 서브쿼리 (single-row subquery) : 실행결과가 단 하나의 행으로 나오는 서브쿼리
2. 다중행 서브쿼리 (multiple-row subquery) : 실행결과 행이 여러 개로 나오는 서브쿼리
3. 다중열 서브쿼리 (multiple-column subquery) : 서브쿼리의  SELECT 절에 비교할 데이터를 여러 개 지정하는 방식
4. 인라인 뷰(inline view) + WITH절 :  FROM 절에 사용하는 서브쿼리
5. 스칼라 서브 쿼리(scalar subquery) :  SELECT 절에 사용하는 서브쿼리

 

서브쿼리의 특징  서브쿼리는 연산자와 같은 비교 or 조회 대상의 오른쪽에 놓이며  ( ) 로 묶어서 사용한다. 특수한 몇몇 경우를 제외한 대부분의 서브쿼리에서는  ORDER BY 절을 사용할 수 없다. 서브쿼리의  SELECT 절에 명시한 열은 메인쿼리의 비교대상과 같은 자료형과 같은 개수로 지정해야 한다. ex) 메인 쿼리의 비교 대상 데이터가 하나라면 서브쿼리의  SELECT 절도 같은 자료형인 열을 하나 지정해야 한다. 4. 서브쿼리에 있는  SELECT 문의 결과 행 수는 함께 사용하는 메인쿼리의 연산자 종류와 호환 가능해야 한다.

 

 

1. 단일행 서브쿼리(single-row subquery)

-- 자료형이 DATE일 때도 서브쿼리 사용가능
SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE 	-- 자료형이 DATE(날짜)일 때도 서브쿼리를 사용할 수 있다.
                  FROM EMP
                  WHERE ENAME = 'SCOTT');

 

-- 서브쿼리에서 특정 함수를 사용한 결과 값이 하나일 때 단일행 서브쿼리로 사용가능
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
    AND E.DEPTNO = 20
    AND E.SAL > (SELECT AVG(SAL) 	-- 서브쿼리안에서 함수 사용 가능(결과값이 단일행인 경우만)
                 FROM EMP);

 

단일행 연산자

  단일행 서브쿼리(single-row subquery)란 실행결과가 하나의 행으로 반환되는 서브쿼리를 의미한다. 메인쿼리와 비교할 때는 위의 표에 있는 단일행 연산자를 이용해서 비교해준다. 서브쿼리의 결과 값이  DATE (날짜) 자료형일 때나, 서브쿼리에서 특정 함수를 사용한 결과 값이 하나일 때도 단일행 서브쿼리로 사용 가능하다. 중복 등의 이유로 여러 행이 반환되는 경우에는 에러가 나므로 다중행 서브쿼리(multiple-row subquery)를 사용해야 한다. 

 

 

 

2. 다중행 서브쿼리(multiple-row subquery)

다중행 연산자	설  명
IN	메인 쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치하는 데이터가 있다면 true (  = ANY  ,  = SOME 이 같은효과 )
ANY, SOME	메인 쿼리의 조건식을 만족하는 서브쿼리의 결과가 1개 이상이면 true
ALL	메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true
EXISTS	서브쿼리의 결과가 존재하면(행이 1개 이상일 경우) true

다중행 서브쿼리(multiple-row subquery)는 실행 결과 행이 여러 개로 나오는 서브쿼리를 의미한다.

메인쿼리와 비교하려면 위의 표에 있는 다중행 연산자들을 이용해야 한다.

 

 

다중행 연산자 정리

  1) IN 연산자

-- IN을 기본연산자가 아닌 다중행 연산자로 사용할 수 있다.(효과는 같음)
SELECT *
FROM EMP
WHERE DEPTNO IN (20, 30);


-- 서브쿼리로 넣어서 사용가능
SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) 	
              FROM EMP
              GROUP BY DEPTNO);

 IN 연산자는  ( ) 안의 값들 중 하나라도 일치하는 메인쿼리를 반환한다.

 

 

  2) ANY, SOME 연산자

   ANY ,  SOME 연산자는 서브쿼리가 반환한 여러 결과 값 중 메인쿼리와 조건식을 사용한 결과가 하나라도  true 면 메인쿼리의 조건식을  true 로 반환해 주는 연산자이다. 메인쿼리와  = 비교를 하면( = ANY ,  = SOME )  IN 연산자의 효과와 같다.(효과는 같지만 알아보기 쉽도록 대부분  IN 연산자를 사용한다.)

   <  연산자로 비교하면 ( < ANY ,  < SOME ) 서브쿼리에  MAX 함수를 적용한 효과가 나고,  > 연산자로 비교하면( > ANY ,  > SOME ) 서브쿼리에  MIN 함수를 적용한 효과가 난다.

 

 

  3) ALL 연산자

   ALL 연산자는 서브쿼리의 모든 결과가 조건식과 맞아야만 메인쿼리의 조건식이  true 가 되는 연산자이다.  < 연산자로 비교하면 서브쿼리에  MIN 함수를 적용한 효과가 나고,  > 연산자로 비교하면 서브쿼리에  MAX 함수를 적용한 효과가 난다. ( ANY ,  SOME 과 반대)

 

 

  4) EXISTS 연산자

  서브쿼리에 결과 값이 1개 이상 존재하면 조건식이 모두  true (모두 출력), 존재하지 않으면 모두  false (출력하지 X)가 되는 연산자이다. 특정 서브쿼리 결과 값의 존재 유무를 통해 메인쿼리의 데이터 노출 여부를 결정해야 할 때 간혹 사용한다.

 

 

 

3. 다중열 서브쿼리(multiple-column subquery)

SELECT *
FROM EMP
WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL)
                        FROM EMP
                        GROUP BY DEPTNO);

  서브쿼리의  SELECT 절에 비교할 데이터를 여러 개 지정하는 방식을 다중 열 서브쿼리(multiple-column subquery, 복수열 서브쿼리)라고 한다. 메인쿼리에 비교할 열을  ( ) 로 묶어 명시하고, 서브쿼리에서는  ( ) 로 묶은 데이터와 같은 자료형 데이터를  SELECT 절에 명시하여 사용할 수 있다. 

 

 

 

4. 인라인 뷰(inline view) + WITH절

  1) 인라인 뷰(inline view)

SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC 
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E10, -- 인라인 뷰는 특정 테이블 전체가 아닌 SELECT문을 통해  
     (SELECT * FROM DEPT) D     	        -- 일부 데이터를 추출해 온후 별칭을 지정하여 사용할 수 있다.
WHERE E10.DEPTNO = D.DEPTNO;

   FROM 절에도 서브쿼리를 사용할 수 있으며 이를 인라인 뷰(inline view)라고도 부른다. 이 방식은  FROM 절에 직접 테이블을 명시하여 사용하기에는 테이블 내 데이터 규모가 너무 크거나 현재 작업에 불필요한 열이 너무 많아 일부 행과 열만 사용하고자 할 때 유용하다. 하지만  FROM 절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능면에서 좋지 않기 때문에 경우에 따라  WITH 절을 이용하기도 한다.

 

 

  2) WITH절 (오라클 9i부터 제공)

WITH
E10 AS (SELECT * FROM EMP WHERE DEPTNO = 10),
D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO, E10.ENAME, E10.DEPTNO, D.DNAME, D.LOC
FROM E10, D
WHERE E10.DEPTNO = D.DEPTNO;

   WITH 절은 메인쿼리가 될  SELECT 문 안에서 사용할 서브쿼리와 별칭을 먼저 지정한 후 메인쿼리에서 사용한다. (앞에서  E10 과  D 로  FROM 절에 명시한 서브쿼리를  WITH 절로 바꾸면 위의 SQL문이 된다.) 여러 개의 서브쿼리가 몇십, 몇 백 줄 이상 넘나드는 규모가 되었을 경우, 실제 수행해야 하는 메인쿼리와 서브쿼리를 분류할 때 유용하게 사용할 수 있다. 

 

---

상호 연관 서브쿼리(correlated subquery)

SELECT *
FROM EMP E1
WHERE SAL > (SELECT MIN(SAL)
            FROM EMP E2
            WHERE E2.DEPTNO = E1.DEPTNO)
ORDER BY DEPTNO, SAL;

  메인쿼리에 사용한 데이터를 서브쿼리에서 사용하고 서브쿼리의 결과 값을 다시 메인쿼리로 돌려주는 방식을 상호 연관 서브쿼리(correlated subquery)라고 한다. 성능을 떨어뜨리는 원인이 될 수 있고 사용빈도가 높지 않아 간간히 위의 SQL문처럼 사용될 수 있다. 

 

---

 

5. 스칼라 서브쿼리(scalar subquery)

SELECT EMPNO, ENAME, JOB, SAL,  
        (SELECT GRADE
         FROM SALGRADE
         WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE,
       DEPTNO,
        (SELECT DNAME
         FROM DEPT
         WHERE E.DEPTNO = DEPT.DEPTNO) AS DNAME
FROM EMP E;

  서브쿼리는  SELECT 절에도 사용할 수 있으며, 스칼라 서브쿼리는  SELECT 절에 하나의 열 영역으로 결과를 출력하는 것을 의미한다. 주의할 점은  SELECT 절에 명시하는 서브쿼리가 반드시 하나의 결과만 반환하도록 작성해 주어야 한다는 것이다.