Subquery

정의

• SQL 구문 안에 또 다른 Select 구문
• DML문과 CREATE TABLE 또는 VIEW 이용
• 알려지지 않은 조건에 근거한 값들을 검색하는 select문장 작성 유용
• Main Query 실행되기 이전에 한번 실행

가이드라인

• 괄호()로 묶음
• 연산자와 사용할 경우 오른쪽 배치
• ORDER BY 사용할 수 없다.
• 두 종류의 비교 연산자들이 서브 쿼리 사용
  • 단일 행 연산자 (=, >, >=, <, <=, <>, !=)
  • 복수 행 연산자 (IN,NOT IN, ANY, ALL, EXISTS)

서브쿼리 사용 가능한곳

• SELECT
• FROM
• WHERE
• HAING
• ORDER BY
• INSERT문의 VALUES 부분 대체제
• UPDATE문의 SET 부분 대체제

서브쿼리 실행순서

서브쿼리 실행 → 메인(부모) 쿼리  실행

---

서브쿼리 종류

SELECT 컬럼명1, (SELECT ...) 
-- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...)        
-- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE 컬럼명2 = (SELECT ...) 
-- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)

---

스칼라 서브쿼리

• SELECT문에서 나타나는 서브쿼리
• 딴 테이블에서 어떤한 값을 가져올때 씅ㅁ
• 하나의 레코드만 리턴 가능, 2개 이상 레코드 리턴 할수없다
• 일치하는 데이터가 없더라도 NULL값을 리턴할 수있다
  • 그룹함수의 특징중 하나인데 스칼라 서브쿼리 또한 이 특징 가지고 있다

SELECT D.DEPTNO, (SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO = D.DEPTNO) as EMPNO 
FROM DEPT D 
ORDER BY D.DEPTNO

---

★ INLINE VIEW

• 서브쿼리의 결과가 하나의 테이블에 대한 VIEW 처럼 사용
• FROM문에 나타나는 서브쿼리
• 무조건 AS 별칭 지정해야 한다

SELCET 컬럼명
FROM(SELECT * FROM 테이블명) AS 별칭
WHERE 조건식;

INLINE VIEW 사용하는 이유

• 서브쿼리와 테이블 조인이 이루어질 경우 검색하는 데이터명은 [테이블.필드명] 과 같이 길어진다. 이때 서브쿼리 통해 메인쿼리로 올라갈수록 쿼리의 길이가 점점 더 길어질 뿐 아니라 쿼리의 가독성 떨어짐
  이때, FROM절에 사용하는 INLINE VIEW에 별칭(Alias)를 줘서 간단하게 만들수 있음.
• 전체 테이블을 비교하는 것보다 테이블의 일부 데이터만 블러와 그중에 조건을 따지는 것이 비교 횟수가 적다.

1.급여가 20 부서의 평균 급여보다 크고 사원을 관리하는 사원으로서 20 부서에 속하지 않은 사원의 정보를 보여주는 SQL 문장 만들어라

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, -- a 별칭
      emp b // -- emp 테이블 별칭 : b
WHERE a.empno = b.empno
  AND b.mgr is NOT NULL
  AND b.deptno != 20;

결과창

---

2. 회원Table에서 마일리지가 평균마일리지 보다 큰 회원을 검색하시오  (Alias는 회원명,마일리지,평균마일리지)

    (마일리지는 천단위 구분, 평균마일리지는 소숫점이하 2자리로) : NULL인 사람도 처리

SELECT  M.MEM_NAME 회원명
     ,  M.MEM_MILEAGE 마일리지
     ,  TO_CHAR(A.AVG_MILEAGE,'999,999,999.00') 평균마일리지
FROM    MEMBER M, 
       (SELECT ROUND(AVG(NVL(MEM_MILEAGE,0)),2) AVG_MILEAGE FROM MEMBER) A
WHERE   M.MEM_MILEAGE > A.AVG_MILEAGE;

결과창

---

단일행 서브쿼리

• 오직 한개의 행(값)을 반환
• 단일 행 연산자( =, >, <=, <=, <>, !=) 만 사용

SELECT ename,job
      FROM emp
      WHERE job = (SELECT job 
                   FROM emp 
                   WHERE empno = 7369);

먼저 서브쿼리를 실행 시키면, empno가 7369인 job을 먼저 검색하고

job이 'CLERK'인 사원의 이름과 직업 반환

---

다중행(Multiple-Row) 서브쿼리

• 하나 이상의 행을 RETURN하는 서브쿼리를 다중 행 서브쿼리
• 복수 행 연산자 (IN,NOT IN, ANY, ALL, EXISTS) 사용

IN 연산자

• 서브쿼리 출력결과와하나라도 일치하면 참이 된다

부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제

SELECT empno,ename,sal,deptno  
    FROM emp
    WHERE sal IN (SELECT MAX(sal)
                  FROM emp
                  GROUP BY deptno);

ANY 연산자

• 어느 하나의 값이라도 만족이 되면 결과값을 반환

SELECT ename, sal 
    FROM emp 
    WHERE deptno != 20
      AND sal > ANY(SELECT sal 
                    FROM emp 
                    WHERE job='SALESMAN');

 

ALL 연산자

• 도출된 모든 조건값에 대해 만족 되야만 결과값 반환

SELECT ename, sal 
    FROM emp 
    WHERE deptno != 20
      AND sal > ALL(SELECT sal 
                    FROM emp 
                    WHERE job='SALESMAN');

EXISTS 연산자

• 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만 결과로 변환
• 서브쿼리에서 적어도 1개의 행을 리턴하면 논리식은 참이고 그렇지 않으면 거짓

사원을 관리할 수 있는 사원의 정보 보여줍니다.

ELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno 
              FROM emp 
              WHERE e.empno = mgr)

---

다중열(Mulitple-Column) 서브쿼리

• 서브쿼리의 결과값이 2개 이상의 컬럼을 반환하는 서브쿼리

Pairwise(쌍비교)서브쿼리

• 서브쿼리가 한번 실행되면서 모든 조건 검색해서 메인 쿼리로 넘겨줌

SELECT empno, sal, deptno
    FROM emp
    WHERE (sal, deptno) IN ( SELECT sal, deptno
                             FROM emp
                             WHERE deptno = 30
                             AND comm is NOT NULL );

Nonpairwise(비쌍비교) 서브쿼리

• 서브쿼리가 여러 조건별로 사용 되어서 결과값을 메인 쿼리로 넘겨줌

SELECT empno, sal, deptno
    FROM emp
    WHERE sal IN ( SELECT sal
                   FROM emp
                   WHERE deptno = 30
                     AND comm is NOT NULL )
     AND deptno  IN ( SELECT deptno
                      FROM emp
                      WHERE deptno = 30 
                        AND comm is NOT NULL );