[Oracle] 스칼라 서브쿼리, 인라인뷰, 서브쿼리

서브쿼리란 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.

SELECT  (SELECT ... )    --> 스칼라 서브쿼리
FROM    (SELECT ... )    --> 인라인 뷰
WHERE ...

 

---

 

1. 스칼라 서브쿼리

: Select절에 있는 Subquery

 

-- 특징

하나의 행(row)만 반환한다.

일치하는 값이 없으면 NULL을 반환한다.

부서별 최대 급여정보를 조회하는 쿼리

부서번호 40에 해당하는 직원이 없어 최대 급여가 조회되지 않는다.

부서번호 40의 최대 급여를 제외한 나머지 정보를 확인할 수 있다.

 

 

 

 

2. 인라인뷰

: From절에 있는 Subquery

부서번호 20의 평균 급여보다 급여를 많이 받고,

PRESIDENT가 아닌 MANAGER를 조회하는 쿼리

 

 

* 인라인뷰는 왜 사용할까?

 

1. 서브쿼리와 데이블 조인이 이루어질 경우 검색하는 데이터명(필드명)은 [테이블.필드명]과 같이 길어지된다. 이때 서브쿼리를 통애 메인쿼리로 올라갈수록 쿼리의 길이가 점점 더 길어질 뿐 아니라 쿼리의 가독성이 떨어진다. 이때, FROM절에서 사용하는 인라인뷰에 별칭(Alias)를 줘서 간단하게 만들 수 있다. 

 

2. 전체 테이블을 비교하는 것 보다 테이블의 일부 데이터만을 불러와 그 중에서 조건을 따지는것이 비교하는 횟수가 적다. 예를들어, 전체 데이터에서 비교하는 것보다 ROWNUM을 이용해서 10번째 튜플 정보만 가져와 그 중에서 조건을 따져 데이터를 가져온다면 비교하는 횟수가 줄어들게 할 수도 있다. (ROWNUM은 데이터를 가져오는 갯수를 제한하는 방식으로 N-TOP 질의라고 한다.)

 

https://loghada.tistory.com/18

[Oracle] Inline View 인라인뷰란

 

---

스칼라 서브쿼리 예제

+ 스칼라 서브쿼리 -> JOIN 으로 바꾸는 연습

 

SEE_INFO 테이블

• PK -> SEENUM
• FK -> GRADE

 

SEE_GRADE 테이블

• PK -> GRADE

/* 스칼라 서브쿼리 */

/* FROM 절에는 하나의 테이블만 명시, SELECT 절에서 다른 테이블과 원하는 데이터 정보를 추출하는 방식 */

-- 2학년 학생의 번호 / 이름 / 나이 / 학년이름을 찾아 출력하라

SELECT S.SEENUM, S.NAME, S.AGE,
    (SELECT G.GNAME
    FROM SEE_GRADE G
    WHERE S.GRADE=G.GRADE) AS GNAME
FROM SEE_INFO S
WHERE S.GRADE = 2;

 

-- JOIN으로 바꿔보기

SELECT S.SEENUM, S.NAME, S.AGE, G.GNAME
FROM SEE_GRADE G, SEE_INFO S
WHERE S.GRADE = G.GRADE(+)
AND S.GRADE = 2;

 

 

-- 학생의 번호가 5번 이상인 학생의 번호 / 이름 / 나이 / 학년이름을 찾아 출력하라

SELECT S.SEENUM, S.NAME, S.AGE,
    (SELECT G.GNAME
    FROM SEE_GRADE G
    WHERE S.GRADE=G.GRADE) AS GNAME
FROM SEE_INFO S
WHERE S.SEENUM >= 5;

 

-- OUTER JOIN 으로 바꿔보기

SELECT S.SEENUM, S.NAME, S.AGE, G.GRADE
FROM SEE_INFO S, SEE_GRADE G
WHERE S.GRADE = G.GRADE (+)
AND S.SEENUM >= 5;

 

 

예제 출처 ) https://seeminglyjs.tistory.com/276

[Oracle SQL] 오라클 스칼라 서브쿼리 알아보기

 

---

 

-- 직업이 MANAGER인 사원의 사원명, 부서명을 조회해라

SELECT E.ENAME,
    (SELECT D.DNAME
    FROM SCOTT.DEPT D
    WHERE E.DEPTNO=D.DEPTNO) DNAME
FROM SCOTT.EMP E
WHERE E.JOB = 'MANAGER';

-- JOIN으로 바꿔보기

SELECT E.ENAME, D.DNAME
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO(+)
AND E.JOB = 'MANAGER';

 

 

-- 부서별 최대 급여정보 조회 예

SELECT D.DEPTNO, D.DNAME,
    (SELECT MAX(SAL)
    FROM SCOTT.EMP E
    WHERE D.DEPTNO = E.DEPTNO) MAX_SAL
FROM SCOTT.DEPT D;

-- JOIN 으로 바꿔보기

SELECT D.DEPTNO, D.DNAME, MAX(E.SAL)
FROM SCOTT.DEPT D, SCOTT.EMP E
WHERE D.DEPTNO = E.DEPTNO(+)
GROUP BY D.DEPTNO, D.DNAME;

 

---

인라인 뷰 예제

-- 부서번호 20의 평균 급여보다 많이 받고 / 부서번호 20에 속하지 않은 / 사장이 아닌 / 관리자를 조회

SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL, E.DEPTNO
FROM (SELECT EMPNO
        FROM SCOTT.EMP
        WHERE SAL > (SELECT AVG(SAL)
                        FROM SCOTT.EMP
                        WHERE SCOTT.EMP.DEPTNO=20)) D, SCOTT.EMP E
WHERE E.EMPNO = D.EMPNO
AND E.DEPTNO != 20 ;

 

-- 사원 테이블과 부서 테이블에서 '부서별' 월급 총합, 평균, 부서별 인원수를 출력

-- JOIN

SELECT E.DEPTNO, SUM(SAL) SUM_SAL, ROUND(AVG(SAL)) AVG_SAL, COUNT(*) COUNT_NUM
FROM SCOTT.EMP E, SCOTT.DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO;

 

-- INLINE VIEW

SELECT D.DEPTNO, SUM_SAL, AVG_SAL, COUNT_NUM
FROM (SELECT DEPTNO, SUM(SAL) SUM_SAL, ROUND(AVG(SAL)) AVG_SAL, COUNT(*) COUNT_NUM
        FROM SCOTT.EMP
        GROUP BY DEPTNO) E, SCOTT.DEPT D
WHERE D.DEPTNO=E.DEPTNO;

 

 

-- 부서별 최대 급여

-- 스칼라 서브쿼리

SELECT D.DEPTNO, D.DNAME,
    (SELECT MAX(SAL)
    FROM EMP
    WHERE DEPTNO=D.DEPTNO) SAL
FROM DEPT D;

-- INLINE VIEW

SELECT D.DEPTNO, D.DNAME, SAL
FROM (SELECT MAX(SAL) SAL, DEPTNO
        FROM SCOTT.EMP
        GROUP BY DEPTNO) E, SCOTT.DEPT D
WHERE D.DEPTNO = E.DEPTNO(+);