Oracle 서브쿼리(sub query)

[서브쿼리 란?]

 

1. SELECT문 안에 SELECT문이 또 들어가 있는 형태

Q. 윤은혜 사원과 동일한 급여를 받고있는 사원을 조회

1)

SELECT SALARY
FROM EMPLOYEE
WHERE EMP_NAME='윤은해';

 

2) 서브쿼리 이용

SELECT *
FROM EMPLOYEE
WHERE SALARY=(SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME='윤은해');

 

2. 다중행 서브쿼리

- 서브쿼리(=)는 항상 1:1로 비교가 되어야 한다

SELECT *
FROM EMPLOYEE
WHERE JOB_CODE = (SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN ('부장','과장'));

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

 

- 다중행 서브쿼리(in)는 항상 1:1로 비교가 되어야 한다

SELECT *
FROM EMPLOYEE
WHERE JOB_CODE in (SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN ('부장','과장'));

 

 

3. EXIST : 서브쿼리의 켤과가 1행 이상이면 TURE, 0이면 FALSE 반환해주는 연산자 = FALSE면 인출 無

SELECT *
FROM EMPLOYEE
WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE DEPT_CODE='D20');  

 

 

 

4. INLINE VIEW

- INLINE VIEW SELECT문에서 선택한 컬럼만 사용할 수 있음

1)SELECT에서 선택된 것들 중점으로 불러오지 EMPLOYEE에서 불러오지 못한다
SELECT EMP_ID, DEPT_CODE
FROM (SELECT EMP_NAME, SALARY, BONUS FROM EMPLOYEE);

 

- WHERE 절도 마찬가지
SELECT EMP_ID, DEPT_CODE
FROM (SELECT EMP_NAME, SALARY, BONUS FROM EMPLOYEE)
--SELECT에서 선택된 것들 중점으로 불러오지 EMPLOYEE에서 불러오지 못한다
WHERE JOB_CODE='J12';

- 인라인뷰에 유니온을 쓸 수 잇다
SELECT *
FROM(
    SELECT EMP_ID AS CODE, EMP_NAME AS TITLE
    FROM EMPLOYEE
    UNION 
    SELECT DEPT_ID, DEPT_TITLE
    FROM DEPARTMENT
    UNION
    SELECT JOB_CODE, JOB_NAME
    FROM JOB)
WHERE TITLE LIKE '%부';

 

- 인라인뷰에 별칭을 달 수 있다

SELECT T.*, (SELECT 100 FROM DUAL) AS LITERAL

FROM(
    SELECT EMP_ID AS CODE, EMP_NAME AS TITLE
    FROM EMPLOYEE
    UNION 
    SELECT DEPT_ID, DEPT_TITLE
    FROM DEPARTMENT
    UNION
    SELECT JOB_CODE, JOB_NAME
    FROM JOB) T
WHERE TITLE LIKE '%부'; 

 

 

5. ROWNUM

SELECT ROWNUM,E.* 
FROM EMPLOYEE E
WHERE ROWNUM <10;

 

- ROWNUW은 1을 시작점으로 밖에 읽지 못한다

SELECT ROWNUM,E.* 
FROM EMPLOYEE E
WHERE 5< ROWNUM AND ROWNUM <10;  -- 오류

 

>>INLINE VIEW 이용
SELECT ROWNUM, T.* 
FROM (SELECT ROWNUM AS RNUM, E.* FROM EMPLOYEE E)T
WHERE RNUM BETWEEN 5 AND 10;

 

 

6. RANK_OVER() : 

- ORDER BY가 정렬한 기준으로 랭킹을 매긴다 

-  RANK_OVER(ORDER BY 컬럼명 + DESC) : 내림차순으로 정렬

- RANK_OVER(ORDER BY 컬럼명 + ASC) : 오름차순으로 정렬

 

SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) AS "RANK"
FROM EMPLOYEE;

 

 

7. DENSE_RANK() OVER : 공동순위가 있을 때 다음 순위를 센다(1위-2위-2위-3위)

RANK_OVER()	공동순위가 있으면 다음 순위를 건너뛰고 매긴다 예) 1위-2위-2위-4위
DENSE_RANK()	공동순위가 있으면 다음 순위를 그대로 매긴다 예)1위-2위-2위-3위

SELECT *
FROM(
    SELECT EMP_NAME, SALARY, RANK()OVER(ORDER BY SALARY DESC) AS "RANK",

    --중복된 순위 다음 순위의 번호는 없고 다다음 순위로 (1-2-2-4)
     DENSE_RANK() OVER(ORDER BY SALARY DESC) AS "RANK2"--중복된 순위 다음 순위의 번호가 있다(1-2-2-3), 
    FROM EMPLOYEE
);