(Oracle)서브쿼리(Subquery)란 무엇인가??

Subquery란 SELECT문 내에서 다시 SELECT문을 사용하는 것이다.

 

형태는

① FROM 구에 SELECT문을 사용하는 인라인 뷰와 

② SELECT에 사용하는 스칼라 서브쿼리(Scala Subquery),

③ WHERE에 사용하는 서브쿼리 등이 있다.

 

예시를 들어보겠다.

① 인라인 뷰(from)

- FROM 구에 일종의 가상의 테이블(뷰)를 만들어 사용하는 것이다.

// EMP에 순번을 매겨 10번 이하까지 구하는 쿼리
SELECT * 
FROM (
      SELECT ROWNUM NUM, ENAME 
      FROM EMP
      ) a
WHERE NUM < 10;      

 

② 스칼라(select)

- 스칼라 Subquery는 반드시 한 행과 한 칼럼만 반환하는 서브쿼리이다. (여러행이면 에러뜸)

// 회원별 급여와 상급자의 급여를 구하는 쿼리
SELECT a.ENO, 
       a.SAL,
       (SELECT SAL
        FROM EMP
        WHERE ENO = a.MGR) as 상급자SAL
FROM EMP a

위 쿼리는 현재 main쿼리 내의 a 테이블을 사용했으므로 연관 Subquery라고도 할 수 있다.

메인쿼리와는 상관없이 사용한다면 순수 스칼라 서브쿼리라고 볼 수 있겠다.

하지만 업무상 경험을 보았을 때 전부 연관 Subquery였다...

 

③ 서브쿼리(where)

- 서브쿼리는 반환하는 행 수가 한 개인 것과 여러 개인 것에 따라서 단일 행 서브쿼리와 멀티 행 서브쿼리로 분류된다.

- 단일행 서브쿼리는 단 하나의 행만 반환하는 서브쿼리로 비교 연산자 =, <, <=, >, >=, <> 를 사용한다.

- 다중행 서브쿼리는 여러 개의 행을 반환하는 것으로 IN, ANY, ALL, EXISTS를 사용해야 한다.

1) IN : 서브쿼리의 결과 중 하나만 동일하면 참이 된다.(OR 조건)

         서브쿼리에 일치하는 값들을 모두 뽑아낼 수 있다. 

SELECT a.ENO, a.SAL, a.DEPTNO
FROM   EMP a
   INNER JOIN DEPT b
   ON a.DEPTNO = b.DEPTNO
WHERE a.ENO IN(SELECT ENO 
                FROM EMP
                WHERE SAL > 2000
               )

위 쿼리는 급여가 2천 이상인 모든 사람들을 뽑아내는 쿼리이다.

 

2) ALL : 메인쿼리와 서브쿼리의 결과가 모두 동일하면 참이 된다. 즉 모든 조건에 AND를 걸어준다고 보면 된다.

SELECT *
FROM EMP
WHERE SAL <= ALL (2000, 3000)

위 쿼리는 급여가 2000 이하이고 3000 이하인 것을 모두 만족하는 쿼리를 뽑아낸다. 즉 2000 이하인 값들을 뽑아내는 것이고 ALL( ) 안에는 다른 서브쿼리가 들어갈 수도 있다.

 

3) ANY : 메인쿼리의 비교조건이 서브쿼리 결과 중 하나 이상 동일하면 참이 된다. 즉 모든 조건에 OR을 걸어준다고 보면 된다.

SELECT *
FROM EMP
WHERE SAL <= ANY (2000, 3000)

위 쿼리는 급여가 2000 이하이거나 3000 이하인 것을 만족하는 쿼리를 뽑아낸다. 즉 3000 이하인 값들을 뽑아내는 것이고 ANY( ) 안에는 다른 서브쿼리가 들어갈 수도 있다.

 

4) EXISTS : 어떤 데이터의 존재 여부를 확인하는 것이다. 즉 결과는 참과 거짓이 반환된다.

SELECT ENO, SAL
FROM EMP a
    INNER JOIN DEPT b
    on a.DEPTNO = b.DEPTNO
WHERE EXISTS(SELECT 1
             FROM EMP
             WHERE SAL > 2000)

위 쿼리는 급여가 2000 이상인 직원이 있으면 메인쿼리를 전부 뽑아낸다.

 

IN과 EXISTS 앞에 NOT을 붙여서 활용할 수도 있다.

 

예를 들어 테이블이 Header / Detail 구조라면

Detail의 데이터가 일부가 삭제될 수도 있고 전체가 삭제될 수도 있다.

그럼 Header의 경우 Detail 데이터가 전부 삭제되면 삭제가 되어야 하는데

이를 not exists를 활용해서 해결할 수 있다.

아래와 같은 예시가 있다.

delete
from Header
where 조건들
      and not exists(select *
      		      from  디테일
                     where 조건들)