CHAPTER 09. SQL문 속 다른 SQL문 서브 쿼리-DO IT! 오라클로 배우는 데이터베이스 입문

CHAPTER09. SQL문 속 다른 SQL문 서브 쿼리

서브 쿼리는 어떤 상황이나 조건에 따라 변할 수 있는 데이터 값을 비교하거나 근거로 하기 위해 SQL문 안에 작성하는 작은 SELECT문을 의미

서브 쿼리는 조인과 더불어 여러 데이터를 하나의 SQL문에서 처리하기 위해 사용

서브 쿼리의 유형

• 기준 -서브 쿼리의 리턴 행 수2.다중 행 서브 쿼리: IN NOT IN, ANY(SOME),ALL
• 1.단일 행 서브 쿼리:= <> > ≥ < ≤
• 기준- 서브 쿼리의 비교 컬럼 수2.다중 컬럼 서브 쿼리
• 1.단일 컬럼 서브 쿼리

09-1. 서브 쿼리

서브 쿼리란?

• 서브 쿼리는 SQL문을 실행하는데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미
• 서브 쿼리의 결과 값을 사용하여 기능을 수행하는 영역은 메인 쿼리라고 부름
• 서브 쿼리는 실제로 INSERT문, DELETE문 ,CREATE문 등 다양한 SQL문에서 사용

서브 쿼리의 특징

1.서브 쿼리는 조회 대상의 오른쪽에 놓이며 괄호로 묶어 사용

2.서브 쿼리에서는 몇 몇의 특수한 경우를 제외하고는 ORDER BY사용이 불가

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

4.서브 쿼리의 결과 행 수는 사용하는 메인 쿼리의 연산자 종류와 호환 가능해야 함

09-2. 실행 결과가 하나인 단일 행 서브 쿼리

• 단일 행 서브 쿼리는 실행 결과가 단 하나의 행으로 나오는 서브 쿼리를 뜻
• 서브 쿼리에서 출력 되는 결과가 하나 이므로 메인 쿼리와 서브 쿼리 결과는 다음과 같이 단일 행 연산자를 사용하여 비교
• 단일 행 연산자≥:이상≤:이하<>,^=,≠:같지 않음
• <:미만
• =:같은
• :초과

단일 행 서브 쿼리와 날짜 형 데이터

• 단일 행 서브 쿼리는 서브 쿼리 결과 값이 날짜 자료형일 때도 사용 가능

단일 행 서브 쿼리와 함수

• 서브 쿼리에서 특정 함수를 사용한 결과 값이 하나일 때 역시 단일 행 서브 쿼리로서 사용 가능

09-3. 실행 결과가 여러 개인 다중 행 서브쿼리

• 다중 행 서브쿼리는 실행 결과 행이 여러 개로 나오는 서브쿼리를 가리킴
• 단일행 연산자가 아닌 다중행 연산자를 사용해야 메인쿼리와 비교할 수 있음

다중행 연산자

IN: 메인 쿼리의 데이터가 서브 쿼리의 결과중 하나라도 일치한 데이터가 있다면 true

ANY,SOME:메인 쿼리의 조건식을 만족하는 서브 쿼리의 결과가 하나 이상이면 TRUE

ALL: 메인 쿼리의 조건식을 서브 쿼리의 결과 모두가 만족하면 TRUE

EXISTS: 서브 쿼리의 결과가 존재하면 (즉, 행이 1개 이상일 경우) TRUE

IN연산자

• IN연산자를 사용해 메인쿼리에서는 서브쿼리 결과중 일치하는 값을 가진 행만 출력

ANY,SOME연산자

• ANY,SOME연산자는 서브 쿼리가 반환한 여러 결과 값 중 메인 쿼리와 조건식을 사용한 결과가 하나라도 TRUE라면 메인 쿼리 조건식을 TRUE로 반환해 주는 연산식
• ANY,SOME연산자를 등가 비교 연산자(=)와 함께 사용하면 IN연산자와 정확히 같은 기능을 수행

ALL연산자

• 모든 결과가 조건식에 맞아 떨어져야만 메인 쿼리의 조건식이 TRUE가 되는 연산자

EXISTS연산자

