서브쿼리 subquery

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

 

※ SELECT문에서 WHERE절의 조건식에 서브쿼리를 사용한다면 다음과 같다.

가장 빈번하게 사용하는 방식

ex) 서브쿼리로 EMP 테이블에서 JONES보다 급여가 높은 사원을 조회하기

•  특수한 몇몇 경우를 제외한 대부분의 서브쿼리에서는 ORDER BY절을 사용할 수 없다.
•  서브쿼리의 SELECT절에 명시한 열은 메인쿼리의 비교대상과 같은 자료형과 같은 개수로 지정해야한다.

 

① 단일행 서브쿼리

▶ 실행 결과가 단 하나의 행으로 나오는 서브쿼리를 뜻한다. 출력되는 결과가 하나이므로 메인쿼리와 서브쿼리 결과는

 다음과 같은 단일행 연산자를 사용하여 비교한다.

단일행 연산자
>	>=	=	<=	<	<>	^=	!=
초과	이상	같음	이하	미만	같지 않음

▶ 단일행 서브쿼리는 날짜형 데이터일때도 사용가능하다.

ex) SCOTT보다 빨리 입사한 사원 목록을 조회 한다면 다음과 같다.

SELECT *
FROM EMP
WHERE HIREDATE < (SELECT HIREDATE
                    FROM EMP
                    WHERE ENAME = 'SCOTT');

▶ 조인과 서브쿼리 함께 사용하기

ex) 20번 부서에 속한 사원 중 전체 사원의 평균 급여보다 높은 급여를 받는 사원정보와 소속부서정보를 함께 조회

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 VAG(SAL)
		 FROM EMP);

 

② 다중행 서브쿼리

• 실행 결과가 여러 개로나오는 서브쿼리
• 서브쿼리 결과가 여러개이므로 단일행 연산자는 사용할 수 없고 다중행 연산자를 사용해야 메인쿼리와 비교할 수 있다.
• 다중행 연산자의 종류는 다음과 같다.

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

 

 

②-1 IN연산자

 

각 부서별 최고 급여와 동일한 급여를 받는 사원 정보 출력

 

 

▶ 서브쿼리에서 GROUP BY절로 DEPTNO컬럼을 그룹화 하고 그룹별로 최고급여를 SELECT하면 

2850, 3000, 5000 이 나온다. IN 연산자를 사용해 메인쿼리에서 세 값중 일치하는 값을 가진 행만 출력한다.

 

 

②-2 ANY, SOME 연산자 

▶ 서브커리 결과 중 하나만 조건식에 맞으면 메인쿼리의 조건식이 참이되어 출력대상이 된다.

메인쿼리와 값을 비교할 때 ANY, SOME 연산자를 등가비교 연산자( = ) 와 함께 사용하면 IN 연산자와 정확히 같은 기능을 수행한다.

※ IN 연산자가 알아보기도 편하고 글자 수도 적기때문에 =ANY 연산자를 사용하는 경우는 거의 없고

   대부분 INT 연산자를 사용한다.

 

 

30번 부서 사원들의 최대 급여보다 작은 급여를 받는 사원 정보 출력하기

▲위의  ANY, SOME을 사용한 SELECT문의 서브쿼리 결과값은 950, 1250, 1500, 1600,  2850 이다.

   ANY 와 SOME 연산자는 한가지 값이 조건식에 만족하면 메인쿼리 조건식의 결과가 true가 되므로.

   제일 작은 값인 950보다 큰값들이라도 제일 큰 숫자인 2850 보다 작으면 조건 중 하나를 만족 하기때문에

   true가 되어  다른 숫자는 상관없이 2850보다 작은 값들이 모두 출력된다.

   (결국 30번 부서 사원들의 최대 급여보다 작은 급여를 받는 사원들이 출력된다.)

 ▶ 따라서  < ANY(SOME) , > ANY(SOME) 연산자는 서브쿼리에  MAX와 MIN 함수를 적용한 값을

 ANY(SOME) 연산자 없이 비교 연산자(<,>) 만 사용한 결과와 같아진다.

 

②-3 ALL 연산자

▶ ALL 연산자는 서브쿼리의 모든 결과가 조건식에 맞아야 메인쿼리의 조건식이 true가 되는 연산자다.

