오라클(Oracle) - (DQL) : 서브쿼리

• SQL문 안에 작성하는 작은 SELECT 문, 주로 WHERE 절에서 사용
• 서브 쿼리는 반드시 괄호() 안에 넣어 표현

서브 쿼리 특징

1. 조회 대상의 오른쪽에 놓이며 () 묶어서 사용
2. 대부분의 경우 ORDER BY 절을 사용할 수 없음
3. 메인 쿼리의 대상과 서브 쿼리의 대상은 같은 자료형이거나 같은 개수로 지정해야 함
4. 서브 쿼리와 메인 쿼리는 연산자와 함께 상호 작용하는 방식에 따라 단일행 서브 쿼리와 다중행 서브 쿼리로 나눠짐

예제

🐸 사원의 이름으로 사원이 속한 부서 번호를 찾음(서브쿼리)
- 메인 쿼리에서 부서 이름과 부서 번호를 비교해서 원하는 결과를 찾음

SELECT DNAME
	FROM DEPT
	WHERE DEPTNO = (SELECT DEPTNO
		FROM EMP
		WHERE ENAME = 'KING');

🐸 서브 쿼리를 이용해 'JONES'의 급여 보다 높은 급여를 받는 사원 정보 출력

SELECT *
	FROM EMP
	WHERE SAL > (SELECT SAL
		FROM EMP
		WHERE ENAME = 'JONES');

🐸 서브 쿼리를 사용하여 EMP 테이블의 사원 정보 중에서 사원 이름이 ALLEN인 사원의 추가 수당보다 많은 추가 수당을 받는 사원 정보 출력

SELECT *
	FROM EMP
	WHERE COMM > (SELECT COMM
		FROM EMP
		WHERE ENAME = 'ALLEN');

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

SELECT EMPNO, ENAME, SAL
	FROM EMP
	WHERE SAL > (SELECT TRUNC(AVG(SAL))
		FROM EMP)
	AND DEPTNO = 20;

다중행 서브 쿼리

출처 : https://m.blog.naver.com/regenesis90/222184391297

IN 연산자

X IN (서브쿼리 Y) : 서브쿼리 Y의 값이 X와 같음

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

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

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

ANY 연산자

X > ANY (서브쿼리 Y) : X는 Y의 최소값보다 큼

X < ANY (서브쿼리 Y) : X는 Y의 최대값보다 작음

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

메인 쿼리의 비교 조건이 서브 쿼리의 여러 검색 결과 중 하나 이상 만족되면 반환됩니다.
SALESMAN들의 급여를 조회하여 ANY 연산자로 메인 쿼리와 비교하여 출력되도록 해보자.
SALESMAN들의 급여 중 최소값보다 많은 급여를 받는 사원들이 출력됩니다. 

SELECT EMPNO, ENAME, SAL
	FROM EMP
	WHERE SAL > ANY (SELECT SAL
		FROM EMP
		WHERE JOB = 'SALESMAN');

🐸 SALESMAN들의 급여와 같은 급여를 받는 사원 정보 출력

SELECT *
	FROM EMP
	WHERE SAL = ANY (SELECT SAL
		FROM EMP
		WHERE JOB = 'SALESMAN');

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

SELECT *
	FROM EMP
	WHERE SAL < ANY (SELECT SAL
		FROM EMP
		WHERE DEPTNO = 30)
	ORDER BY SAL, DEPTNO;

ALL 연산자

X > ALL (서브쿼리 Y) : X는 Y의 최대값 보다 큼

X < ALL (서브쿼리 Y) : X는 Y의 최소값보다 작음

• 메인 쿼리의 비교 조건이 서브 쿼리의 여러 검색 결과와 모든 값이 일치하면 반환

SELECT EMPNO, ENAME, SAL
	FROM EMP
	WHERE SAL > ALL (SELECT SAL
		FROM EMP
		WHERE JOB = 'MANAGER');

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

SELECT *
	FROM EMP
	WHERE SAL < ALL (SELECT SAL
		FROM EMP
		WHERE DEPTNO = 30);

EXISTS 연산자

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

서브 쿼리에 값이 있는 경우

SELECT *
	FROM EMP
	WHERE EXISTS (SELECT DNAME
		FROM DEPT
		WHERE DEPTNO = 10);

서브 쿼리에 값이 없는 경우

SELECT *
	FROM EMP
	WHERE EXISTS (SELECT DNAME
		FROM DEPT
		WHERE DEPTNO = 50);

다중 열 서브 쿼리

• 서브 쿼리의 결과가 두 개 이상의 컬럼으로 반환되어 메인 쿼리에 전달하는 쿼리

SELECT EMPNO, ENAME, SAL, DEPTNO
	FROM EMP
	WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, SAL
		FROM EMP
		WHERE DEPTNO = 30);

• GROUP BY 절이 포함된 다중 열 서브 쿼리

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

FROM 절에 사용하는 서브 쿼리

메인 쿼리의 FROM 절을 서브 쿼리로 이용하는 방법으로, 인라인 뷰라고 합니다.
이 방식은 FROM절에 직접 테이블을 명시하여 사용하기에는 테이블 내 데이터 규모다 너무 크거나 현재 작업에 불필요한 열이 너무 많아 일부 행과 열만 사용하고자 할 때 유용 합니다.
하지만 FROM절에 너무 많은 서브쿼리를 지정하면 가독성이나 성능이 떨어 질 수 있습니다.

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

• 먼저 정렬하고 해당 개수만 가져오기

SELECT ROWNUM, ENAME, SAL 
	FROM (SELECT * FROM EMP ORDER BY SAL DESC)
	WHERE ROWNUM <= 3;

SELECT 절에 사용하는 서브 쿼리

• SELECT 절에서 쓰이는 단일 행 서브 쿼리를 스칼라 서브 쿼리라고 합니다.
• 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;

🐸 매 행마다 부서번호가 각 행의 부서번호와 동일한 사원들의 SAL 평균을 구해서 반환

SELECT ENAME, DEPTNO, SAL,
    (SELECT TRUNC(AVG(SAL)) -- 소속 분서의 급여 평균값 1개
        FROM EMP
        WHERE DEPTNO = E.DEPTNO) AS AVGDEPTSAL -- 해당 부서를 결정하는 것은 현재 입력되는 행의 부서
FROM EMP E;

🐸 부서 위치가 NEW YORK인 경우에 본사로, 그 외 부서는 분점으로 반환

SELECT EMPNO, ENAME,
        CASE WHEN DEPTNO = (SELECT DEPTNO
                            FROM DEPT
                            WHERE LOC = 'NEW YORK')
            THEN '본사'
            ELSE '분점'
        END AS 소속
FROM EMP
ORDER BY 소속 DESC;