• 서브 쿼리에 결과 값이 하나 이상 존재하면 조건식이 모두 TRUE, 존재하지 않으면 FALSE가 되는 연산자
• 특정 서브쿼리 결과 값의 존재 유무를 통해 메인 쿼리의 데이터 노출 여부를 결정해야할 때 간혹 사용

09-4. 비교할 열이 여러개인 다중열 서브 쿼리

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

09-5. FROM절에 사용하는 서브쿼리와 WITH절

• FROM절에서 사용하는 서브 쿼리는 인라인 뷰(INLINE VIEW)라고도 부름
• 인라인 뷰는 특정 테이블 전체 데이터가 아닌 SELECT문을 통해 일부 데이터를 먼저 추출해 온 후 별칭을 주어 사용할 수 있음
• from절에 직접 테이블을 명시하여 사용하기에는 테이블 내 데이터 규모가 너무 크거나 현재 작업에 불필요한 열이 너무 많아 일부 행과 열만 사용하고자 할 때 유용
• from절에 너무 많은 서브 쿼리를 지정하면 가독성이나 성능이 떨어질 수 있기 때문에 경우에 따라 with절을 사용
• with절은 메인 쿼리가 될 select문안에서 사용할 서브 쿼리와 별칭을 먼저 지정한 후 메인 쿼리에서 사용

with절의 기본 형식

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

실제 수행해야 하는 메인 쿼리와 서브 쿼리를 분류할 때 꽤 유용하게 사용

09-6. SELECT절에 사용하는 서브 쿼리

• 스칼라 서브 쿼리 : SELECT절에서 사용하는 서브 쿼리
• SELECT절에 명시하는 서브 쿼리는 반드시 하나의 결과만 반환하도록 작성

Q1. 전체 사원중 ALLEN과 같은 직책(JOB)인 사원들의 사원 정보, 부서 정보를 다음과 같이 출력하는 SQL문을 작성하세요.

SELECT E.JOB,E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME
FROM EMP E ,DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND JOB=(SELECT JOB
         FROM EMP
       WHERE ENAME='ALLEN');

Q2. 전체 사원의 평균 급여(SAL)보다 높은 급여를 받는 사원들의 사원 정보, 부서 정보, 급여 등급 정보를 출력하는 SQL문을 작성하세요.

급여가 많은 순으로 정렬하되 급여가 같을 경우에는 사원 번호를 기준으로 오름차순 정렬

SELECT E.EMPNO,E.ENAME,D.DNAME,E.HIREDATE,D.LOC,E.SAL,S.GRADE
FROM EMP E,DEPT D,SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND SAL>(SELECT AVG(SAL)
					FROM EMP)
					ORDER BY E.SAL DESC,EMPNO;

Q3. 10번 부서에 근무하는 사원 중 30번 부서에서 존재하지 않는 직책을 가진 사원들의 사원 정보, 부서 정보를 다음과 같이 출력하는 SQL문을 작성하세요.

SELECT E.EMPNO,E.ENAME,E.JOB,E.DEPTNO,D.DNAME,D.LOC
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.DEPTNO=10
AND JOB NOT IN (SELECT JOB
                FROM EMP
                WHERE DEPTNO=30);

Q4. 직책이 SALESMAN인 사람들의 최고 급여보다 높은 급여를 받는 사원들의 사원 정보, 급여 등급 정보를 다음과 같이 출력하는 SQL문을 작성하세요

(단 서브 쿼리를 활용할 때 다중 행 함수를 사용하는 방법과 사용하지 않는 방법을 통해 사원 번호를 기준으로 오름차순으로 정렬하세요.)

-- 다중 행 함수를 사용하지 않는 방법
SELECT E.EMPNO,E.ENAME,E.SAL,S.GRADE
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.SAL>(SELECT MAX(SAL)
					FROM EMP
					WHERE JOB='SALESMAN')
ORDER BY E.EMPNO;

--다중 행 함수를 사용하는 방법
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SALGRADE S
 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
   AND SAL > ALL (SELECT DISTINCT SAL
                    FROM EMP
                   WHERE JOB = 'SALESMAN')
ORDER BY E.EMPNO;