부서 번호가 30번인 사원들의 최소급여보다 더 작은 급여를 받는 사원 출력하기

▶ 서브쿼리의 결과 값(950, 1250, 1500, 1600,  2850)보다 작은 값을 가진 메인쿼리의 행만 true가 되어 출력된다.

즉 메인 쿼리 값 중 950(서브쿼리 결과 값 중 가장 작은 값)보다 작은 값을 가진 데이터만 출력 된다.

반대로 > ALL을 사용하면 최대급여(2850) 보다 큰 값을 가진 데이터만 출력 된다.

 

②-4 EXISTS 연산자

• 조금 특이한 연산자로 서브쿼리에 결과 값이 하나 이상 존재한다면 조건식이 모두 true가 되어 모든 행이 출력되고, 존재하지않으면 모두 false 가 아무 행도 출력되지 않는다.
•  특정 서브쿼리 결과 값의 존재 유무를 통해 메인쿼리의 데이터 노출 여부를 결정해야 할 때 간혹 사용한다.
•  다른 다중행 연산자에 비해 그리 자주 사용하는 편이 아니므로 EXISTS 연산자가 존재한다는 것 정도만 기억해 두어도 큰 문제는 없다.  

 

 

 

 

③ 다중열 서브쿼리 ( multiple-column subquery = 복수열 서브쿼리)

▶ 서브쿼리의 SELECT절에 비교할 데이터를 여러 개 지정하는 방식이다.

 메인쿼리에 비교할 열을 괄호로 묶어 명시하고 서브 쿼리에서는 괄호로 묶은 데이터와 같은 자료형 데이터를

 SELECT절에 명시하여 사용할 수 있다. 실무에서 유용하게 쓰이는 경우가 꽤 많다.

※ 서브쿼리의 SELECT 절에 명시한 열은 메인쿼리의 비교대상과 같은 자료형, 같은 개수로 지정해야한다.

 

④ FROM절에 사용하는 서브쿼리와 WITH절

▶ FROM절에 사용하는 서브쿼리는 인라인 뷰(inline view)라고도 부른다. 인라인 뷰는 특정 테이블 전체가 아닌

    SELECT문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주어 다음과 같이 사용한다.

• (SELECT * FROM EMP WHERE DEPTNO = 10) E10  <--  SELECT 해서 나온 테이블을 E10 으로 별칭 지정해서 사용
• (SELECT * FROM DEPT) D    <-- SELECT 해서 나온 테이블을 D 로 별칭 지정 해서사용
• 이 방식은 FROM절에 직접 테이블을 명시하기에는 테이블의 데이터 규모가 너무 크거나 현재 작업에 불필요한 열이 너무 많아 일부 행과 열만 사용하고자 할 때 유용하다.
• 하지만 FROM 절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어질 수 있기 때문에 경우에 따라 WITH절을 사용하기도 한다. 
• 오라클 i9 부터 제공하는 WITH절은 메인쿼리가 될 SELECT문 안에서 사용할 서브쿼리와 별칭을 먼저 지정한 후 메인쿼리에서 사용한다.

---

WITH
[별칭1] AS (SELECT문 1)
[별칭1] AS (SELECT문 2)
...
[별칭n] AS (SELECT문 n)
SELECT
    FROM 별칭1, 별칭2, 별칭3
    ...

---

▶ 위에 사용한 FROM 절의 서브쿼리와 출력되는 값은 같지만 WITH 절을 활용하여 작성하였다.

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;

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

메인쿼리에 사용한 데이터를 서브쿼리에서 사용하고 그 결과값을 다시 메인쿼리로 돌려주는 방식이다.

다만 성능을 떨어뜨리는 원인이 될 수 있고 사용빈도가 높지않아 이런식으로 사용할 수 있다는 정도만 기억해둔다.

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

 

⑤ SELECT절에 사용하는 서브쿼리

▶ 스칼라 서브쿼리(scalar subquery) 라고부르는 이 서브쿼리는 SELECT절에 하나의 열 영역으로서 결과를 출력한다.

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 절에 명시하는 서브쿼리는 반드시 하나의 결과만 반환하도록 작성해 주어야한다.